ImportSpec and ExportSpec Functions

A function is an instruction word representing an action you want to perform in Longview Application Framework. You can carry out the action by typing a function statement in an ImportSpec or ExportSpec Document in Longview Application Framework.

Note: Unless otherwise specified, the following functions are available for use in both ImportSpecs and ExportSpecs.

The following functions apply to DataAreas:

The following functions apply to DataTables:

Assign

This function does not apply to ImportSpecs and ExportSpecs for DataTables.

Use this Longview Application Framework function to designate dimension names when each external record contains, or will get, multiple value fields, providing a way to associate each value field with specific symbols.

The ASSIGN function can only be used in a single dimension.

ASSIGN DimName                                    

where:

• DimName is the dimension across which the multiple values will be associated.

The ASSIGN function must be used with the PUT function, and can only be used if the dimension has not been set by DEFINE, and if the VALUEFIELD function has not been used.

See also

• Define (for DataAreas)
• Put
• ValueField

DataSource

Use this function with import specification to define the source of the data to be imported. This keyword is available in a ImportSpecs only, is mandatory, and specifies the type and source of data for the import operation. The parameters for the DataSource function depend on the specified type of connection.

DataSource TEXT, "FileName", INTERNAL|EXTERNAL, "Delimiter|REGIONAL"            

where:

• FileName is the name of the source file, and must be enclosed in double quotation marks.

  Note: By default, ODBC connections through Application Framework are allowed. To restrict all ODBC connections through Application Framework, open the lv_af.cfg file found in the working directory of the lv_af.exe and add the following statement to the end of the document: PERMIT_DATASOURCE_ODBC=FALSE

DataSource TEXT, "MyData", EXTERNAL, "{"
DataSource TEXT, "MyDataTable", INTERNAL, "REGIONAL"
DataSource ODBC, "Driver={SQL Server Native Client10.0};Server=localhost;Database=mydb ;Trusted_Connection=Yes;"

See also

• SQLBatchSize
• SQLStatement

DataTarget

Use this function with export specification to define the target of the data to be exported. This keyword is available in a Data Export Specification only, is mandatory, and specifies the target and type of data for the export operation.

DataTarget TargetType, "FileName", INTERNAL|EXTERNAL, "Delimiter|REGIONAL"

where:

• TargetType is one of the following:
  • TEXT—Exports the data to a text file. You must use this keyword if you are exporting from a DataTable.
  • ODS—Exports the data to an ODS file. For more information on ODS, see the “Longview Integration Guide”.

    Note: DataTables do not support ODS.

• "FileName" is the name of the source file, and must be enclosed in double quotation marks.

  Note: If the document is in the same location as lv_af.exe, you do not need to specify the drive or path. If the path specified does not exist, it will be created. The folder path can have a maximum length of 260 characters.

• INTERNAL|EXTERNAL is the location of the file, where INTERNAL is in the file repository and EXTERNAL is in the file system.
• Delimiter is the character that you would like to use to separate the fields in the target file, and must be enclosed in double quotation marks.
• REGIONAL specifies to use the client machine’s regional settings to determine the decimal character for export. This keyword must be enclosed in double quotation marks.

DataTarget TEXT, "MyData", INTERNAL
DataTarget TEXT, "MyDataTable", INTERNAL, "REGIONAL"            

DecimalCharacter

Use this function to specify whether the character that is used as a decimal character, in the source or target file, is a comma or a period.

DecimalCharacter "Character|REGIONAL"                    

where:

• Character is the decimal character for the import or export file and can be either a period ( . ) or a comma ( , ). The default decimal character is a period ( . ).
• REGIONAL specifies to use the client machine’s regional settings to determine the decimal character for import and export. This keyword must be enclosed in double quotation marks.

DecimalCharacter ","
DecimalCharacter "REGIONAL"                                            

Define (for DataAreas)

Use this function to specify how a field in the source file will map to the dimensions.

DEFINE DimName, MapMethod, FieldName|SymName[, MapName, INTERNAL|EXTERNAL]

where:

• DimName is the dimension being defined.
• MapMethod is a mapping method and is one of the following:

  Value	Description
  MATCH	The values encountered in field FieldName must match the dimension symbols exactly.
  MAP	The values encountered in field FieldName will be mapped between dimension symbols via the Map specified by MapName.
  UNIQUE	Import operations: all values encountered will be placed in the symbol SymName (symbol names are not derived from any source field). Export operations: only values in the source DataArea belonging to the symbol SymName will be exported.
  CONSECUTIVE	Values will be placed consecutively (in the order they are encountered) in the dimension starting at symbol SymName, moving in priority order through that symbol's siblings. CONSECUTIVE is designed to work with a symbol existing in only one hierarchy - if the symbol belongs to more than one hierarchy, the behavior used to determine the siblings is undefined and may be affected by hierarchy reorganizations and import/exports. If the dimension is a schedule dimension, there is no concept of hierarchy or priority, therefore symbols will be used in defined order. If the end of the sibling or symbol list is reached, the next value will generate an error. Note: The CONSECUTIVE parameter is available for use in ImportSpecs only.

• FieldName|SymName is one of the following:

  Value	Description
  FieldName	This parameter applies only when MapMethod is MATCH or MAP. This can be any alphanumeric identifier, and is used only within the scope of the import action. This parameter must match exactly the FieldName specified in your Set function.
  SymName	This parameter applies only when MapMethod is UNIQUE or CONSECUTIVE. This is the name of a valid symbol for the specified dimension.

• MapName is required when MapMethod is MAP and is one of the following:
  • the name of a map that exists inside the database, created using the Mappings editor. You must specify INTERNAL as the map location for this type of map.
  • the path and file name, including the relevant extension, of a map that exists as a file, such as a text file on your local machine or network location. If you do not specify the path, the system assumes the path is C:\...\MyDocuments\Longview. You must specify EXTERNAL as the map location for this type of map.

A valid ImportSpec or ExportSpec must contain a DEFINE or ASSIGN for each base dimension in the DataArea. If the DataArea contains schedule data, each extra dimension must also relate to a DEFINE or ASSIGN.

DEFINE Accounts, MATCH, Acct
DEFINE TimePeriods, MAP, TimePer, TimePeriodsMap, INTERNAL                        
DEFINE Entities, MAP, Ent, MyMaps\EntitiesMap.txt, EXTERNAL
DEFINE Currencies, UNIQUE, USD
DEFINE Details, CONSECUTIVE, AgingT

It is possible to use a single named field in an Import Spec with one or more keywords (Define, Put, ValueField) to avoid outside transformation of the source file.

To achieve this, you can use multiple define statements which refer to the same fieldname but different map files. For example:

72740000, Bike, 123.45
72740001, Skateboard, 987.65            
73740000, Bike, 12.34
73740001, Skateboard, 98.76

Set AccEnt, 1
Set Tim, 2
Set Det, 4
Set Cur, 5
Set Seg, 6
Set Ele, 7
Set Con, 8
Set Val, 9
Define ACCOUNTS, Map, AccEnt, AccountMapping, INTERNAL        
Define TIMEPER, Match, Tim
Define ENTITIES, Map, AccEnt, EntityMapping, INTERNAL
Define DETAILS, Match, Det
Define CURRENCY, Match, Cur
Define SEGMENTS, Match, Seg
Define ELEMENTS, Match, Ele
Define CONTROLS, Match, Con
ValueField Val

40000, Wildcard, ???40000
40001, Wildcard, ???40001                    

727, Wildcard, 727*
737, Wildcard, 737*            

See also

• Assign
• Set
• ValueField

Define (for DataTables)

Use this function to specify how a field in the source file maps to a DataTable field.

DEFINE TableFieldName, MapMethod, FieldName|SymName[, MapName, INTERNAL|EXTERNAL]         

where:

• TableFieldName is the name of the DataTable field to which to map the field in the source file.
• MapMethod is a mapping method and is one of the following:

  Value	Description
  MATCH	The values encountered in field FieldName must match the dimension symbols exactly.
  MAP	The values encountered in field FieldName will be mapped between dimension symbols via the Map specified by MapName.
  UNIQUE	Import operations: The SymName will be placed in the corresponding TableFieldName.

• FieldName|SymName is one of the following:

  Value	Description
  FieldName	This parameter applies only when MapMethod is MATCH or MAP. This can be any alphanumeric identifier, and is used only within the scope of the import action. This parameter must match exactly the FieldName specified in your Set function.
  SymName	This parameter applies only when MapMethod is UNIQUE. This is the name of a valid symbol for the specified dimension.

• MapName is required when MapMethod is MAP and is one of the following:
  • the name of a map that exists inside the database, created using the Mappings editor. You must specify INTERNAL as the map location for this type of map.
  • the path and file name, including the relevant extension, of a map that exists as a file, such as a text file on your local machine or network location. If you do not specify the path, the system assumes the path is C:\...\MyDocuments\Longview. You must specify EXTERNAL as the map location for this type of map.

Define Account, Map, Acc, MyAccountMap, INTERNAL
Define CostCentre, Match, CostCentre
Define Company, Map, CompCode, MyCompanyMap, INTERNAL
Define Currency, Match, Curr
Define PostPeriod, Unique, [[ SYSTEM, SGPCurrentPeriod, DBDEFAULT ]]        
Define Amount, Match, Val
Define DebitOrCredit, Match, DRCR
Define PostDate, Match, PostDate
Define MyColumnName, Match, FieldX

It is possible to use a single named field in an Import Spec multiple times to avoid certain external transformations of the source file. To achieve this, you can use multiple define statements which refer to the same FieldName but different map files.

For example, if Field1 in the source file is similar to Account.CostCenter.CompanyCode.Currency (e.g.: 11000.007.123.CAD), you can use Define statements similar to the following:

Define Account, Map, Field1, MyAccountMap, INTERNAL
Define CostCentre, Map, Field1, MyCCMap, INTERNAL
Define Company, Map, Field1, MyCompanyMap, INTERNAL
Define Currency, Map, Field1, MyCurrMap, INTERNAL                

DuplicateMappings

Use this Longview Application Framework function to specify how your process handles duplicate mappings. Duplicate mappings can occur when a dimension’s DEFINE function is set to MAP, and the specified field's value (for imports) or symbol name (for exports) for a given record matches multiple maps in the Symbol Map.

DUPLICATEMAPPINGS Action             

where:

• Action is the action to take when the system encounters duplicate mappings in a source file. Select one of the following:

  Value	Description
  ALLOW	Specifies that duplicate maps are permitted. In this case, if multiple EXACT maps are found, all mappings will be used so that the same value is imported to multiple data cells. Import operations only: If no EXACT maps are found, the value will get imported to only one location, the one corresponding to the first MAP listed.
  DISALLOW	Specifies that duplicate maps should not be allowed. The first entry is submitted. If a duplicate map is encountered, the system reports an error. Note: This is the default setting.

DUPLICATEMAPPINGS ALLOW                

DuplicateRecords

This function does not apply to ImportSpecs and ExportSpecs for DataTables. Use this Longview Application Framework function to specify how your process handles duplicate records.

Caution: Using the ADD option adds values in the file to the existing DataArea value. Using all other options overwrites existing DataArea values. Longview recommends clearing the DataArea first before using the ADD option.

DUPLICATERECORDS Action         

where:

• Action is the action to take when the system encounters duplicate records in a source file. Select one of the following:
  • For use in both ImportSpecs and ExportSpecs:

    Value	Description
    ADD	Specifies that duplicate records should be allowed, with the value of all such records being summed. In this mode, if a duplicate record is encountered that contains text (versus numeric) data, it will be treated as an invalid/error record.
    DISALLOW	Specifies that duplicate records should not be allowed. The first entry is submitted. If a duplicate record is encountered, the system reports an error. Note: This is the default setting.

  • For use in ImportSpecs only:

    Value	Description
    USEFIRST	Specifies that the first entry of a duplicate record should be used. If a duplicate record is encountered, the system does not report an error.
    USELAST	Specifies that the last entry of a duplicate record should be used. If a duplicate record is encountered, the system does not report an error.

  • For use in ExportSpecs only:

    Value	Description
    ALLOW	Specifies that duplicate records are allowed, and are all exported to the target. Note: This option is not allowed if the Assign or Put functions are used.

DUPLICATERECORDS USELAST        

See also

• Reconcile Upload

ErrorFile

Use this Longview Application Framework function to specify the name of the error file to contain data on invalid records.

This will create a text file in the default folder. All rejected records will be captured in this file.

ERRORFILE FileName             

where:

• FileName is the name of the error file to contain data on invalid records. It can include a complete or partial folder path in the format C:\...\FileName. If FileName includes spaces, enclose it in double quotation marks; for example:

  "C:\My Documents\My Data.txt"

Note: If the document is in the same location as lv_af.exe, you do not need to specify the drive or path. If the path specified does not exist, it will be created. The folder path can have a maximum length of 260 characters.

ERRORFILE error.txt                

IgnoreFooterRecords

This function does not apply to ExportSpecs.

Use this Longview Application Framework function to specify the number of footer rows to ignore on import. If you do not include this function in your import specification, the system assumes your import file ends with data records with no footer rows.

IGNOREFOOTERRECORDS Num         

where:

• Num is the number of header records to ignore. Blank lines at the bottom of the import file are treated as records.

IGNOREFOOTERRECORDS 2        

IgnoreHeaderRecords

This function does not apply to ExportSpecs.

Use this Longview Application Framework function to specify the number of header rows to ignore on import. If you do not include this function in your import specification, the system assumes your import file starts with data records with no header rows.

IGNOREHEADERRECORDS Num         

where:

• Num is the number of header records to ignore. Blank lines at the top of the import file are treated as records.

IGNOREHEADERRECORDS 2        

IncludeHeader

Use this Longview Application Framework function to export a default or specific header rows when exporting from a DataArea or DataTable.

IncludeHeader AUTO|SPECIFY[, "line 1", "line 2", ...]         

where:

• AUTO auto-populates the header with the dimension descriptions for each dimension and VALUE for the value field. If there are multiple value fields, the symbol description will be output instead of VALUE.
• SPECIFY allows you to specify a custom header. The header can be a single line or multiple lines. Each line must be enclosed in quotes and separated by a comma.

INCLUDEHEADER AUTOINCLUDEHEADER SPECIFY, "Account, Entity, Time Period, Value"
INCLUDEHEADER SPECIFY, "File created on May 31 2017", "****"

LogFile

Use this Longview Application Framework function to specify the name of the log file to contain information of the processing of the source or target file. This will create a text file in the default folder. All log information will be captured in this file.

LOGFILE FileName                 

where:

• FileName is the name of the error file to contain data on invalid records. It can include a complete or partial folder path in the format C:\...\FileName. If FileName includes spaces, enclose it in double quotation marks; for example:

  "C:\My Documents\My Data.txt"

Note: If the document is in the same location as lv_af.exe, you do not need to specify the drive or path. If the path specified does not exist, it will be created. The folder path can have a maximum length of 260 characters.

LOGFILE log.txt                                        

MaxError

This function does not apply to ImportSpecs and ExportSpecs for DataTables. For DataTables, this function is always set to 1.

Use this Longview Application Framework function to specify the maximum number of error records to permit before stopping a process. This function is optional.

MAXERROR NumErrorRecords                            

where:

• NumErrorRecords is the maximum number of error records to permit before stopping an import. The default value for this function is 0.

MAXERROR 1                    

Put

This function does not apply to ImportSpecs and ExportSpecs for DataTables.

Use this Longview Application Framework function in both ImportSpecs and ExportSpecs to designate symbol and field names when each external record contains (or will get) multiple value fields, providing a way to associate each value field with specific symbols.

PUT SymName, FieldName                    

where:

• SymName is a symbol name in this dimension.
• FieldName is the field with which this symbol is to be associated.

The PUT function can be used only if the ASSIGN dimension has been specified, and multiple PUT functions should be specified (targeting multiple fields to multiple symbols). This function cannot be used in conjunction with the ValueField function.

See also

• Assign

QuotedStrings

This function is available only for DataTables.

If you’re importing from or exporting to a .csv file, you may need to enclose string values in double quotation marks. Use this function in an ImportSpec to remove the double quotation marks from string values on import. Similarly, use this function in an ExportSpec to enclose string values in double quotation marks on export to .csv.

QuotedStrings ON|OFF                 

where:

• ON encodes/decodes strings with double quotation marks.
• OFF indicates that string values should be exported or imported as-is.

QuotedStrings ON                            

RegionalDates

This function is available only for DataTables.

The system stores dates for DataTable objects and App tables in ISO 8601 format (yyyy-mm-dd). Use this function in an ExportSpec to convert dates from ISO 8601 format to the format specified by the client machine’s regional settings.

Similarly, you can also use this function in an ImportSpec to convert dates from the format specified by the client machine’s regional settings to ISO 8601.

This can be useful when working with DataTable data in a .csv file that you plan to import into your Longview system.

RegionalDates ON|OFF                        

where:

• ON converts dates from ISO 8601 to regional format and vice-versa.
• OFF imports and exports date values without any conversion.

RegionalDates ON                    

ReverseSign

This function does not apply to ImportSpecs and ExportSpecs for DataTables.

Use this Longview Application Framework function to toggle logic dealing with sign reversal of numeric values.

When this logic is enabled (ON), the system considers the Account symbol's "balance type" property. If this property indicates a "credit" Account, the numeric value being imported or exported should have its sign reversed.

The default value for ReverseSign is OFF.

REVERSESIGN ON|OFF                              

REVERSESIGN ON                                             

ScientificNotation

Use this function in an ImportSpec if the data in your import is in scientific notation format.

ScientificNotation ON|OFF                                              

where:

• ON enables importing of data in scientific notation format.
• OFF disables importing of data in scientific notation format.

ScientificNotation ON                                        

Search

Use this Longview Application Framework function to filter source records based on values of specified fields.

SEARCH SearchExpression                                    

where:

• SearchExpression consists of one or more relational expressions, linked by AND and OR and grouped by brackets, as appropriate:

  [(] RelationalExpression [AND|OR RelationalExpression [)] [AND|OR …]]

  Each RelationalExpression is created using field names, operators and values, as follows:

  RelationalExpression = FieldName EQ|NE|LE|LT|GE|GT "Value"

  The value parameter may optionally contain wildcards (? and *), and symbols for operators, such as ==, <=, >=, and so on.

  Multiple SEARCH expressions may appear within a single specification object.

SEARCH "ACCT==??1 OR ACCT==??2"                                    

Set

Use this function to assign unique names identifying the structural elements of the source data file. You must use either the Define or ValueField functions for each Set function.

SET FieldName, Position                                                    

where:

• FieldName can be any alphanumeric identifier, and is only used within the scope of the import action.
• Position is a parameter that can be a field number (for delimited text files) or an element[.subelement] name (for XML files). Multiple SET functions are used to refer to various fields in the data object, but not all fields require the SET function.

SET Acct, 1
SET TimePer, 2
SET Ent, 3
SET Cur, 4                                            

StringEncodedSymbolNames

This function is available only for DataTables.

If you’re importing from or exporting to a .csv file, you may need to enclose symbol names in square brackets so that numeric symbol names are not interpreted as numbers (and the leading zeros are retained). Use this function in an ExportSpec to enclose symbol names in square brackets on export to .csv. Similarly, use this function in an ImportSpec to remove the square brackets from symbol names on import.

Note: This logic applies only to symbol names in columns that have the symbol data type.

StringEncodedSymbolNames ON|OFF                                          

where:

• ON encodes/decodes symbol names with square brackets.
• OFF indicates that symbol names should be exported or imported as-is.

StringEncodedSymbolNames ON                                            

SQLBatchSize

Use this function to specify the number of records to retrieve with each fetch against the ODBC data source. The default if this function is not specified is 1000 records. Specifying a larger number of records to retrieve may improve performance.

Note: This function applies only when DataSource is ODBC.

SQLBatchSize Size                                        

where:

• Size specifies the number of records to retrieve with each fetch against the ODBC data source.

SQLBatchSize 500                                    

SQLStatement

Use this function to specify the SQL statement to pass along to the ODBC data source.

Note: This function applies only when DataSource is ODBC.

SQLStatement Statement[, SQLFile]                              

where:

• Statement is the SQL statement to pass along to the ODBC datasource and can be one of the following:

  Value	Description
  “statement”	Specifies the SQL statement in text, enclosed in double quotation marks.
  FILE	Specifies that the SQL statement is specified in a file.

• SQLFile is the file containing the SQL statement, including the extension, and applies only when Statement is FILE. The file name must be relative to the current directory.

SQLStatement SELECT Currency, Rate FROM Rates
SQLStatement FILE, Sample.sql                                        

ValueField

This function does not apply to ImportSpecs and ExportSpecs for DataTables.

Use this Longview Application Framework function to specify which field will contain the actual data value being imported or exported.

VALUEFIELD FieldName                            

where:

• FieldName can be any alphanumeric identifier, and is only used within the scope of the import action.

See also

• Assign
• Define (for DataAreas)