Import data

You can use this function to load data from text files into the tables of a database. The text files can be in either 'free' or 'fixed' format. In a free format text file, the fields to load are separated by a specified delimiter, such as a comma. In a fixed format text file, the fields to load are in specific columns which have a fixed length.

If you have any non-numeric data that you want to load into a numeric field in your database, you must first establish a numeric equivalent for them by inserting an appropriate translation record for them into the translation table of the database before loading. See Translation Table.

Prerequisites: A database is open.

To run this function: Choose Database > Database > Import data, or Design > Blast design > Database > Import data, or...

In the Function Chooser, type LOAD DATABASE, and press ENTER.

Fields on the Select format for load/unload form

Field	Description
Database Name	Read-only. The name of the database.
Format File Name	The name of the format file. If you type a new name, after you click Apply, and confirm the creation of the file, the Select database tables to include in format form is displayed. If the format file already exists, the Load tables from text files form is displayed.
Report file name	The name of a report file to hold any errors or information messages that occur during the import.
Format	The file type and file extension of the report file.

After you click Apply, if you typed the name of a new format file, the Select database tables to include in format form is displayed. Otherwise, the Load database tables from text files form is displayed.

Fields on the Select database tables to include in format form

Field	Description
Table Name	Read-only. The names of each table in the database.
Include	Whether or not to include the table in the format file.
Format	FREE. This is suitable when the fields in the text file are separated by a specified delimiter, such as a comma. FIXED. This is suitable when the fields to load are in specific columns that have a fixed length.
Delimiter	Only available for FREE fields. The delimiter is the character that separates fields in the text file that you are about to load.
Space Fill	Only available for FREE fields and only relevant when outputting.text files from a database. Selected. A space is output after the delimiter. Cleared. No space is output after the delimiter.
Text Qual.	Only available for FREE fields. The text qualifier is placed around any text and memo fields in the database, allowing you to have special characters (for example line breaks) in a text field. It is strongly recommended that you use a text qualifier at all times, but is only essential if the text field contains either line breaks, or the character defined as your delimiter. For the qualifier to be correctly read into the database, the qualifier must be doubled. For example, the following is a valid text file, as output by Surpac and some other database products. It uses a comma delimiter and a double quote text qualifier. The following field value : This is a test of "double" quotes Will be output to a text file out of Surpac as: "This is a test of ""double"" quotes" When this field is read back, the doubled quotes ("") will be converted back to a single quote (") and so the data is not changed. If the data did not contain the double quotes, however (for example the following) : "This is a test of "double" quotes" The data would be read into Surpac as This is a test of quotes because it stops reading at the first quote, and begins again at the second one. For some field values, this could cause validation problems. For this reason, the qualifier must match what appears in the data. Another reason that the qualifier chosen when loading a text file must be correct, is that nothing may be loaded from the text file into the database. Loading to the database only begins when the text qualifier is first encountered.

When you click Apply, the Select fields to include in format form is displayed.

Fields on the Select fields to include in format form

Field	Description
Table Name	Read-only. The names of each table in the database.
Field Name	Read-only. All the field names of the selected tables.
Include	Whether or not to include the field in the format file.
Column	For a FREE format field, this field shows the column number in the text file. The column before the first designated delimiter in the text file is column one. The column that follows the delimiter is column two, and so on. For a FIXED format field, this field shows the start position of the field in the text file. The first position is column one with the column numbers incremented for each character position on the line.
Length	Length is writeable only for FIXED format fields. It is the length of the field from the start column.
Format	Read-only. Shows whether the field is FREE format or FIXED format.

Click Apply to display the Load database tables from text files form.

Fields on the Load database tables from text files form

Field	Description
Perform overlapping sample check	After time-independent interval tables are loaded, an overlapping sample check is performed to find any overlapping samples. You can choose whether or not to perform this test to improve database load time. It is recommended that you allow the overlapping sample check to occur unless you are certain that your data contains no overlapping samples. The time saving is only minimal if you do not perform the check.
Maximum errors allowed during load	You can nominate the number of errors that can occur during the load before the load terminates. This is recommended because a large number of errors would indicate problems in the format file, your text files or the data itself. All errors are logged in an associated database log file, and rejected records are written into a reject file.
Table Name	Read-only. The name of the table to load the data into.
Text File Name	The name of the text file to load into the database table. You can leave this field blank if you do not have a text file to load. This feature enables you to reuse format files in which there are more tables included than you have text files.
Load Type	The type of load. Insert An insert only load is performed. This means only records which do not exist in the database table are loaded. All duplicate and existing records in the text file are rejected. Update An update only load is performed. With this style of load, only records which already exist are updated. Non- existing records are rejected. Ins/Upd An insert and update load is performed. In this mode, records that already exist in the database are updated and records which do not are inserted.

Output

The text files are imported into the database. A <databaseName>.log file is displayed showing the results of the import. A .dsc format file is created. Any records that were rejected are written to a <databaseName>.rej file.