Documentation

SQL Importer

Online documentation for SQL Importer by Cullinet LLC.

  • Version: 2024.1
  • Publisher: Cullinet LLC
  • Release: 02/04/2024

If you have any questions that are beyond the scope of this documentation, please feel free to contact us.

Getting Started

SQL Importer is designed to make it easy to import data into popular relational databases. It works with SQL Server®, Oracle®, PostgreSQL®, MySQL®, MariaDB®, and Snowflake®. We've tried to make SQL Importer easy and intuitive to use, but please review or refer to this documentation to better better understand how to use the software.

Installation

Follow the steps below to install SQL Importer:

  1. Download the self-extracting installation exe from the download page.
  2. Double-click on the file and enter the location you want to extract it to (or confirm that you want to use the default location).
  3. Once extracted, look for and double-click on the following file to start the program:
    • SQLImporter.exe

SQL Importer is a self-contained application, which means it doesn't require installation to be used.

End-User License Agreement (EULA)

When you first open the application, you'll be asked to review and agree to the following:

  1. End-User License Agreement (EULA)
  2. Terms of Use
  3. Privacy Policy

After confirming that you've read and agree to these documents, the main application will open. If, for any reason, you do not agree to the End-User License Agreement, Terms of Use, or Privacy Policy, you may choose not to accept them, and the software will close.

Lite and Pro Versions

The Lite version of SQL Importer is available to download and use for free without a time limit. However, the Lite version has limitations that make it unsuitable for most commercial or professional users.

  1. Lite is limited to 5,000 records per file.
  2. Lite only allows you to select up to two files to import at a time.

These restrictions are lifted for the Pro version. The Pro version removes any restrictions on record count and the maximum number of files that can be selected for import at once.

We recommended that new users test with the Lite version to ensure they can successfully connect to their database.

Connection Manager

The Connection Manager is where you manage the database platforms you want to work with in SQL Importer. Here you can choose the platform you want to use and configure its connection settings. Supported platforms include: SQL Server®, Oracle®, PostgreSQL®, MySQL®, MariaDB®, and Snowflake®. To open the Connection Manager from the main screen, click the plug icon from the toolbar. Alternatively, you can choose Connection Manager from the File menu.

Using a Database

You set the active database type from the Use database type dropdown. Alternatively, you can directly click the logo of the database type you want to use. The logo of the active database type will be outlined in blue. Once selected, any connection settings you've saved for the active database type will be loaded, and you'll be ready to import data. If you don't frequently switch between database platforms or modify your connection configurations, you may not need to use the Connection Manager often.

From the main sceen, you can also select a database platform you want to use from the Use menu.

Configuring a Database

To configure a database connection using the Connection Manager, click on the hyperlink located beneath the logo of the respective database type. Alternatively, you can access the configuration options for the desired database platform by selecting it from the Configure menu on the main screen.

Each database type has its own set of configuration settings. Enter your settings into the provided form fields. Additionally, you can opt to enter your own connection string by selecting the Use Connection String checkbox in the Connection String section of the form.

You always have the option to decline saving your password when configuring a database connection. If you opt for this choice, you will be prompted to enter your password each time the software attempts to connect to your database. Otherwise, your password will be stored in a configuration file.

The configuration screen has three action buttons:

  1. Test Connection - Use this button to verify that your connection parameters are correct and that a connection can be established. SQL Importer will display a success message upon successfully connecting. Otherwise, it may continue attempting to connect until the timeout period elapses.
  2. Save - Use this button to save your connection settings. These settings will be applied by default the next time you use the database in SQL Importer
  3. Save and Use - Use this button to save your connection settings, use the database, and return to the main screen.

Your connection settings will be saved when you test the connection.

Settings

Please use this section to familiarize yourself with SQL Importer's settings. We're always interested in adding new feaures and options so please do not hesitate to request them.

Parsers

SQL Importer's parsing logic draws from decades of frontline data experience. Each of the three parsers included use different approachs. While they all work effectively, you always have a fallback if you run into an issue with a particular file. This flexibility underscores another advantage of using SQL Importer.

  1. Precision Parser - Generally the fastest parser and recommended for most use cases. It is the default parser.
  2. Logic Parser - Uses a more detailed approach to parsing your files. It is quite fast and accurate.
  3. Hybrid Parser - Has some elements in common with the other two parsers but is more similar to Precision Parser.

Tables

In the Tables page of Settings, you'll find table-level options. The General section has one option:

  • Warn me before dropping a table that exists - When turned on, you will be prompted to confirm that you want to replace a table that already exists.

We recommend that you keep the drop table warning turned on so you don't mistakenly overwrite any tables you did not intend to.

The Special section allows you to choose which special tables you would like SQL Importer to create or use:

  1. Exceptions - Writes unparsable records to an exceptions table for the current table. For example, if you're importing into a table named "Test" and there are unparsable records that can't be imported, the unparsable records will be written to "Test_SQLImporter_Exceptions".
  2. Fixes - Writes initially unparsable records that SQL Importer was able to fix to table for the current table. For example, if you're importing into a table named "Test" and there are unparsable records that SQL Importer fixes, the fixed records will be written to "Test_SQLImporter_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. The History table captures the following information: Imported Filename, Destination Table Name, Import Date, and Record Count. Depending on the database platform and permissions, the table name may include the SQL schema (e.g., SQLImporter.History) or not (e.g., SQLImporter_History). The History table is created locally within each database if it doesn't already exist. Data will continue to be added to the History table as long as this option is enabled.

Columns

The Columns settings page has three sections: Column lengths and names, Default column options, and Special Columns.

In Column lengths and names includes these settings:

  1. Use first row (header row) to define column names - When enabled, the first row on your files will be used to name the columns in each table created. Columns will be assigned generic names when this option is not enabled (e.g., Field1, Field2, etc.).
  2. Optimize column lengths from file content - Sizes your column lengths so they will always be the exact length they need to be, no longer or shorter.
  3. Use this global length for all columns - If you don't want to optimize your column lengths, you can assign your own global column length. Your length setting will have to be large enough to accommodate the longest values in your table. For example, if you have a column with string values up to 500 characters long, every column on your table will have to be sized to accommodate 500 characters. If any of your columns contain data longer than your global column length, the import may fail unless you enable the Truncate data when longer than this option.
  4. Only create table structures - Use this option if you just want to build tables structures based on your files. No data will be imported.

SQL Importer has special handling to rename duplicate column names, such as when a column header is used more than once.

We generally recommend using the Optimize column lengths option when possible.

The Default column options section lets you define default string column data types. Currently, SQL Importer only stores data in string field types (such as varchar or nvarchar/unicode). Once imported, you can cast and maipulate fields as you see fit:

  1. Varchar - Defaults all fields to varchar.
  2. Unicode - Defaults all fields to unicode/nvarchar.
  3. Allow Nulls - Sets your column definitions 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 your database.

If you optimize column lengths and the data in a column exceeds the size limits of your default data type, SQL Importer will change the column's data type to TEXT or the closest equivalent for your database platform.

The Special columns section provides optional columns that SQL Importer can append to imported tables:

  1. Identity/Sequence - Appends a column containing a unique sequential number for each record.
  2. Import Date and Time - Appends a column containing the date and time the table was imported.
  3. Append Filename - Appends a column containing the original source filename.

Fixes

SQL Importer can fix issues with records as they import. Enable the options you want to use:

  1. Replace embedded carriage returns with spaces - Replaces carriage return characters within a record with spaces.
  2. Replace embedded line feeds with spaces - Replaces line feed characters within a record 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, 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.
  7. Fix broken records - Attempts to fix records that break onto one or more lines.

If column trimming is enabled, please be aware that column optimization is based on input data prior to trimming columns.

Other

The Other / Save page allows you to set the application buffer, enable error logging, and save your settings.

  • The application buffer can be set to any number between 1 and 1000. Import speed correlates to buffer size; the higher the buffer size, the faster the import. Conversely, lower buffer sizes will slow the import process. Generally, we recommend using the maximum buffer size (1000).

SQL Importer provides warnings if your buffer size is lower than 1000.

  • Error logging can be enabled from this page. When enabled, errors will be logged to a file stored in the application's data folder.

  • While your settings will also save automatically when you close the Settings form, you can actively save your settings by clicking the Save Settings button.

Importing

Most of your time in SQL Importer will be spent importing files, which is set up from the left pane of the main screen.

Database Name

Start by entering a database name in the Database name input. By default, the input will reflect the name of the last database you used for the current database type. The hyperlink below Database name shows the current database type you're using (e.g., SQL Server®). If you want to change this, click this link to go to the Connection Manager.

One File to Table

One file to table allows you to import one file into a table with a name of your choosing. When you start the import, you'll browse for a single file. A table will be created with the name you specified and data will be imported from the file into the table.

One or More File(s)

One or more file(s) is the easiest and most powerful way to import. When you start the import, you'll browse for and select one or more delimited files. SQL Importer will create tables named from each input filename.

When using the One or more file(s) import option, be sure that your input filenames are also valid table names for your database type.

Delimiter

If you check the Guess my delimiters checkbox, SQL Importer will attempt to guess each file's delimiters automatically. This is especially helpful if you're importing multiple files with potentially different delimiter types. Otherwise, if you prefer and are importing files that are all delimited the same way, you can enter your delimiter in the Delimiter input. If your input columns are qualified with quotation marks, enable the Fields are double quoted option. Typically input columns are wrapped in quotes when a column contains the delimiter (e.g., "Sam Jones, Jr").

Start the Importer

Once you've set your import options, use the Choose Files and Import button to begin importing. You'll browse for your file or files and SQL Importer will begin working. After starting the import, you may be asked to confirm that you want to replace a table or see other prompts depending on your settings.

Registration

To activate a Pro license key, click Register License Key from the Register menu on the main screen. The Register form will open. Please paste your license key into the license key input and click the Save button. Your Pro version copy should be confirmed, along with an expiration date.

A Pro license key removes limits on the number of records each file can contain. It also allows you to select more than two files in One or more file(s) import mode.

You can purchase license keys from https://www.sqlimporter.com/purchase.

You can manage your license keys from https://www.sqlimporter.com/account.