Import text and comma separated files

SQL DataTool loads text (txt) and comma separated files (csv) into SQL databases. It handles all kinds of exceptions and tests on a record basis whether a record can be loaded into the database. When a record will result in an error while importing, the record is stored in an errorfile. The import will continue, even when some records result in errors.

Import files

To import files, open 'Import data' on the main screen.

The screen like the screen below opens. In the screenprint below, some information about the file has been entered.

Enter the following information in SQL DataTool for the import:

• Select system: system that contains the datamodel/table definition of the file.
• Select version: version that contains the datamodel/table definition of the file.
• Table prefix: whether the imported table will be created with a prefix.
• Source type: the type of the sourcefile - in this case a Text files type.
• Source file: the source file to be imported.

Furthermore, also the text file settings needs to be defined:

• Column separator: the character that defines the column. Column separators consisting of multiple characters are supported.
• Text identifier: a character (or multiple characters) that define texts. This character (or characters) are expected at the beginning and the end of a text field in the source file.
• Decimal separator: the decimal separator that is expected.
• Thousand separator: the sign that is used in the source file that defines thousand (or millions etc).
• Data format: the date format that is used in the source file. The dropdown contains often used date format types, however you can also add one manually. If no date format is entered, the format will be defined during import.
• Table name: the table name to which the data will be imported.
• Append data: when checked, the data in the source file will be added to the existing table. When not checked, the table will be (re)created, and already existing data in the table will be lost.
• Skip rows: the number of rows to be skipped. This is the number of rows before the header line.

The text file settings can be saved (Saved imports). To save an import, check the 'Save?' checkbox behind the parameter(s), enter a new name in 'Saved imports', and click on the save button.

Table prefix

SQL DataTool supports adding a new table with a prefix to a SQL database. In the screen below, the table 'TestTable' is created during import with the table prefix 'new_'. The tablename in the SQL database is 'new_TestTable'.