Use the File Format Editor to configure file formats. The different types of configurable file formats are text files with delimited record values (includes Web Log format), files with fixed-length values and variable length records, IQ binary, and large objects.
Instead of configuring a format for every input file configure the format for all files.
Sample Content pane – displays the contents of a sample document, for which you specified the source path in the Sample File Source field. You can detect delimited file formats by clicking the Detect File Format icon in the toolbar.
Format Properties pane – displays the file format properties and the metadata source.
Preview pane - displays a tabular view of data as it will appear in the destination table. Optionally, to read the column names from a data file, click the Read Column Names from the Data File icon in the Preview pane, provide the line number of the record that contains the column heading, and click OK to confirm. This option is only available to Delimited file formats.
Select Delimited to read and extract structured data from source files containing delimited fields into a destination table in Sybase IQ. Configuring a delimited format in an EL project is similar to configuring a delimited file in the Text Data Provider component of the ETL project.
Specify the other mandatory and optional file properties for the delimited format type:
Character encoding – select the character set encoding with the correct endianness type for character data.
Line delimiter – specify how each row is delimited. Select the delimiter from the list or enter a different delimiter.
Column delimiter – specify how each column is delimited. Select the delimiter from the list or enter a different delimiter.
(Optional) Null byte replacement – set the character to replace null bytes.
(Optional) Skip first rows – specify the number of rows to be skipped in the row sequence.
(Optional) Quote character – specify the quote character pairs used in the file. You can define multiple pairs of quoting characters to support Web log formats.
Select Ignore trailing empty columns option if you want the trailing empty columns used in IQ parallel load files to be ignored.
Select the Read empty values as null option if you want all empty values to be loaded as null values in the destination table.
Delimited format metadata – requires no additional metadata to create destination tables or load delimited files into destination tables.
Fixed-length and IQ binary format metadata – since these formats are specific to each of the IQ destination tables, they require additional metadata to load files into the destination tables. The destination tables must already exist and you must not be required to define columns for each input file. To verify that fixed-length source files load correctly into their destination tables, you can preview the load result for each file in the File Format Editor.
Large objects (LOBs) format metadata - no additional metadata is required when loading LOBs into IQ or for creating the destination tables. The destination tables for LOB sources have the same table structure, created by Sybase IQ InfoPrimer.
Character encoding – select the character set encoding with the correct endianness type for character data.
Line delimiter – specify how you want each row to be delimited. Select the delimiter from the list or enter a different delimiter.
(Optional) Null byte replacement – set the character to replace null bytes.
(Optional) Skip first rows – skip a specified number of rows in the row sequence.
Select the Read empty values as null option if you want all empty values to be loaded as null values in the destination table.
For fixed-length files, the metadata source is always the destination tables. When specifying this format, the destination tables must exist.
Select IQ Binary to extract and load structured data from an IQ binary file into a destination table in Sybase IQ.
Specify the other mandatory and optional properties for the IQ Binary format type:
Byte Order – specify the byte ordering of the input files. Sybase IQ InfoPrimer assumes that the IQ Binary input files have the same byte ordering as the machine where the IQ server is running (default is DESTINATION). You can override the default by specifying HIGH (Big Endian) or LOW (Little Endian) depending on the machine architecture on which the files have been created.
For IQ Binary files, the Metadata Source is always the destination tables. When specifying this format, the destination tables must exist.
Select Large Object to extract and load LOB from file into a destination table in Sybase IQ. EL Projects with LOB file sources, load file content into one or more LOB tables with a predefined structure containing CLOB or BLOB columns, depending on the LOB Type selection.
LOB Type – select BLOB (long binary) or CLOB (long varchar).
Sample Directory – specify a LOB directory and file name pattern in the Sample Content pane. A list of files in the directory appears. A preview of the resulting LOB table displays in the Preview pane.