Import Data

From DBArtisan
Jump to: navigation, search

Go Up to Tools

It is often necessary to import data into database tables from external sources. You may need to bring sample data into a database to establish test case scenarios or import data for use in the real world. Business analysts, for example, often use spreadsheets from outside sources. Putting such data into tables can help them perform detailed queries and make informed decisions based on meaningful data.

The Import Data tool lets you import data from a Microsoft Excel (.xls) file or any SQL data file format in which data values are stored in plain-text form and delimited by tab, semicolon, comma, space, or other user-specified character (such as .sql, .tab, or .csv).

Note: When you import a text file, the wizard may give the impression of stalling. Press ESC and you should be able to continue without further incident.

To Open the Import Data Wizard:

  1. Connect to the datasource to which you want to import data and ensure that the datasource is selected. For more information, see Connecting to Datasources.
  2. On the Tools menu, select Import Data. The Import Data Wizard opens.
  3. Use the following table as a guide to providing information as you work through the panels of this wizard:
Panel Setting Description

Data Properties

Specify the file to be used in this data load operation

Type the path and name of the file that you want to imprt from or use the browse button to locate and select the file.

Which table do you want to load data into?

If working against a Sybase or SQL Server datasource, select the Catalog of tables. Select the Schema that owns the target table. Select the particular Table where you want to import the data.


What character delimited the columns in the data file?

Choose among Tab, Semicolon, Comma, or Space. Alternatively, select Other and type the specific delimiting character.

First Row Contains Field Names

By choosing this, if the first row contains column names, these will be displayed as the first row of the file. You can still change column mappings if you want. If you do not select this, generic Field 1, Field 2,... Field n will indicate column mappings.

Column Mapping

Note: You must map all mandatory (NOT NULL) column fields to a column heading before you can enable the Finish button and import the data. To map columns, click the grey bar at the top of the grid and a drop-down list appears. The names in this drop-down list are columns in the table that’s going to receive the data. These need to sync up to the data that’s being imported. Red, or mandatory columns, turn blue in the column list when they have been assigned. A maximum of 10 rows are displayed here to make mapping columns easier. All rows will be imported when the job completes. If there are more columns in the file you are importing than in the receiving table, the extra columns will be ignored and you can create the insert statements. You cannot complete the import operation if columns are mapped more than once. The import operation will not let you generate invalid columns.

Excel Column Mapping (.xls files only)


If the file you selected has more than one sheet, identify the worksheet that has the data you want to import.

Start/End Cell

Identify the starting and ending cells of data you want to import and click Refresh. Or, accept the default cells.

4. Click Finish.