Importing Database Data

www.altova.com Print this Topic Previous Page Up One Level Next page

Home > 

Importing Database Data

With DatabaseSpy, you can import data from XML or CSV files to your databases. When you click the Import ic_import button in the Tools toolbar or select the menu option Tools | Import data to the database... (Ctrl+I), the "Import data to the database" dialog box opens where you can select the XML or CSV file to be imported and define the import options. The "Import data to the database" dialog box contains two tabs, Selection and Options.

dlg_import-selection

The lower part of the dialog box always shows a preview of the selected data, which can be updated to reflect the selected options.

imp-csv2

You can choose to preview the data (screenshot above) or the definition (screenshot below) of the selected element's table structure.

imp-csv3

All fields will be created as Text fields with the length specified in the Options tab. You can change the data type separately for every column in the Preview group box if you select the Show Definition radio button (see screenshot above).

 

 

To import database data from a text file:

1.Make sure that a data source is active in the project folder.
2.Select the menu option Tools | Import data to the database, or press Ctrl+I, or click the Import ic_import button in the Tools toolbar. The "Import data to the database" dialog opens.
3.In the Source group box, enter the path to the text file you want to import. You can type it in, select it from the drop-down list, or click the Browse ic_browse button.
4.In the Destination group box, select where you want to import the data to. If you previously selected a data source, the three fields are automatically filled in, if not:

 

a)Select the data source, database, and schema from the drop-down lists.
b)Select Create new table(s), Update/Create if not existing, or Update existing tables, depending on where you want to import the data.
c)In the Stop after errors drop-down list, specify the number of errors after which the import process should be stopped.
d)In the Stop action drop-down list, select the action to perform when import is stopped: Commit commits the tables that did not contain errors; rollback does not commit any imported data.

 

5.In the Import Mode group box, do one or more of the following:

 

Activate the Execute radio button if the necessary SQL should be generated and executed in one step.
Activate the Generate SQL in SQL Editor radio button if the generated SQL file should be placed in the SQL Editor ready for execution.
Check the Log check box to activate the drop-down list where you can enter the name and path of the SQL log, select a previously used log file, or select an existing log file by clicking on the Browse ic_browse button.

 

6.Click the Apply Options button to preview the data and further define the import settings.
7.Click the Show Data or Show Definition radio button to edit the different aspects of the table structure.
8.Double-click a field in the preview window to edit the data type, nullability, or indexing. Define how/whether you want to import each column by clicking the column header to change it.

 

ic_include-col Import

ic_unique-key Import as unique key

ic_primary-key Import as primary key

ic_exclude-col Do not import

 

9.Click Import. If the Execute radio button is selected in the Import Mode group box, the text file is imported into the database and a prompt appears asking if you want to view the log.sql file. You may have to execute the generated SQL script first to import the text file into the database.

© 2019 Altova GmbH