User Guide > Map Connectors > Source and Target Map Connectors > ASCII (Delimited)
Was this helpful?
ASCII (Delimited)
ASCII stands for American Standard Code for Information Interchange. Delimited ASCII is a data format in which fields and records are separated by selected characters called delimiters. Field and record delimiters are distinct. You can set the delimiters in the source or target properties for this connector.
• Multiple Structured Schemas Possible - Delimited ASCII files can contain multiple structured schemas. The procedure used for setting up a delimited ASCII multiple structured schema file is the same as for a fixed ASCII multiple structured schema. For more information, search for the keywords "multiple record types."
• Delimited ASCII as Alternate Source or Target Connector - Delimited ASCII files are among the most universal types of data files used to transfer data between software applications. Many applications have the ability to import data from or export data to a delimited ASCII file. Even if the application is not directly supported, you can transform its exported delimited ASCII source data. On the target side, if an unsupported application can import delimited ASCII, use the integration platform to output delimited ASCII files for importing.
• Using an External Schema to Override Source Structure - When ASCII (Delimited) is the source connector, the data structure is normally set by field delimiters and the header record of the source file. However, after connecting to a file, you can override this structure by selecting a schema, for example, to change field names, change their size, or even add additional fields for multiple record layouts.
• Using an External Schema to Override Target Structure - When ASCII (Delimited) is the target connector, the data structure is normally set by field delimiters and the header record of the target file. However, after connecting to a file, you can override this structure by selecting a schema, for example, to change field names, change their size, or even add additional fields for multiple record layouts.
Note that applying an external schema with fewer fields than the target file prevents writing of fields beyond the range of the schema.
• Delimiter Characters Occurring as Data within a Field - Characters that delimit the start and end of a field may also appear as data within the field. To ensure that a data character is not interpreted as a delimiter, the integration platform creates an escape sequence by doubling the character when it is assumed to be data.
Quotation marks are a common example of this escape sequence. As shown below, the quotation marks enclose quoted words in an Excel source field. In the mapping to a delimited ASCII target with a quotation mark selected as field delimiter, the quotation marks are doubled for the data but not for the delimiters enclosing the field.
Excel source
The customer said, "A penny saved is a penny earned."
Delimited ASCII target, with field delimiters
"The customer said, ""A penny saved is a penny earned."""
Connector-Specific Notes
• HeaderRecord Property in the Target - If the HeaderRecord property is set to true, a header is written only for the first record type in a multiple record layout.
All records are written using the same properties.
• HeaderRecord Property in the Source - If the HeaderRecord property is set to true, then a single header record is skipped at the beginning of the file. If there are later header records for the additional record types, they appear as data and can possibly cause errors.
All records are read using the same properties.
Unless truncation handling is turned off (set to Ignore), each record is read twice. To read a single source record requires reading the discriminator record, then if the discriminator indicates a different record type we must reread with the new record type. However, while reading the discriminator record, we have to momentarily turn truncation handling off, so even if the discriminator record indicates itself as the record type, if truncation is not set to ignore we must turn it back on and reread the record.
Simply connecting to a source file produces only one structured schema. If the file has multiple record types, you must use Structured Schema Designer to manually design the record types.
• Catching Schema Mismatch for One Record Type - You can add the “SingleRecordSchemaCheck” parameter in the “Preferences” section of the Cosmos.ini file and set it to “1”(ON). For more information, see Cosmos.ini Settings.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target.
The settings that are available depend on the connector you select.
For a list of all parts for source connectors, see Specifying Connector, Parts, and Properties.
For a list of all parts for target connectors, see Specifying Connector, Parts, and Properties.
Property Options
These are properties that you may need to specify for your source or target data. After you have selected all needed settings for these options, apply them to the connection.
Delimited ASCII data has special characters between fields and records. Another name for delimited ASCII files is CSV (comma separated value) data. Excel spreadsheets, for instance, can be saved as .csv files.
When delimited ASCII is the source, the fields are assumed to be separated by commas and delimited by quotation marks. Records are assumed to be separated by a carriage return-line feed (CR-LF). If your source file contains different separators or delimiters, you must specify them by clicking the Source Properties icon in the toolbar and choosing the correct separators and delimiters for your source data file. If you do not know what the separators or delimiters are in your source file, right-click the ASCII (Delimited) Properties grid in the right panel and select Show Delimiters to open the Hex Browser.
When delimited ASCII is your target connector, the default field separators are commas (,) and the delimiters are quotes ("). The default record separator is a carriage return-line feed (CR-LF). If different separators or delimiters are needed, you can specify them by clicking the Target Properties icon in the toolbar and choosing the desired separators and delimiters for your target file. When you are creating a delimited ASCII file that is imported into another application, you should set each of the target property options as required by the other application before creating the file.
If the field or record separator is not listed
1. Highlight the default separator.
2. Enter the separator as an ASCII character.
3. Example: If the separator is a line feed, enter:
LF
4. To use a hex value instead, go to step 3.
5. Enter a backslash, an "x", and the hex value for the separator.
6. Example: If the separator is a line feed, enter:
\x0a
For a list of the 256 standard and extended ASCII characters, search for "hex values" in the documentation.
You can specify the following source (S) and target (T) properties:
Property
S/T
Description
AlternateFieldSeparator
S
Most data files have only one field separator between all the fields. However, it is possible to have more than one field separator. If your source file has one field separator between some fields and a different separator between others, you can specify the second field separator here. Otherwise, leave this set to None (the default).
To select an option other than the default, click AlternateFieldSeparator. Then click the arrow to the right of the box to choose from the list of available separators. To specify a field separator that isn't on the list, type it here.
AutomaticStyling
S
Changes the way the integration platform reads or writes ASCII data. By default, AutomaticStyling is set to false, causing all data to be read or written as Text. When you change the setting to true, the integration platform formats particular data types, such as numeric and date fields, automatically.
During transformation of ASCII source files, autostyling ensures that a date field in the source file is formatted as a date field in the target file, and not as character or text data. If your source file contains zip code data, you may want to leave AutomaticStyling set to false so that leading zeros in some zip codes in the eastern US are not deleted.
For an ASCII target file, if you set FieldDelimitStyle to Text, you must also set AutomaticStyling to true so that delimiters are placed around only the nonnumeric fields.
CodePage
S/T
Translation table that specifies which encoding to use for reading and writing data. The default is ANSI, the standard in the US.
EmptyFieldsNull
S
Treats all empty fields as null.
Field1IsRecTypeId
S
If the first field of each record in your source file contains the Record Type ID, you can select true for this property and each record is treated as a separate record type. Within each record, field names derived from the Record Type ID are automatically generated for each field. For details, see Field1IsRecTypeId.
FieldDelimitStyle
T
For delimited ASCII target connectors, this option determines whether the specified FieldStartDelimiter and FieldEndDelimiter are used for all fields, only for fields containing a separator, or only for text fields:
• All – Places the delimiters specified in FieldStartDelimiter and FieldEndDelimiter before and after every field. Default setting is All. For example: "Smith","12345","Houston"
• Partial – Places the specified delimiters before and after fields only where necessary. A field that contains a character that is the same as the field separator will have the field delimiters placed around it. A common example is a memo field that contains quotes within the data: "Customer responded with "No thank you" to my offer".
• Text – Places delimiters before and after text and name fields (non-numeric fields). Numeric and date fields have no FieldStartDelimiter or FieldEndDelimiter. For example: "Smith", 12345,"Houston", 11/13/04
• Non-numeric – Places delimiters before and after all nonnumeric types, such as date fields. Non-numeric delimits date fields, while text does not.
FieldEndDelimiter
S/T
Most delimited ASCII files have beginning-of-field and end-of-field delimiters. The default delimiter is a quotation mark. This option is available for source files and target files that do not have delimiters. To read from or write to a file with no delimiters, set FieldEndDelimiter to none.
FieldSeparator
S/T
A delimited ASCII file is assumed to have a comma between each field. To specify another field separator, click FieldSeparator to select one from the list.
To specify a field separator that is not on the list and is a printable character, highlight the CR-LF and then type the character. If the field separator is not a printable character, replace CR-LF with a backslash, an X, and the hexadecimal value for the separator.
FieldStartDelimiter
S/T
Delimited ASCII files often have beginning-of-field and end-of-field delimiters. The default delimiter is a quotation mark. To read from or write to a file with no delimiters, set FieldStartDelimiter to none.
Header
S/T
In some files, the first record is a header record. For source data, you can remove it from the input data and cause the header titles to be used automatically as field names. For target data, you can cause field names in your source data to automatically create a header record in your target file. To identify a header record, set Header to true. The default is false.
Note:  If your target connector is ASCII (Delimited) and you are appending data to an existing file, leave Header set to false.
MaxDataLen
T
Specifies the maximum number of characters to write to a field. When set to zero (the default), the number of characters written to a field is determined by the length of the field. If you set this value to a number other than zero, data may be truncated.
NullIndicator
S/T
Special string used to represent null values. Select a predefined value or type any other string.
• Target – When writing a null value, the contents of the null indicator string are written.
• Source – A check is made to see if the null indicator is set. If it is set, the data is compared to the null indicator. If the data and the null indicator match, the field is set to null.
NumericFormatNormalization
S
When set to true, this property handles thousands-separators according to usage for locale when numeric strings are converted to numeric type. This property overrides any individual field settings. Supported in 9.2.2 and later. Default is false.
RecordFieldCount
S
If your source file has field separators but no record separator or uses the same separator for both fields and records, follow these steps:
1. Set the RecordSeparator (most likely a blank line).
2. Leave the AlternateFieldSeparator option blank.
3. Enter the number of fields per record for RecordFieldCount.
4. The default value is zero.
RecordSeparator
S/T
Most delimited ASCII files have a carriage return-line feed (CR-LF) between records. To use a different character, click the RecordSeparator cell, then click the arrow and select one from the list. The SystemDefault setting enables the same transformation to run with CR-LF on Windows and with LF on Linux systems without having to change this property.
To use a record separator that is not listed and is a printable character, highlight CR-LF and enter the character. For example, to use a pipe (|) character, enter a pipe from the keyboard.
If the record separator is not a printable character, enter escape CR-LF with a backslash, an X, and the hexadecimal value for the separator.
StartOffset
S
If your source data file starts with characters that need to be excluded from the transformation, set the StartOffset option to specify at which byte of the file to begin. The default value is zero. The correct value may be determined by using the Hex Browser. Note: This property is set in number of bytes, not characters.
StripLeadingBlanks
S/T
Leading blanks occur in ASCII source files by default. To delete them, set StripLeadingBlanks to true.
Leading blanks are removed from ASCII target files by default. To retain them, set StripLeadingBlanks to false.
StripTrailingBlanks
S/T
Trailing blanks occur in ASCII source files by default. To delete them, set StripTrailingBlanks to true.
Trailing blanks are removed from ASCII target files by default. To retain them, set StripTrailingBlanks to false.
StyleSampleSize
S
Allows you to set the number of records (starting with record 1) that are analyzed to set a default width for each source field. The default value for this option is 5000. You can change the value to any number between one and the total number of records in your source file. As the number gets larger, more time is required to analyze the file, and it may be necessary to analyze every record to ensure that no data is truncated.To change the value, click StyleSampleSize, highlight the default value, and type a new one.
TransliterateIn
T
Specifies a character or set of characters to be filtered out of the source data. For any character in TransliterateIn, the corresponding character from TransliterateOut is substituted. If there is no corresponding character, the source character is filtered out completely. TransliterateIn supports C-style escape sequences such as \n (new line), \r (carriage return), and \t (tab).
TransliterateOut
T
Specifies a character to be substituted for another character from the source data. For any character in TransliterateIn, the corresponding character from TransliterateOut is substituted. If you wish the source character to be filtered out completely, leave this field blank. If there are no characters to be transliterated, leave this field blank. TransliterateOut supports C-style escape sequences such as \n (new line), \r (carriage return), and \t (tab).
Field1IsRecTypeId
If your first record consists of the following:
"Names", "Arnold", "Benton", "Cassidy", "Denton", "Exley", "Fenton"
The following field names are assigned:
Names_01
Names_02
Names_03
Names_04
Names_05
Names_06
Names_07
Names
Arnold
Benton
Cassidy
Denton
Exley
Fenton
The default for this property is false.
Length
These are field lengths in your data. If you need to change field lengths, reset them in the schema.
For example, a field contains numbers and dollar values with two decimal places, such as 7122.50. The field type default is Text and because of values in other records, the field length default is 10. You are transforming the data to a database application in which you want the data in this field to be numeric. If you change the source field type to Float, the field length becomes blank, the precision default is 15, and the decimal changes to 2. This field automatically appears as an appropriate numeric data type in your target schema and is a numeric field in your target data file.
Supported Data Types
The following data types are supported:
• Boolean
• Date
• Date/Time
• Decimal
• Float
• Integer
• Name (parses and displays a name into its parts, such as title, last name, middle initial)
• Text
• Time
By default, ASCII data is read as Text. Fields containing dates or numbers may be changed to another appropriate data type.
If you want to set different field delimiters for your fields that contain numeric data, see FieldDelimitStyle in the property options.
Last modified date: 02/09/2024