Delimited (Free-Format) ASCII Files

Restriction: This topic applies to Windows environments only.

Use this format to import data from delimited, free-format ASCII files. ASCII format is a common means of transferring data from one program to another. Many software applications (including SQLWizard) provide a means of creating and reading ASCII output files. There are two ASCII file formats:

Delimited, free-format ASCII Files allow variable field lengths-- each field is separated by a field delimiter.
Columnar, fixed-format ASCII Files have fixed field lengths-- data is arranged in columns. Every record fits into a standard form, so field delimiters are not required.

The new XDB Server table definition suggested by SQLWizard when it examines a free-format ASCII file depends entirely on the data found in the file. You can examine the file yourself by using the Source File command from the View menu. If needed, you can change the suggested table definition in the table display area on the Import window.

When you use the Browse button to choose a file to import, SQLWizard defaults the file listing to the *.fre extension. The file you are importing can have a different extension.

The default field delimiter assumed for free-format ASCII files is an @ symbol. You can change this on the Import window or you can change the default by using the Defaults command from the Import menu.

The following table shows you the delimited ASCII format options:

Field Delimiter	You must specify the field delimiter that is used to indicate where each field ends. The field delimiter differs from the record delimiter. The field delimiter is entered as a keyboard character or as an ASCII decimal code for a character. Decimal codes must be enclosed in angled brackets. For example, if the source file uses a # as the field delimiter, enter the character # or the string <35>. To use the < or > character as a delimiter, you must enter the ASCII decimal codes <60> or <62>, respectively. If you don't know what the field delimiter is, use the Source File command from the View menu to display the source file. Optionally, you can use an ASCII text editor to examine the contents of the source file. If the field delimiter contains nonvisible ASCII characters, you might have to use a hex editor to examine the contents of the file. If you specify a field delimiter that is not found in the file, you will not be allowed to continue. If your data uses a delimiter character that also appears in the data values, the system might try to create a table with too many fields. You can use a delimiter that is found in the data values only if the data values are surrounded by double quotes. If you use a double quote character as the delimiter, the fields cannot also be enclosed in quotes. Do not use a comma as the delimiter if your data contains numeric values having commas as a grouping character.
Record Delimiter	The record delimiter for standard ASCII files is a carriage-return followed by a line-feed, as indicated by the default <013><010>. If your file uses a different end-of-record delimiter, you can change this setting. For example, you can import a file using the record delimiter <005><010>.
Values In Quotes	If field values in your data contain quotes, click the Quotes checkbox to select it. Otherwise, be sure the Quotes checkbox is deselected. If data values contain quotes, you cannot use a quote as the field or record delimiter. If fields are enclosed in quotes (either double- or single-), any delimiter character found within a pair of quotes is treated as part of the string. For example, if you are using a comma as a delimiter, and your source file contains the string "Washington, DC 20021", the entire string will be treated as one field, even though it contains the delimiter character. There must be a matching quote character for every quote character in the file. If your fields are enclosed in quotes, do not use quotes as a delimiter. The system suggests a character data type for any columns of data enclosed in quotes; you can change the data type on the Field Definitions Grid at the bottom of the Import window.
Heading	If the first row of the file you are importing contains field names, click the Heading checkbox to select it. This tells Import to use those names as the column names in the table you are creating. If record data begins in the first row, then uncheck the Heading checkbox to turn off inclusion of a heading row.
Insert Nulls As Defaults	Select this checkbox if you would like to insert the columns' default values in place of NULLs in the source file for those defined WITH DEFAULT.