About

EMS MS SQL Import is a powerful to import your data quickly from MS Excel, MS Access, DBF, TXT, CSV, and XML files to MSSQL tables. MS SQL Import includes a wizard, which allows you to set all the import options for different files visually, and a command-line utility to execute import in one-touch.

Main features:
Import from most popular data formats: MS Excel, MS Access, DBF, TXT and CSV.
Importing into tables, selected from different databases on one host
Easy-to-use wizard interface
Adjustable import parameters, including: source data formats for all the fields and destination data formats for the selected fields, commit options, number of records to skip, etc.
Possibility of saving all the import parameters, used on current wizard session, to the template file for future using with the wizard or with the command-line utility
Command-line utility to import data using the template file

Product Information

Homepage: http://www.mssqlutils.com

Support E-mail: support@mssqlutils.com

Register on-line at: http://www.mssqlutils.com/purchase.phtml


Step 1 - Connect to MSSQL Server

On this wizard step you should enter the necessary settings to establish connection with Microsoft® SQL Server. First select connection type: local or remote. Local connection is being used to connect to Microsoft® SQL server launched on the same machine where you run MS SQL Import Wizard from. Remote mode allows you to connect to Microsoft® SQL Server launched on another computer.

By default the program uses local connection. It is indicated by switch 'Local' selected. To establish remote connection you should select 'Remote' switch. For remote connection you should also enter host to connect to in the 'Connection Host' field. For both types you should select the authentication type - the SQL Server user name and password, or the built-in Windows authentication system. If you select SQL Server authentication system, you must enter the user name and password below.

Please note, that you should have the sufficient privileges to write to the destination database on Microsoft® SQL server.

When you are done, click 'Next' to proceed to the next step.


Step 2 - Managing Import Files

On this step you should add files for import and set options for these files. First select the file type in the upper-left drop-down list: MS Excel, MS Access, DBF, TXT or CSV. Then click button 'Add file' addstyle. The Open File Dialog appears, where you can select file (or several files). The Files of Type drop-down list allows you to choose file type from MS Excel Files, MS Access Files, DBF Files, Text Files, CSV Files, All Supported Types or All Files. All file of type you choose will be displayed in the file list. If you select All Supported Types, you'll see all files of supported types in the current folder. If you select All Files, you'll see all files in the current folder. After you select file (or several files) in the Open File Dialog, the 'Select Table' dialog window appears, where you should select the destination Microsoft® SQL database from the 'Select Database' drop-down list and then select table from the 'Select Table' list. If you select All Files in the Open File Dialog, and select file of undefined type, the Select Import Type dialog appears, where you must select the import type. Check the 'Associate extension *.<Extension> with the selected import type', if you want to associate this file type with the selected import type. If you do this, the files of this type will be also displayed when you select All Supported Types in Files of Type drop-down list, and the import type will be automatically set when you select files of this type.

If you want to change the destination table after the file is added you should click button 'Change Table' below the file list. To remove the file from the list use button 'Remove file' delstyle.

For MS Access files you can use button 'Add Access Special' addaccess. It allows you to select Access tables and queries for import right after adding the file and select for each Access table a separate destination MSSQL table. In the 'Add Access Special' dialog window you should select an Access table from the 'Select Access Table' list, then select Microsoft® SQL database and table just as in the usual 'Select Table' dialog window, then click button 'Add'. In the bottom list the new item appears, which looks like '<ACCESS_TABLE>=<MSSQL_TABLE>. To remove this item, click 'Remove'; to clear all the items, click 'Clear'. When you are done, click 'OK'. For each selected Access table the source Access file is added with the selected Access table chosen as data source (see below) and with the destination MSSQL table, set for the selected table.

If option 'AutoFill Columns' is checked (default), then all the file maps (except TXT) are filled automatically on adding the file (see below).
If option 'Filter Files by Type' is checked, then the file list displays only files of type, selected in the file type drop-down list.

After the file is added, you can set import parameters for this file. Select it in the list and set parameters on the 'Map', 'Formats' and 'Options' tabs. If multiple files are selected, then tab 'Map' and 'Formats' subtab 'Data Formats' are not available.

'Map' Tab

Set the correspondence between the source table columns and the fields of the table they are imported to on the 'Map' tab.

MS Excel

First select the MSSQL table field from the 'Fields' list. Then select the corresponding cells by clicking row or column caption (to select the whole row or column) or clicking the individual cells, using Shift and Ctrl keys.
You can also define the corresponding cells manually by clicking the Add Range [image] button and defining ranges in the appeared dialog window. After you select all the corresponding cells for the current table field, proceed to another field and repeat all these operations for each table field.

If you don't want some first rows or columns of the source table to be imported, set the number of such rows in the 'Skip ... first row(s)' and 'Skip first ... col(s)' edit fields.

Auto fill cols [image] - use this button to set the correspondence between the source table columns and the MSSQL table fields automatically. It is convenient if they are ordered in the same way. First table column will correspond to the first MSSQL table field, second column to the second field, etc. If quantity of the table columns exceeds quantity of the MSSQL table fields, then the last columns will have no correspondence.
Auto fill rows [image] - use this button to set the correspondence between the source table rows and the MSSQL table fields automatically. It is convenient if they are ordered in the same way. First table row will correspond to the first MSSQL table field, second row to the second field, etc. If quantity of the table rows exceeds quantity of the MSSQL table fields, then the last rows will have no correspondence.

MS Access

First select import source on the 'Select Import Source' panel. If you choose import from a table, then you should select a table name from the drop-down list; if you choose to import from a query, you should set the query SQL text in the lower area, e.g.
SELECT * FROM COUNTRY WHERE CONTINENT='South America'. To load query from the *.sql file, click button load, to save query to the file, click save. After you are done editing the query, click compile to receive the list of the query fields.

Now select the MSSQL table field from the 'DataSet' list. Then select the corresponding field in the 'Access' list.
Click button 'Add' to link these fields. These fields will be added to the list at the bottom of the window. Repeat these operations for each MSSQL table field. If you want to remove the accordance you set, select the linked fields in the bottom list and click button 'Remove'.

Auto fill - use this button to set the correspondence between the source table fields and the MSSQL table fields automatically. It is convenient if they are ordered in the same way. First table field will correspond to the first MSSQL table field, second field to the second field, etc. If quantity of the table fields exceeds quantity of the MSSQL table fields, then the last fields will have no correspondence.

DBF

First select the MSSQL table field from the 'DataSet' list. Then select the corresponding field in the '<TABLE_NAME>.DBF' list.
Click button 'Add' to link these fields. These fields will be added to the list at the bottom of the window. Repeat these operations for each MSSQL table field. If you want to remove the accordance you set, select the linked fields in the bottom list and click button 'Remove'.

Auto fill - use this button to set the correspondence between the source table fields and the MSSQL table fields automatically. It is convenient if they are ordered in the same way. First table field will correspond to the first MSSQL table field, second field to the second field, etc. If quantity of the table fields exceeds quantity of the MSSQL table fields, then the last fields will have no correspondence.

TXT

First select the MSSQL table field from the 'Fields' drop-down list. Then set two separator lines to delimit the source table column. Click to add a separator, double-click to delete one. Drag separators to change the column width. You can also set the column starting position and the column width manually in the edit fields 'Pos' and 'Size'.
When you set the separators correctly, proceed to another field and repeat these operations for each MSSQL table field.

If you don't want some first rows of the source table to be imported set the number of such rows in the 'Skip ... first line(s)' edit field.

CSV

If the delimiter you have defined on the first step was found in the source table, then you will find the table columns already separated and delimited. Select the MSSQL table field from the 'Fields' drop-down list. Then click the corresponding source table column or set the 'Col' value manually. Repeat these operations for each MSSQL table field.

If you don't want some first rows of the source table to be imported set the number of such rows in the 'Skip ... first line(s)' edit field.

Auto fill - use this button to set the correspondence between the source table columns and the MSSQL table fields automatically. It is convenient if they are ordered in the same way. First table column will correspond to the first MSSQL table field, second column to the second field, etc. If quantity of the table columns exceeds quantity of the MSSQL table fields, then the last columns will have no correspondence.

XML

First select the table field from the 'Table Fields' list. Then select the corresponding field in the '<File_Name>' list.
Click button 'Add' to link these fields. These fields are added to the list at the bottom of the window. Repeat these operations for each table field. If you want to remove the accordance you set, select the linked fields in the bottom list and click button 'Remove'.

Auto fill
Use this button to set the correspondence between the XML fields and the table fields automatically. It is convenient if they are ordered in the same way. First XML field corresponds to the first table field, second XML field to the second table field, etc. If quantity of the XML fields exceeds quantity of the table fields, then the last fields have no correspondence.

'Formats' Tab

Base Formats

Decimal separator - set a character, which delimits the decimal parts of the imported numbers.
Thousand separator - set a character, which separates the digit groups in the imported numbers.
Short date format, Long date format, Short time format, Long time format - use these edit fields to set the date and time formats.

Boolean true - set some variants of TRUE value representation in the imported table, e.g. 'Yes' or '+'. Use new line for each new variant.
Boolean false - set some variants of FALSE value representation in the imported table, e.g. 'No' or '-'. Use new line for each new variant.
Null Values - set some variants of NULL value representation in the imported table, e.g. 'Null'. Use new line for each new variant.

Data Formats

On this tab you can customize the format of each imported field in case when additional formatting is required. Select the field in the 'Field Name' list and set its format in the proper edit fields.

Generator
Value - use this edit field to set the initial value of the autoincrement field.
Step - set the step of the autoincrement field. If it is 0 then the value of the generator will be ignored.

Constant
Value - use this edit field to set the constant value of the field.

Default
Null - set the value, which will be understood as NULL to set the default value.
Default - set the default value of the NULL field.

Quotation
Left quotation - set a character or a number of characters, which denote quoting in the imported string.
Right quotation - set a character or a number of characters, which denote unquoting in the imported string.
Quotation action - you can select 'Add' to add quotation marks to the imported string, 'Remove' to remove all the quotation marks from the imported string or 'As is' to save the original quotation marks.

String conversion
Char case - set the case of the imported string. 'As is' saves the original string, 'Upper' sets the whole string to upper case, 'Lower' sets the whole string to lower case, 'UpperFirst' sets the first letter of the string to upper case, 'UpperFirstWord' sets the first letter of each word to upper case.
Char set - set the char set of the imported string to ANSI or OEM. 'As is' saves the original string char set.

Replacements
Use this list to set the replacement list for the selected field. To add a replacement, click the Add Replacement button. Then define the text to replace and the value to replace with in the appeared dialog window. Check the Ignore Case option to make replacement case-insensitive.

'Options' Tab

Commit Options

Commit after done - check this option to commit the transaction after import is finished.
Commit after ... records - set a number of records, after importing which the transaction shall be committed.

Import all records - check this option to import all records from the source table.
Import only ... record(s) - if you don't want all the records to be imported, set a number of records to import them from the source file. In this case only this number of records (beginning from the first one) will be imported.

chbox Save Result SQL to File
This option allows you to set the name of the file to save the import SQL script, which is executed on server, to.

Import Mode

Here you can set the import mode. Insert All means that all records from source file would be inserted into the tables independently of existance of this records in destination table. Insert New mode will skip already existing records and insert new records into the tables. Update mode updates all existing records from the source file. Update or Insert mode updates already existing records and inserts others. Delete mode deletes already existing records, and Delete or Insert mode deletes existing records and inserts others. Key columns for this operations defines on the Key Columns tab. Add Type panel defines the records adding type. Insert type means that records inserts into current position, and Append type means that records adds to the end of the table.

Key Columns

This tab allows you to set key columns for records processing mode. Add fields from Available Columns list to Selected Columns list by using buttons with arrows, dragging and dropping or double-clicking.

When you are done managing files and import options, click 'Next' to proceed to the next step of the wizard.


Step 3 - Before and After Scripts

On this step you can define scripts, that will be executed before and after import process for each database.

Select database, which the script is defined for. Type script, that executes before import in the Before Import Script edit box, and type after import script in the After Import Script edit box. Then select another database and type script for this, if necessary. You can set scripts for all databases you want.

You can save and load these scripts by pressing appropriate save save and load load buttons.

When you are done typing scripts, click 'Next' to proceed to the last step.


Step 4 - Start Import

This step informs you, that all the import parameters are set, and you can start import.

chbox Continue import if a script before import was executed with errors
Disable this option to abort the import process, if a script set on the Scripts Step as a before import script, caused errors during its execution.

chbox Close Progress When Done
This option closes the import progress dialog automatically after the import is finished.

This step also displays information about the import process in the Import Log area. You can save this log to file using the Save Log to File button, and clear it with the Clear Log button, if necessary.

If everything is correct, click Import to start the process. If you want to change something, you can return to any of the wizard steps, using button Back.


Tools

This button provides you popup menu, which allows you to Load Template, Save Template, Reopen previously opened template and define some program Preferences.

Save Template
This item allows you to save current import options for future using.

Load Template
This item allows you to load previously saved import template.

Reopen template
This submenu allows you to load previously saved and once opened template. This function makes your template loading much easier.

Preferences
This item opens the Preferences dialog window, which allows you to set common import parameters.


Preferences

This dialog allows you to set various program options and select the program interface language.

Common

Default Import Type
This option allows you to set default import type. It is useful for periodical imports from one format.

Confirmations
Check the 'On Exit' option to enable or disable confirmation on exit.

Show Databases
This option enables/disables 'Select Database' list in 'Select Table' dialog. If you check this option, you can choose the database from list, otherwise you must type database name manually.

Skip 'Scripts' Step
This option enables/disables showing Scripts page.

Define Column Keys Reminder
Enables/Disables remind you to define Key Columns.

Auto Detect Import Type
Enables/Disables the import type autodetection. If you uncheck this option, the Select Import Type dialog will always appear after you select files for import and the destination table, independently from file types. If you check this option, the import types for supported file types or file types you defined will be set automatically, and the Select Import Type Dialog will appear only for undefined file types.

Active Language

This page allows you to set the program interface language. Just load all the *.lng files from the Language Directory folder using the Load Languages button, and select the required language from the list above. To create your own localization file, translate all the strings after the '=' sign in the 'english.lng' file, located in the $(MS SQL Import)\Languages folder. We'll be greatful to anyone, who will send us a new localization of MS SQL Import.

Language List
This list contains all language names and files from the Language Directory folder. Select one to localize MS SQL Import interface to this language. The Default is always present in the list and stands for the default interface language, which is English.

Language Directory
Set the directory which stores the *.lng files for localization. Click the ... button to browse for folder.

Load Languages
Searches the *.lng files in the Language Directory folder and adds them to the Language List.

Click the OK button to apply modified options, click Cancel to return to the wizard without applying any changes, and click Reset to reset all the options to default.


Working with the console application

MS SQL Import command line utility is intended for quick and powerful data import from different files to MSSQL tables.

All the import options are set in the template *.itm file. The program uses this file as an execution parameter. To create your own template file quickly, use MS SQL Import Wizard.

The file parameter is divided into several sections.

The first section is [Connection]. It contains parameters for connecting to the database: Host, Login, and Password. These parameters are obligatory.

The following sections are repeated for each imported file and contain the appropriate prefix FILE_00, FILE_01, FILE_02, etc.
In these sections the import options for the certain file are set.

In the main file section the following parameters are used:
Database (destination Microsoft® SQL database), Table (destination MSSQL table), FileName (import file name) and ImportType, where 0 stands for XLS, 1 for Access, 2 - DBF, 3 - TXT, and 4 - CSV.

The next section is specific to a file type and is named in accordance, e.g. [FILE_00_XLS]. In this section the following specific file type options are set:
[XLS]
SkipRows, SkipCols - number of rows and columns in the source file to skip on import.
[Access]
SourceType - this option indicates the Access source type - Table or SQL query. 0 stands for table, and 1 for SQL.
TableName and SQL - this options set the source Access table name to import from if source type is 0 and the source SQL query text if the source type is 1.
[TXT]
SkipRows - number of rows in the source file to skip on import.
[CSV]
SkipRows - number of rows in the source file to skip on import.
CSVDelimiter, CSVRightQuot, CSVLeftQuot - these options are used on reading the source CSV file, they set the character, delimiting the columns, and the quotation marks.

The next section is [FILE_XX_MAP].
It is also specific to a file type, but is obligatory for all file types. In this section the correspondence between the source file columns or fields and the destination table fields is set.

If the source file is an Excel file, then the map is set in the following format:
<MSSQL_Field_Name>=CellIndex.
Use semicolon to separate multiple cells. You can define Excel row or column as range of cells, e.g.
Field1=A1;A4;A6-A9;B1-F1.

If the source file is an Access or DBF file, then the map is set in the following format:
<MSSQL_FieldName>=<Source_Field_Name>.

If the source file is a text file, the map looks in the following way:
<MSSQL_FieldName>=<Position>;<Size>.
This means that you should provide the starting position and the size of the source file column for each destination field.

To set the map for a CSV file, provide a column number for each destination field, e.g.
Field1=1
Field2=4
etc.

The properties, set in the sections [FILE_XX_BASE_FORMATS] and [FILE_XX_IMPORT_OPTIONS] totally correspond to those, set on the 'Base Formats' and the 'Options' tabs of the Wizard Step 2.

String values, which stand for boolean true and false values are set in sections [FILE_XX_BOOLEAN_TRUE] and [FILE_XX_BOOLEAN_FALSE].

Data formats, which are set for each destination field separately, are written in sections, named in the following way:
[FILE_XX_DATA_FORMATS_<FIELD_NAME>],
e.g. FILE_00_DATA_FORMATS_FIELD1.
The properties under such sections also correspond to the properties, set on the 'Data Formats' tab of the Wizard Step 2.