Skip to content

Importing Data (@import)

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

With the @import client-side command, you can import data from a file into a database table using an SQL script. Because it is a script, you can save it, run it again, combine it with other client-side commands such as @export and @mail, and run it from the command-line interface to automate imports. To import data interactively instead, see Importing Table Data.

You can import these file formats:

  • CSV: delimited text files.
  • Excel: XLSX, XLSM and the legacy XLS format.
  • TXT: text files with fixed-width columns.
  • XML and JSON: files in the XML and JSON formats that DbVisualizer exports.

Quick start

This example imports the file fruits.csv into the existing table FRUITS. The columns in the file are in the same order as the columns in the table.

1,Banana,1.22
2,Apple,0.35
3,Orange,0.55

The script to import the file:

@import on;
@import set ImportSource="fruits.csv";
@import parse;
@import target Table="FRUITS";
@import execute;
@import off;

When you run the script in SQL Commander, the Log tab shows the result of each command. The @import execute command reports that 3 records were handled and inserted.

Running the import script in DbVisualizer

How an import works

An import is a sequence of @import commands, which you run in this order:

CommandDescription
@import onStarts an import session.
@import setSets the file to import and how to read it. You can use several @import set commands.
@import parseReads the file and analyzes the data.
@import targetSets the target table and how to map the columns, and checks that the data fits the table.
@import executeInserts the data into the table.
@import offEnds the import session.

The import process, from parsing the source file to executing the import

@import parse reads the file and stores the data in an internal format, together with the row in the file that each record comes from. It also analyzes the data to find the data type and size of each column.

@import target compares this analysis with the target table, for example to check that the values fit the table columns. It can also prepare the table, for example by emptying it or by dropping and creating it.

@import execute runs the INSERT statements. If a record can't be imported, the error message tells you which row in the file it comes from, and the record is saved so that you can import it again later.

If a command fails, the remaining @import commands in the session are not run.

@import on

@import on starts an import session. You normally don't need any parameters.

DbVisualizer stores the parsed data, the analysis results and any records that failed to import in a results folder. By default, a new folder with a unique name is created in the import folder in your DbVisualizer workspace (by default, .dbvis in your home folder). Folders in this default location that are older than two days are deleted when DbVisualizer starts.

ParameterDefaultDescription
ImportResultRoot<WORKSPACE>/importThe folder in which the results folder is created. The folder must exist.
ImportResultDirA generated nameThe results folder. A relative path is relative to ImportResultRoot. If the folder already exists, the import fails unless Clean is true.
CleanfalseIf true, an existing ImportResultDir folder is emptied before the import.
UseImportFileThe errorRecords.drec file of an earlier import, to import the failed records again. See Continuing a failed import.
CommentA comment that is written to the import log.

For example, to store the results in /tmp/myimport and empty the folder if it exists:

@import on ImportResultDir="/tmp/myimport" Clean="true";

@import set

@import set sets the file to import and how to read it. The parameters are written as Name="value", and the names are case-insensitive.

You can use several @import set commands, but the first one must include ImportSource.

Parameters for all formats

ParameterDefaultDescription
ImportSourceRequired. The path to the file to import. A relative path is relative to the script location, or to the folder set with @cd.
FormatFrom the file extensionThe file format: CSV, XLS, XLSX, XLSM, JSON, TXT or XML. You only need it if the file extension doesn't match the format.
EncodingAs set in Tool PropertiesThe character encoding of the file.
HeaderStartRow0The row with the column names. The first row is 1, and 0 means that the file has no header row. If you set this to n and don't set StartRowOfData in the same command, StartRowOfData is set to n + 1.
StartRowOfData1The first row to import. It must be greater than HeaderStartRow.
SkipHeadertrueIf false, the header row is also imported as data.
SkipEmptyRowstrueIf true, empty rows are skipped.
SkipRowsStartingWithRows whose first column starts with this text are skipped, for example // for comment rows.
MaxRows-1The maximum number of records to import. -1 means no limit.
ShowNullAsAs set in Tool PropertiesThe value that represents NULL, for example (null).
DateFormatAs set in Tool PropertiesThe format of date values. See Changing Date and Number Formats.
TimeFormatAs set in Tool PropertiesThe format of time values.
TimeStampFormatAs set in Tool PropertiesThe format of timestamp values.
DecimalSeparator.The decimal separator in numbers.
ThousandSeparator,The thousands separator in numbers.
BooleanTrueFormatstrue, yes, 1, onA comma-separated list of values that mean true.
BooleanFalseFormatsfalse, no, 0, offA comma-separated list of values that mean false.
TxtTrimtrue for TXT, otherwise falseIf true, leading and trailing spaces are removed from the values.
FailOnParseFailurefalseIf true, the import fails if a value can't be parsed.
FailOnConvertFailurefalseIf true, the import fails if a value can't be converted to the column's data type.
FailOnNoColumnsFoundFailuretrueIf true, the import fails if no columns are found in the target table, for example because the table doesn't exist.

For example, to import data from row 5 and skip rows that start with //:

@import set ImportSource="data.csv" StartRowOfData="5" SkipRowsStartingWith="//";

To use the column names in row 1 and import data from row 2:

@import set ImportSource="data.csv" HeaderStartRow="1";

CSV parameters

ParameterDefaultDescription
CsvColumnDelimiterThe column delimiter, for example ;. It can be more than one character. Use tab or \t for a tab. If you set this, CsvColumnDelimiterType is set to String.
CsvColumnDelimiterTypeAuto DetectAuto Detect to detect the most common delimiters automatically, or String to use CsvColumnDelimiter. If the delimiter can't be detected, the parse fails and you need to set CsvColumnDelimiter.
CsvTextQuotedBetweenNoneThe character that encloses values that contain the delimiter, line breaks or other special characters. Use Single for ', Double for ", None for no quote character, or any other character. A file can only use one quote character. If the values in the file are quoted and you don't set this, the quote characters are imported as part of the values.

For example, for a file where the columns are separated by tabs and the values are enclosed in double quotes:

@import set ImportSource="data.csv" CsvColumnDelimiter="tab" CsvTextQuotedBetween="Double";

Excel parameters

ParameterDefaultDescription
ExcelSheetNameThe name of the sheet to import. Only for XLSX and XLSM files.
ExcelSheetId1The number of the sheet to import. In XLSX and XLSM files, the first sheet is 1. In XLS files, the first sheet is 0, so set ExcelSheetId="0" to import the first sheet. You can use ExcelSheetName or ExcelSheetId, but not both.
ExcelCellPolicyErrorEMPTYWhat to do with a formula cell whose value is an error, such as #DIV/0!. The value must be in uppercase:
  • EMPTY: Import the cell as empty.
  • SKIP_ROW: Skip the row.
  • ERROR: Report an error for the row.
  • TO_STRING: Import the error as text, for example #DIV/0!.

TXT parameters

ParameterDefaultDescription
TxtColumnsRequired for TXT files. The character positions of the columns, for example 0,6. See Importing a fixed-width text file.

There are no parameters specific to XML and JSON files.

@import parse

@import parse reads the file and analyzes the data. It has no parameters.

@import parse;

The result shows the number of records and columns found, and the delimiter for a CSV file. For example:

Parsed 5 records. Columns in source: 2 using ',' delimiter

INDEX NAME     TYPE       NULLABLE FROM ROW 
----- -------- ---------- -------- -------- 
0     NAME     String(6)  No       2        
1     BIRTHDAY String(16) No       4        

Total bytes: 73 B

For each column, the result shows:

  • INDEX: The index of the column. The first column is 0.
  • NAME: The column name, if HeaderStartRow is set.
  • TYPE: The data type found, with the size of the longest value.
  • NULLABLE: Whether the column is nullable.
  • FROM ROW: The row in the file that decided the data type.

FROM ROW helps you find out why a column gets an unexpected type. In the example, the BIRTHDAY column is analyzed as a string because of the value in row 4. See Overriding a detected data type.

@import target

@import target sets the table to import into and how to map the columns. It checks that the data fits the table, and it can also empty, drop or create the table before the import.

ParameterDefaultDescription
TableThe table to import into. Write the name as the database stores it, for example in uppercase in H2 and Oracle.
SchemaThe current schemaThe schema of the table.
CatalogThe current catalogThe catalog of the table.
ColumnMappingHow the columns in the file map to the columns in the table. By default, the first column in the file is imported into the first column in the table, and so on. See Mapping columns.
ColumnMappingFileA file with the column mapping. It uses the same syntax as ColumnMapping, but you can put each mapping on its own line and use comments (--, // and /* */). You can't use both ColumnMapping and ColumnMappingFile.
CleanDataHow to clean the table before the import:
  • Clear: Delete all rows in the table. See ClearTableMethod.
  • Drop: Drop the table if it exists, and create it again. You must also set CreateTableSQL or CreateTableSQLFile.
ClearTableMethodTruncate if supportedHow CleanData="Clear" deletes the rows: Truncate or Delete. By default, TRUNCATE is used if the database supports it, otherwise DELETE.
ClearTableSQLThe SQL statement to use for CleanData="Clear", instead of TRUNCATE or DELETE.
CreateTableSQLThe SQL statement that creates the table. Use it with CleanData="Drop", or to create a table that doesn't exist.
CreateTableSQLFileA file with the SQL statement that creates the table. You can use CreateTableSQL or CreateTableSQLFile, but not both.
DropTableSQLThe SQL statement to use to drop the table, instead of the default DROP TABLE statement.
FailOnDropFailurefalseIf true, the import fails if the table can't be dropped.
SkipValidateColumnsA comma-separated list of table column names that are not checked against the data, for example "Column1,Column2".
SkipValidateColumnNumbersA comma-separated list of table column numbers that are not checked against the data, for example "1,5".
SkipValidateJdbcTypesA comma-separated list of JDBC types that are not checked against the data, for example "INTEGER,VARCHAR".
UseDelimitedIdentifierstrueIf true, table and column names in the INSERT statements are enclosed in delimiters, if the database supports it.

For example, to drop and create the table MYTABLE, and import the first column in the file into ID and the third column into NAME:

@import target Table="MYTABLE" ColumnMapping="0=ID,2=NAME" CleanData="Drop"
               CreateTableSQL="CREATE TABLE MYTABLE (id SMALLINT, name VARCHAR(45))";

To delete all rows in the table before the import:

@import target Table="MYTABLE" CleanData="Clear";

Mapping columns

With ColumnMapping, you choose which column in the file goes into which column in the table, and you can leave out columns. The syntax is a comma-separated list of mappings:

ColumnMapping="<file column>=<table column>, <file column>=<table column>, ..."
  • A file column is a column index, where the first column is 0, or a column name from the header row.
  • A table column is a column position, where the first column is 1, or a column name.
  • Column names are case-sensitive. Write table column names as the database stores them, for example in uppercase in H2 and Oracle.

For example, ColumnMapping="0=2, 1=3" or ColumnMapping="id=NO, color=COLOR".

You can also override the data type that @import parse found for a file column, by adding the type in parentheses after the file column, for example ColumnMapping="id(Number)=NO, color=COLOR". The types are String, Date, Time, Timestamp, Number, Decimal Number, Boolean, BLOB and CLOB. See Overriding a detected data type.

@import execute

@import execute inserts the data into the table.

ParameterDefaultDescription
BatchImporttrueIf true, rows are inserted in batches, which is much faster. Some databases and drivers have limited support for batch inserts; if the import fails, try setting this to false.
BatchSize1000The number of rows in each batch.
FailOnInsertFailurefalseIf true, the import fails if an INSERT statement fails.
CommentA comment that is written to the import log.

The inserted rows are committed according to the auto-commit setting in SQL Commander. See Committing and Rolling Back Changes.

For example, to insert the rows one by one:

@import execute BatchImport="false";

@import off

@import off ends the import session. It has no parameters.

Examples

Skipping rows and changing the column order

The file cars.csv uses ! as the column delimiter, and the brand comes before the model:

Volvo!XC90
BMW!F32 4 Series
Volvo!XC60
Mercedes!C197 SLS AMG

The table has the columns in the opposite order, so the columns must be mapped. In this example, the BMW rows are also skipped:

CREATE TABLE carmodel (model VARCHAR(50), brand VARCHAR(50));

@import on;
@import set ImportSource="cars.csv" CsvColumnDelimiter="!" SkipRowsStartingWith="BMW";
@import parse;
@import target Table="CARMODEL" ColumnMapping="0=BRAND,1=MODEL";
@import execute;
@import off;
  • CsvColumnDelimiter="!" sets the column delimiter.
  • SkipRowsStartingWith="BMW" skips the rows where the first column starts with BMW.
  • ColumnMapping="0=BRAND,1=MODEL" imports the first column in the file into the BRAND column, and the second column into the MODEL column.

The table after the import:

model        brand    
------------ -------- 
XC90         Volvo    
XC60         Volvo    
C197 SLS AMG Mercedes

Overriding a detected data type

@import parse decides the data type of each column from the values in the file. If a single value doesn't match the type, the column is analyzed as a string, and you may need to override the type.

In the file birthdays.csv, Lotta's birthday is not a valid date:

NAME,BIRTHDAY
August, "Sat, 21 Jul 1962"
Sven, "Fri, 21 Jan 1972"
Lotta, "NoData"
Bert, "Sat, 21 Jul 1962"

@import parse therefore analyzes the BIRTHDAY column as a string, because of row 4:

INDEX NAME     TYPE       NULLABLE FROM ROW 
----- -------- ---------- -------- -------- 
0     NAME     String(6)  No       2        
1     BIRTHDAY String(16) No       4        

Inserting strings into a DATE column would fail for all rows. To import the valid dates, set the date format and override the type in the column mapping:

CREATE TABLE birthdays (name VARCHAR(40), birthday DATE);

@import on;
@import set ImportSource="birthdays.csv" HeaderStartRow="1" CsvTextQuotedBetween="Double" DateFormat="EEE, d MMM yyyy";
@import parse;
@import target Table="BIRTHDAYS" ColumnMapping="0=NAME,1(Date)=BIRTHDAY";
@import execute;
@import off;
  • DateFormat="EEE, d MMM yyyy" sets the format of the dates in the file.
  • 1(Date)=BIRTHDAY imports the second column into the BIRTHDAY column, as a date.

August, Sven and Bert are imported. Lotta's row fails, and the error tells you which row in the file it comes from:

1 Record affected, Record: 0 originating at row: 4
DataRecordException: Convert error, Column: BIRTHDAY at index: 1
DataTypeConversionException: Value is 'NoData'. Not a valid date format. Valid format: 'EEE, d MMM yyyy'

Importing a fixed-width text file

In the file fruitlist.txt, the first column starts at character position 0 and the second at position 6:

001   APPLE
002    LEMON
003   ORANGE
@import on;
@import set ImportSource="fruitlist.txt" TxtColumns="0,6";
@import parse;
@import target Table="FRUITLIST";
@import execute;
@import off;

The table after the import:

id name   
-- ------ 
1  APPLE  
2  LEMON  
3  ORANGE

LEMON is imported without the leading space, because values in TXT files are trimmed by default (see TxtTrim).

The TxtColumns parameter is a comma-separated list of columns. The first character on a line is at position 0. Each column can be written as:

  • start-end: from start to end, including end.
  • start+n: from start to start + n, including both.
  • start: from start to the character before the next column, or to the end of the line for the last column.

Examples for the line AAA BBB CCC:

TxtColumnsColumns
0-2, 4-5"AAA", "BB"
1-3, 8-9"AA", "CC"
0, 4, 8"AAA", "BBB", "CCC"
1, 8-9"AA BBB", "CC"
0+3, 4+3, 8"AAA", "BBB", "CCC"
0+7, 8+1"AAA BBB", "CC"

Importing an Excel sheet

In the sheet mydata in the file excelData.xlsx, the second cell in row 2 contains a formula that results in an error:

A   B
--  --------
1   A
2   #DIV/0!
3   C

To skip rows with formula errors:

CREATE TABLE exceldata (id INT, value VARCHAR(50));

@import on;
@import set ImportSource="excelData.xlsx" ExcelSheetName="mydata" ExcelCellPolicyError="SKIP_ROW";
@import parse;
@import target Table="EXCELDATA";
@import execute;
@import off;

The table after the import:

id value 
-- ----- 
1  A          
3  C

Testing an import with dry run

To check an import without changing the database, run @import target and @import execute with @set dryrun on. The data is parsed and checked against the table, but the table is not cleaned and no rows are inserted.

Turn on dry run after @import parse, because @import on, @import set and @import parse are not run in dry run mode:

@import on;
@import set ImportSource="fruits.csv";
@import parse;
@set dryrun on;
@import target Table="FRUITS" CleanData="Clear";
@import execute;
@set dryrun off;
@import off;

An import that passes the dry run can still fail on checks in the database, such as primary key or unique constraints.

Continuing a failed import

Records that can't be imported are saved in the file errorRecords.drec in the results folder. After you fix the problem, you can import only those records again. When the import fails, the Log tab shows the commands to do so.

In this example, the name Clementine is too long for the name column:

ID,FRUIT,PRICE
1,Banana,1.22
2,Clementine,0.35
3,Orange,0.55
CREATE TABLE fruits (id SMALLINT, name VARCHAR(6), price DECIMAL(10,2));

@import on ImportResultDir="/tmp/importFirst" Clean="true";
@import set ImportSource="fruits.csv" HeaderStartRow="1";
@import parse;
@import target Table="FRUITS" SkipValidateJdbcTypes="VARCHAR";
@import execute;
@import off;
  • ImportResultDir="/tmp/importFirst" stores the results in a known folder, so that it is easy to find the failed records.
  • SkipValidateJdbcTypes="VARCHAR" turns off the check of VARCHAR columns, so that the import starts and the row fails in the database. Without it, @import target would stop the import before any rows are inserted.

The database rejects row 3 of the file:

1 Record affected, Record: id = 0 originating at row: 3
DataRecordException: Error when importing data. 
MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 1

The table after the first import:

id name   price 
-- ------ ----- 
1  Banana 1.22  
3  Orange 0.55

To import the failed record, make the column wider and run the import again with UseImportFile:

ALTER TABLE fruits MODIFY COLUMN name VARCHAR(20);

@import on ImportResultDir="/tmp/importSecond" UseImportFile="/tmp/importFirst/errorRecords.drec" Clean="true";
@import execute;
@import off;

You don't need @import set, @import parse or @import target, because the settings and the target of the first import are used.

The table after the second import:

id name       price 
-- ---------- ----- 
1  Banana     1.22  
3  Orange     0.55  
2  Clementine 0.35