@import - Importing data

Instead of using the GUI to import data you can use client-side commands to import data, i.e @import. This enables you to use the DbVisualizer command-line interface to automate your imports and utilise other client-side commands such as @export, @mail, among others. Import data using the @import command supports the following formats:

• Excel xlsx or the legacy xls format
• XML The same XML formats that can be exported with DbVisualizer
• JSON The same JSON formats that can be exported with DbVisualizer
• CSV Importing CSV files supports a lot of configurations such as multi-symbol column separator, multi-line values, etc.
• TXT Importing fixed width text files

These are client-side commands for @import are:

The import process is explained by the following figure.

The @import parse step (1) lays the foundation for the database import as it based on the source file format (csv, json, xlsx, etc), parses the data to an intermediary and internal file. This file is then analyzed in order to detect widths, data types and their sizes based on the data, row and cell references back to the source file, and a lot more.

The intermediary format stores the input data as Records associated with information from where the data originates.

Once the data has been parsed some basic tests are performed to see if the properties of the data is compatible with the target table properties. E.g. if the size of the input data will fit the targeted table columns. This done when when the @import target command is run (2).

Final step of an import is to execute (3-4) the actual operations towards the database to import the data. I.e. execute the INSERT statements. Any failures in the import will include specification of where in the source file the invalid data originates.

Following is a complete example where a simple CSV file is imported to a Target table Expenses.

Importing data - Example

The example shows how data about fruits included in a CSV file fruits.csv is imported into a database table fruits.

The example shows a minimal example where the columns of the CSV file is mapped directly to the table columns.

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

The SQL needed to import the file

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

Running the import script in DbVisualizer

As seen in the screen-shot above 3 records were inserted and and 1 was skipped (the header).

Read further on in the guide for how you can tailor your import using the different parameters of the import commands.

The @import commands

@import on

The @import on command initialises the import session. When a client-side command import session is started an output folder is created where DbVisualizer generates data representing intermediate results of the import. The output folder is created under a root folder (importresults) in the DbVisualizer's current workspace. (By default, this is the .dbvis folder in the user's home folder.) The name of the folder is generated to be unique.

These folders are automatically cleaned by DbVisualizer on regular basis.

Note: Normally there is no need to specify any parameters to the @import on command. Default values should be sufficient for most uses.

Example 1

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

Import results are stored in the folder /tmp/myimport. Any existing folder /tmp/myimport will be deleted/cleaned.

Example 2

@import on ImportResultDir="newimport";

Import results are stored in WORKSPACE/importresults/newimport. If this directory exists the import will fail.

@import set

The @import set command takes a parameter name followed by an equal sign and a value, e.g. parameter="value". You can use the following parameters, where ImportSource is the only required parameter. All names are case-insensitive. Note that you may use multiple @import set commands but the first one must include the parameter ImportSource setting the input data to import.

Example 1

@import set StartRowOfData="5" SkipRowsStartingWith="//";

We are starting to import data from row 5 of the source data file and skipping rows starting with "//".

Example 2

@import set HeaderStartRow="1";

The input data has header information at row 1. We are starting to import data from row 2. As StartRowOfData is not explicitly set is automatically set to 2.

@import parse

This command does all the parsing and analysing of input data.

Example

@import parse;

The source data file is parsed. As a result result are stored in the location pinpointed by ImportResultDir.

Example output from the @input parse command:

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

The information shows the number of parsed records along with the number of columns found. If the parsed file was a CSV file the used delimiter is printed.

For each column the following information is printed:

• INDEX: The index of the column
• NAME: If @import set parameter HeaderStartRow was specified and header information was extracted the extracted column name is printed.
• TYPE: The type of data found. The size declaration (E.g. 16) represents the longest string found.
• NULLABLE: If the column is nullable or not.
• FROM_ROW: From which row in the source file the data type (e.g. String) was determined. This number serves as a hint to investigate source data when an unexpected type is analyzed. E.g. The column name BIRTHDAY in the source data indicates that this data should be a date. By investigating the source data at row 4 you may find the reason why the column was analyzed as String column.

@import target

This command is responsible of all preparation of the target table prior to import. This includes dropping, truncating, deleting from and creating the table.

When this is executed a check is done if the input data will actually fit the table. This is done by comparing of the analyze result with the specified target and column mapping. Depending on the parameters the check is performed at different occasions. Parameters for this command are:

Some examples

Example 1

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

Target table is the "MyTable" table. The table is dropped and recreated before import. We are mapping the first column of the input data to the target column "ID" and the third column to the target column "NAME".

As the table is dropped we need to supply the DDL/SQL for creation of the table.

Example 2

@import target Table="MyTable" ColumnMapping="2=NAME" CleanData="Clear"

The table is cleared before the import. The clear method is determined by DbVisualizer. For Databases supporting this Truncate is used.

@import execute

Run the actual import.

Examples

@import execute BatchImport="false";

Run the import. Don't import using batch import

Examples

Selecting data to import and mapping columns

CSV File delimited by exclamation mark "!".

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

Note that the first column of the CSV file is the brand name (Volvo) of the car. The table we are importing to have the columns in opposite order model, brand. I.e. we need to Map the columns.

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;

Parameters used

CsvColumnDelimiter="!" specifying that the data is delimited by the character '!'.

SkipRowsStartingWith="BMW" We are not importing BMW cars

ColumnMapping="0=brand,1=model" Column 0 of the CSV file is mapped to the brand column of the table. Column 1 is mapped to the model column.

The table carmodel content after import:

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

Overriding analysed type information

When an input file is parsed DbVisualizer analyses the data to determine data types of the input data. The algorithm for this is quite coarse. DbVisualizer does offer a way to override the analysed data type.

CSV data

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

Note that the birthday of "Lotta" is "NoData" which is of course not a valid date. When DbVisualizer parses/analyses the data, it will come to the conclusion that the BIRTHDAY column is a String.

The result of @import parse will contain a table describing information about the data that was parsed.

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

As mentioned earlier you can see that column BIRTHDAY has been interpreted as a String. This was found examining row 4 (FROM ROW column is 4).

In connection with inserting this column in the database DbVisualizer would insert/set this as a string. This would result in total import failure and no rows would be inserted in the database.

This may be addressed by overriding the analysed type for BIRTHDAY (String) and set the type to date.

The SQL

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

@import on;
@import set ImportSource="birthdays.csv" CsvTextQuotedBetween="Double" DateFormat="EEE, d MMM yyyy" HeaderStartRow="1"; 
@import parse;
@import target Table="birthdays" ColumnMapping="0=name,1(date)=birthday";
@import execute;
@import off;

Parameters

• DateFormat="EEE, d MMM yyyy" Defining the format to be able to interpret the dates in the CSV file.
• ColumnMapping="0=name,1(date)=birthday" Note the 1(date)=birthday where we are mapping the column with index 1 to the target column birthday. The (date) part specifies that column 1 should be interpreted as a date.

The result of the import using the script above is that 3 rows are imported (August, Sven and Bert). The row representing Lotta is reported as a failure as Indicated below.

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 fixed column width input data (TxtColumns parameter)

Text File

001   APPLE
002     LEMON
003   ORANGE

The SQL Script

@import on;
@import set ImportSource="fruitlist.txt" TxtColumns="0,6"; 
@import parse;
@import target Table="fruitslist" Catalog="test";
@import execute;

The parameter TxtColumns parameter specifies the column character positions. In this case first column starts at character position 0 and the second column starts at character position 6.

The resulting imported table is

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

Note how "LEMON" is imported without proceeding blanks. This is because column values are trimmed (TxtTrim parameter default is true).

The TxtColumns parameter

The TxtColumns parameter supports a number of syntaxes as explained in the examples below.

An example when parsing a row "AAA BBB CCC"

Omitting the end index (as in the SQL script above)

Using the "+" sign

Importing Excel data

Excel file

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

Note how row 2 column B has the value #DIV/0!. This value represents a case where a cell is a calculated using formula where the calculation is producing an error.

SQL Script to import

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;

Parameters used

ExcelSheetName="mydata" Specifying that the sheet named mydata is is the sheet to import.

ExcelCellPolicyError="SKIP_ROW"; Specifies that if we find a formula that produced an error we should skip the complete row.

Resulting Table

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

Note that row 2 is not imported as we instructed by ExcelCellPolicyError="SKIP_ROW";

Continuing an export that has failed

If any of the input data cannot be imported DbVisualizer will keep track of this. This is done by storing the failed data in a specific errorRecords.drec file in the directory where the import process stores its intermediate results (See ImportResultRoot parameter).

Following is an example were the export fails. It also shows how to import the failed data again. Specifically, the first import fails as Clementine is a name that is too long to fit in the target table column.

ID,FRUIT,PRICE
1,Banana,1.22
2,Clementine,0.35
3,Orange,0.55

First Import SQL Script

CREATE TABLE fruits (id SMALLINT, name VARCHAR(6), price DECIMAL(10,2)); 

@import on  Clean="true" ImportResultDir="/tmp/importContinueFirstImport";
@import set ImportSource="fruits.csv" HeaderStartRow="1";
@import parse;
@import target Table="fruits" SkipValidateJdbcTypes="VARCHAR";
@import execute;
@import off;

Note: the table definition for the fruit table defines the column name to be VARCHAR(6). The input data "Clementine" will not fit there.

Parameters used

ImportResultDir="/tmp/importContinueFirstImport" We get our results in this directory. Makes it easy to refer in the second import script.

SkipValidateJdbcTypes="VARCHAR" Tells DbVisualizer not to validate VARCHAR columns. This is done for the purpose of this example. If not specified, the import would stop before any data has been imported.

When running this import the Database used in the example (MySQL) will fail when the import tries to insert the data row 3 as Clementine will not fit the column. The Failure printed in the DbVisualizer Log for this looks something like:

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

Note that the source data is pinpointed as originating at row: 3.

The Table fruits content after import.

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

Second import SQL Script

ALTER TABLE fruits MODIFY COLUMN name VARCHAR(20);

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

The ALTER statement is dealing with the root cause why the import failed. The name column was too small.

Note that when continuing an import, the commands @import set and @import target is not specified. The settings and target from the old import is used.

Parameters used

ImportResultDir="/tmp/importContinueSecond": Specifying a separate directory for the results

UseImportFile="/tmp/importContinueFirstImport/Results/errorRecords.drec" Pinpointing the file containing the data that contains the data that could not be imported.

The Table fruits content after second import.

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

Testing an import - Dry Run

The client-side import offers a way to run the import script to perform all client side data validation without changing anything in the database. This is done using the @set dryrun command.

Note that when running the import, without dry run, the import may fail nevertheless due to checks on the database side. E.g. primary- or unique key constraint checks.

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

When running the script, no actual clearing of the table will be done as the parameter CleanData indicates. Nor does the @import execute lead to any rows being inserted in the database.

Since there is a @set dryrun command prior to the commands no changes to the database table will be performed.