Settings

SQL Importer includes a lot of options and settings. This article covers these settings categories:
  1. Parsers
  2. Special Tables
  3. Default Column Type
  4. Optimize Columns
  5. Intelligent Data Types
  6. Non-Optimized Columns
  7. Table Structures Only
  8. Special Columns
  9. Fix Broken Records
  10. Other Fixes
  11. Log Errors
  12. Buffer

Parsers

SQL Importer's parsing logic draws from decades of frontline data experience. SQL Importer includes two parsing methods that use different approaches. This provides for a fallback method in the event of an issue with a particular file.

  1. Logic Parser - Uses a more detailed approach to parsing your files. It is quite fast, accurate, and the default parser.
  2. Precision Parser - Generally the faster parser for most use cases. It is both accurate and effective.

Special Tables

SQL Importer can create special tables that accompany imports:

  1. Exceptions - Writes unparsable records to an exceptions table for the current table. For example, if a file named "test.csv" contains unparsable records that can't be imported, the unparsable records will be written to "test_SQL Importer_Exceptions".
  2. Fixes - Writes initially unparsable records that SQL Importer was able to fix to table for the current table. For example, if a file named "test.csv" contains unparsable records that SQL Importer fixes, the records fixed will be written to "test_Cullinet_Fixes". This allows you to review the fixed records to make sure they look okay.
  3. History - Maintains a record of files imported into the database capturing the following information: Imported Filename ("Filename"), Destination Table Name ("Table_Name"), Import Date ("Import_Date_Time"), Number of Records Fixed ("Records_Fixed_Count"), Number of Exception Records ("Records_Exceptions_Count"), and Record Count ("Import_Record_Count"). Depending on the database platform and permissions, the table name may include a schema (e.g., SQLImporter.History) or simply be named "SQLImporter_History". The History table is created locally within each database when it doesn't already exist. Data will continue to be added to the History table with each import as long as the option is enabled.

Drop Table Warning

The 'Drop Table Warning' feature will provide confirmation message before a dropping and recreating a table that already exists in the target database. This feature is enabled by default.

We recommend that the Drop Table Warning be turned on to prevent tables from being unintentionally overwritten.

Column Names from Header

When enabled, 'Column Names from Header' will base column names on the first row of the input file. This assumes that the input file has a header row. If this setting is turned off, columns will be assigned generic names (COLUMN1, COLUMN2, etc.).

SQL Importer includes duplicate column name detection. If a header row uses the same column name more than once, SQL Importer will add a suffix to the column name to make it unique.

Default Column Type

By default, all columns SQL Importer creates are defined as string. If Intelligent Data Types is enabled, SQL Importer will attempt to identify and convert other data types as well. However, the default fallback data type will always be string. Default string column characteristics can be set from the 'Default Column Type' dropdown:

  1. Varchar - Defaults all fields to the varchar data type.
  2. Unicode - Defaults all fields to the unicode/nvarchar data type.
  3. Allow Nulls - Sets columns to allow null values. Otherwise, columns will be set to not null.

Database platforms differ in how they name and handle varchar and unicode fields. SQL Importer will find the closest equivalent for the active database type.

If Optimize Columns is enabled and the data in a column exceeds the limitations of the default column type, SQL Importer will change the column's data type to TEXT or the closest equivalent.

Optimize Columns

Optimize Columns is a key feature of SQL Importer When enabled, SQL Importer will size column lengths based on the input data. Optimize Columns is also required to use Intelligent Data Types.

We recommend that 'Optimize Columns' be enabled for most use cases.

Intelligent Data Types

SQL Importer can evaluate data and assign data types, including: Boolean, Currency, DateTime, Decimal, and Integer. Data types to evaluate can be selected from the 'Intelligent Data Types' dropdown button. SQL Importer will look for consistent values that comply with the active database's type conventions and adjust data types accordingly.

Consistency is important for successful data type conversion. For example, if a column always contain common boolean indicators (True/False, Yes/No, On/Off, 1/0, etc.), SQL Importer will convert the values to "1" or "0" appropriately and change the data type. However, if a column sometimes contains non-boolean values (including null), a string data type will be assumed.

Some data types have special settings, which can be configured from the 'Data Type Settings' dropdown button.

  1. Currency is identified by the presence of a monetary symbol. The decimal precision for currency can be set or the Money data type can always be used when supported (e.g. SQL Server®).
  2. Since date conventions vary by region, SQL Importer will test date formats using the selected date convention. If the 'Month First' date convention is selected and a day-first date is detected, the data type will not be converted.

The Optimize Columns feature must be turned on in order to use Intelligent Data Types.

Non-Optimized Columns

When Optimize Columns is turned off, all columns in a table will be assigned the same length. This 'Global Column Length' is set in the 'Non-Optimized Columns' dropdown. The value set should be long enough to accommodate the longest value in any column. For example, if the longest value on a file is 400 characters long, the 'Global Column Length' should be 400.

In the event that the 'Global Column Length' setting isn't long enough to handle all values, the import process will fail. For example, 401 characters will not fit into a column sized for 400 characters. However, if 'Truncate Data When Longer' is toggled on, any value exceeding the defined column length will be truncated. That is, when an input value exceeds the 'Global Column Length' setting, the rightmost portion of the value in excess of the column length will be cut off. This may result in loss of data but will ensure that a file can be successfully imported.

We recommend using the Optimize Columns feature rather than 'Non-Optimized Columns' for most use cases.

Intelligent Data Types are not available with Non-Optimized Columns.

Table Structures Only

When enabled, 'Table Structures Only' will build table structures based on selected files without actually importing any data. If Optimize Columns is enabled, column lengths will be optimized and refelect any Intelligent Data Types that are being screened for in the input file. Otherwise, table columns will strictly reflect Default Column Type and Non-Optimized Columns settings.

Special Columns

SQL Importer can append and populate special columns for each file you import.

  1. SQLImporter_DateTime - Contains the date and time the file was imported.
  2. SQLImporter_Filename - Contains the filename that was imported.
  3. SQLImporter_Sequence - Contains a unique sequence number for each row imported.

Fix Broken Records

When enabled, Fix Broken Records will attempt to fix records that break onto one or more lines. For reference, records that are successfully fixed will be written to the SQLImporter_Fixes table when enabled in Special Tables. Records that cannot be fixed will be written to the SQLImporter_Exceptions table when enabled in Special Tables.

Other Fixes

In addition to fixing broken records, SQL Importer can correct other data issues. Choose to enable any of the following fixes from the 'Other Fixes' dropdown:

  1. Replace embedded carriage returns with spaces - Replaces carriage return characters within columns with spaces.
  2. Replace embedded line feeds with spaces - Replaces line feed characters within columns with spaces.
  3. Trim leading spaces on all columns - Removes leading spaces from each column.
  4. Trim trailing spaces on all columns - Removes trailing spaces from each column.
  5. Drop extra columns - Drops extra columns that exist on a record relative to the table structure, moving from right to left.
  6. Add missing columns - Appends blank columns to records until they have the correct number of columns for the table structure.

Log Errors

When 'Log Errors' is enabled, SQL Importer will write errors to a Logs folder in the application's data directory. We recommend keeping this setting turned on to help troubleshoot any issues that may arise.

Buffer

SQL Importer loads data into a record buffer during import. This buffer range can be set from 1 to 1000, which does not necessarily correspond to number of records. In general, we recommend that the buffer be set to its maximum setting (1000) unless troubleshooting an issue. Import speed will slow exponentially at lower settings.