Import

Use this command to import data into Analyzer. The Import command is most commonly used to directly import data within procedures.

The recommended method for creating an Import command is to define the desired data through the Data Definition Wizard. When the definition is completed, a resulting Import command is written into the Command Log. The Import command can then be copied and pasted into a procedure.

For information about all of the available options for defining data, see Defining Files and Fields

Tip: You only need to define data once. Any time the Data Definition Wizard makes a copy of the source data, you can refresh the copy from the live source data using the Refresh command at any time. See Refresh for details.

Note: If accessing the source file requires a password, use the Password command to be prompted for the password so that the Import command can proceed. See Password for details. The ODBC, Excel and Access Import types do not support the password parameter as any password required is stored in the local registry at the time of definition.

Note: As Import commands can be fairly complicated, contact Arbutus technical support prior to editing an Import command in a procedure to insert variables using macro substitution or to modify Import parameters.

Parameters

As the command syntax for the Import command will vary based on the type of file being imported, see Command Mode Syntax for full detail on the specific command syntax for each Import type.

In addition to the common command parameters described below, the Import command also supports use of the AS field modifier to modify imported field names. For more information see Field Modifiers.

End

The END parameter specifies the end of the field list. In the case of ODBC, EXCEL and ACCESS imports, END is also used to specify the end of optional WHERE, CONDITION and ORDER clauses.

Fields

The FIELDS parameter allows the user to specify the fields to be imported (including their field name, field type, start position, field length and optional number of decimals or date format). See Record_Length for exceptions to supplying field information.

Password

When importing password protected data, Analyzer stores the encrypted password in the user's registry for later secure re-use. This parameter is not supported for ODBC, Excel or Access Import types.

For backward compatibility however, the Import command also supports a PASSWORD parameter for accessing and importing a source file that requires a password. This parameter is rarely used and is not recommended, but if used in a command mode (i.e. a procedure); the Import command must be preceded with a Password command to prompt the user for the password. The Password parameter in the Import command then allows the supplied password from the Password command to subsequently be used by the Import command (where supported). For command details, see Password.

Record_Length

For import commands supporting use of the optional Record_Length syntax, this parameter is used to "force" the record length of the input file. If the RECORD_LENGTH parameter is present, then each of the specified field definitions requires, at a minimum, that a field name, field type, start position and field length be supplied.

If the Record_Length parameter is omitted, then the record length is inferred from the defined fields (by accumulating their lengths). If the Record_Length parameter is omitted, then each of the specified field definitions would NOT include a starting position, as this would be inferred from the descriptions. Therefore, each field definition would include, at a minimum, only the field name, field type and field length.

Tip: Omitting the record length makes for simpler Import command syntax, and is only appropriate when you are defining every field in a fixed length file.

Server

The Server parameter is supported for all Import types (except ODBC). Server allows the user to specify the server_profile for the file to be imported from an Arbutus Windows Server.

To

For all Import commands, TO specifies the name of the table layout to be created by the Import command.

Command Mode Syntax

The command syntax varies between the different types of import. Syntax for each Import type is listed below with additional detail except for importing XML, Print and PDF files which should always be generated using the Data Definition Wizard due to their complexity. To see additional detail on common command parameters, see Parameters.

Fields referenced in the Import command can use the AS field modifier to alter imported field names.

All import syntax is valid for files accessed locally or via the Arbutus Windows Server (with the exception of ODBC Import types which are only available locally).

Importing MS Excel Spreadsheets via OPENXML

IMPORT OPENEXCEL TO table_layout FILE "external_file_name" TABLE "sheet_name" SERVER "<LOCAL | SERVER_PROFILE>" FLAT "flat_file_name" <ASCII> FIELDS 1 "Payroll Name" END

Note: Please note that currently individual column selection for Excel spreadsheets imported using OPENEXCEL is currently not available in the Data Definition Wizard, and therefore the column list is essentially ignored in the Import command (in other words the Import command will return all the columns, all of the time, regardless of the columns specified in the in the Import command syntax).

Note: Use the optional ASCII keyword if you want any Unicode data that is read to be outputted as ASCII. This option should only be used if you are confident that the data only contains North American English characters.

Note: The ASCII keyword is automatically inserted into the Import OPENEXCEL command syntax generated by the Data Definition Wizard if the "Convert Imported Unicode Data to ASCII" table option is selected, or if the "Convert Imported Unicode Data to ASCII" check box is selected in the Data Definition Wizard via either the Select Data Source or Subsystem panel or the File Format panel. For pre-existing procedures containing Import OPENEXCEL command syntax, issue the SET SQLASCII ON command at the top of the procedure to ensure that ASCII data is imported.

Note: Please note that this Import only creates a flat file - a live read is not available for this Import type.

Note: Please not that this Import does not allow joining of tables.

For users requiring functionality to select columns, join tables or to read data live, it is recommended that they import Excel Spreadsheets via ODBC (see Import command syntax shown directly below).

Importing MS Excel Spreadsheets or MS Access Databases via ODBC

IMPORT {ODBC|EXCEL|ACCESS} TO table-layout

FILE "<external-file>" TABLE "source-table"

<USERID userid> <PASSWORD n>

SERVER <server-profile-name|LOCAL>

SOURCE "source-name" FLAT "flat_file_name" <ASCII>

{<FIELDS <column-list,… | ALL> END>}

{<WHERE filter END><CONDITION condition END><ORDER order END>|<SELECT select-statement>}¿

Note: Import ODBC syntax is generated whenever a local ODBC data source or an Arbutus Windows Server relational data source is defined and flattened using the Data Definition Wizard.

Note: Use the optional ASCII keyword if you want any UTF-16 data that is read to be outputted as ASCII. This option should only be used if you are confident that the data only contains North American English characters.

Note: The ASCII keyword is automatically inserted into the Import ODBC command syntax generated by the Data Definition Wizard if the "Convert Imported Unicode Data to ASCII" table option is selected, or if the "Convert Imported Unicode Data to ASCII" check box is selected in the Data Definition Wizard via either the Select Data Source or Subsystem panel or the File Format panel. For pre-existing procedures containing Import ODBC command syntax, issue the SET SQLASCII ON command at the top of the procedure to ensure that ASCII data is imported.

Note: For EXCEL, ACCESS and ODBC imports, field modifiers Width, As and Decimals can be used for individual fields in the field list to override default values.

Table Aliases

Table name aliases are supported for these Import types. Aliases are used to simplify column names in the column list, Where, Condition, and Order clauses of the SQL select statement. An alias is simply an alternate name for a schema (or owner) and table name combination. To create an alias, specify the alias in the TABLE parameter, like "dbo.AccountsPayable A" (where "A" is the alias), and then use the alias to take the place of dbo.AccountsPayable in column name references (e.g., A.column1, A.column2, etc.).

Importing DBF files

IMPORT DBASE TO table_layout "source_dbf.DBF"

<SERVER server-profile-name>¿

Importing Delimited files

IMPORT DELIMITED TO table_layout <RECORD_LENGTH n>

FIELDS {field_name type start length <decimals|PIC "date_format">,…} END "source_delimited.TXT" a b c

<SERVER server-profile-name>¿

Importing Excel (XLS21) spreadsheets

IMPORT XLS21 TO table_layout <RECORD_LENGTH n>

FIELDS {field_name type start length <decimals|PIC "date_format">,…} END "source_spreadsheet.XLS" "flat_file.FIL" x y "sheet_name" <cell_range>

<SERVER server-profile-name>¿

Importing Flat Files

IMPORT FILE TO table_layout <RECORD_LENGTH> n

FIELDS {field_name type start length <decimals|PIC "date_format">,…} END "source_file" <SERVER server-profile-name>¿

Note: The FILE Import type enables importing of any flat file by specifying the start position for each of the physical fields.

Importing JSON and XML files

Given the complexities of your JSON or XML file, the Import command syntax can easily become very complex and therefore it is always best to generate the necessary Import command syntax via the Data Definition Wizard by selecting either the JSON or XML options in the File Format panel.

Importing Print Image Files

Creating the Import syntax for a print image file can be very complex. An example of the import syntax necessary for importing a very basic print image file is:

IMPORT PRINT TO report2 "report.fil" FROM "report.txt" SERVER "LOCAL" ASCII RECORD "Detail" 0 1 0 TEST 0 0 AT 1,59,59 7 "." FIELD "Field_1" C AT 1,6 SIZE 2,1 DEC 0 WID 2 PIC "" AS "Field_1" FIELD "Field_2" C AT 1,8 SIZE 2,1 DEC 0 WID 2 PIC "" AS "Field_2" FIELD "Field_3" C AT 1,10 SIZE 5,1 DEC 0 WID 5 PIC "" AS "Field_3" FIELD "Field_4" C AT 1,16 SIZE 24,1 DEC 0 WID 24 PIC "" AS "Field_4" FIELD "Field_5" N AT 1,40 SIZE 10,1 DEC 0 WID 10 PIC "" AS "Field_5" FIELD "Field_6" N AT 1,50 SIZE 12,1 DEC 2 WID 12 PIC "" AS "Field_6" FIELD "Field_7" N AT 1,62 SIZE 12,1 DEC 2 WID 12 PIC "" AS "Field_7" ¿

Depending on the complexity and number of record types contained within your print image file, the IMPORT PRINT syntax can easily become very complex and therefore it is always best to generate the import syntax via the Data Definition Wizard by selecting the Print Image (Report) option in the File Format panel.

Specifying Character Set for Print Image File

When importing a print image file, you must specify the character set of the imported file that is to be flattened. To do so, you must use one of the following acceptable character set values:

Additionally, for ASCII data, you can specify the ASCII code page to use when the print image file being imported uses ASCII character set that is different than that used on your local machine (for example, your local machine uses a US ASCII character set and you are trying to read data stored in a print image file using a Japanese ASCII character set). The optional code page syntax that must immediately follow the character set declaration is:

CODEPAGE n

Where n is the desired ASCII code page number for the file being imported. If not specified then the ASCII data is assumed to use the same ASCII code page as the character set of the local machine.

When the print image file contains tabs for report spacing, you can optionally specify the tab spacing that is appropriate. The optional tab spacing syntax that must immediately follow the character set declaration is:

TAB n

The general command syntax order for including all of these optional parameters is:

IMPORT PRINT TO table_layout_name "data_file_name.fil" FROM "print_image_file.txt" <SERVER LOCAL|1|server_profile_name> ASCII|EBCDIC|UTF8|UTF16|UTF16BE <CODEPAGE n> <TAB n> RECORD … ¿