Once a database is connected and settings have been configured, it's time to import. This article covers these topics:
  1. Database Name
  2. Bulk Import
  3. Named Table Import
  4. Detect File Type
  5. Define File Type
  6. Fixed-Width Files
  7. Starting the Import

Database Name

After a database is connected, the active database can be changed by entering its name into the 'Database Name input'.

Bulk Import

Bulk Import is the easiest way to move data into a database with SQL Importer. When turned on, Bulk Import makes it possible to select and import multiple files in a single pass. Tables will be named based on selected input filename(s), so the filenames must have valid table names for the active database type.

Named Table Import

Whereas Bulk Import makes it possible to import more than one file in a pass, 'Named Table Import' will only import one file to a specific table name. Enter a destination table name into the 'Table Name' input. 'Named Table Import' is only available when Bulk Import is turned off.

Although a destination table name is set when using 'Named Table Import', the destination table will still be created by SQL Importer.

Auto Detect File Type

When enabled, 'Detect File Type' will analyze files to determine how they are delimited or if they are fixed-width. 'Auto Detect File type' will work for files with the same or different delimiters selected together using Bulk Import or when a single file is being imported using Named Table Import.

Define File Type

When Auto Detect File Type is turned off, delimiter information is gathered from 'Define File Type' settings. For delimited files, type the delimiter into the 'Delimiter' input, or for tab-delimited files, toggle 'Tab Delimiter' on. If columns are qualified with double quotes, toggle 'Quoted Columns' on. If a file has a fixed-width text format, toggle 'Fixed-Width' on.

'Define File Type' must reflect all files selected when using Bulk Import. Otherwise, enable the 'Auto Detect File Type' feature instead.

Fixed-Width Files

Fixed-width files allocate specific starting and ending positions for each column, ensuring uniformity in column width. In contrast to delimited files, where columns are separated by a delimiter character, fixed-width files demand precise alignment of columns. For instance, if the first column is assigned 40 bytes, it will consistently occupy that length. Spaces fill any gaps to maintain the designated width for values shorter than the column length. Consequently, the record size of a fixed-width file remains constant.

In order to handle fixed-width files, SQL Importer requires a layout file. The layout file, which defines column positions, must have the same name as the fixed-width input file itself, except with a .lyt extension. This is how SQL Importer binds a fixed-width file to its layout. For example, if a fixed width file is named FixedText.txt, its layout file must be named FixedText.lyt.

The layout (.lyt) file is expected to have a header separated by commas. This header can include specific combinations of the following labels: NAME, START, END, and LENGTH.

  • When the NAME label is included, the values provided under this label will be used as column names in the destination table. If NAME is not included, generic names will be assigned to the columns in the destination table.
  • START denotes the starting byte position of a column, while END indicates the ending byte position of the column. It is recommended to provide either START and END, or START and LENGTH. Alternatively, providing only LENGTH is acceptable. However, it's important to note that the accuracy of parsing fixed-width files depends on the accuracy of this information.

Starting the Import

The 'Choose Files and Import' will start the import process. Files selected from the file browser will begin importing while status updates will appear in the left and right panes of the interface.