Data Importer

The Data Importer loads the rows of a file into a database table. It reads CSV and other delimited text files, Excel files (.xls and .xlsx), XML and JSON. It can create a new table from the file's columns, or add the rows to a table that exists. The Data Importer needs DbSchema Pro.

The second step of the Data Importer: each column of suppliers.csv with its first value and the database column and type it goes into

Import a file into a new table

  1. Choose Database → Data Importer, or press Data in the toolbar and choose Data Importer. The dialog is titled Data Loader.
  2. Press Choose and pick the file. Leave Character Set at Detect automatically unless the text comes out wrong.
  3. Choose the Schema.
  4. Choose New Table Name and type the name of the table, such as suppliers.
  5. Press Next, check the columns, and press Load.
The Data Loader dialog with suppliers.csv chosen, the schema shopdb.shop and the new table name suppliers

When the load ends, DbSchema says how many rows it inserted, and how many failed. The new table is in the model and in the database.

To add rows to a table that exists, choose Existing Table in the first step instead. Tick Drop Existing Data to delete the rows that the table holds before the load.

Check the column types

The second step shows each column of the file next to the database column it goes into. A type that does not fit the data makes the rows fail, so check it before you press Load.

  • File Column: the column name from the file, and its first value.
  • Database Column: the target column and its type. Open the list to choose another column, or Skip to leave the file column out. The pencil button edits the column.
  • CSV Format: the separator and the quote character, such as Standard (, and "), TDF (Tab Delimited) or European (Semicolon). Edit sets other characters.
  • First Line is Header: the first line holds the column names, so it is not loaded as data.
  • Locale and Date Format: how the dates in the file are written, such as yyyy-MM-dd or dd/MM/yyyy. The locale matters for month names such as jun.

Press Refresh Columns after you change these settings. After a load, the Error list under the columns shows each kind of error and how many rows it hit. Click an error to see the lines of the file that caused it.

Create the new table in the database

A new table must exist in the database before the rows can go in. When you press Load, DbSchema opens the synchronization window with the new table.

  1. Press Database next to Set every row, so that the table's action is Create.
  2. Press Commit in the database. A window shows the CREATE TABLE statement.
  3. Press Execute All, then Close.
  4. Press Start Loading Data and confirm with OK.
The Synchronize Model and Database window titled Create the imported tables in the database, with the table suppliers present in the model and absent in the database

See Schema Synchronization for the rest of this window.

Next steps