Export and Import

DbVis Software
DbVisualizer 8.0 http://www.dbvis.com
support@dbvis.com


Master documentation index

Introduction

You can export both schema objects and data from DbVisualizer to a file. With the Export Schema feature, you can export the DDL and/or data for all or selected objects in a database schema. The Export Table feature offers the same options as Export Schema but for a single table.

The Export Data feature writes different types of data presented in DbVisualizer, such as text and graphs, to a file. The Export Data Wizard dialog looks different depending on whether grid, graph or chart data is being exported.

The following sections describe the options available for each of these cases. There are major differences between DbVisualizer Free and DbVisualizer Personal when exporting objects and data. This document explains the complete functionality in the Personal edition, some of which is not available in DbVisualizer Free.

Exporting very large result sets using the standard Export Data feature may fail due to running out of memory, since all data must first be presented in DbVisualizer. The @export client side command in the SQL Commander solves this problem, since it exports the data on the fly while it is fetched from the database.

The Import Table Data feature reads data stored in CSV (Character Separated Values) format from files.

Export Schema

Sometimes you may need to copy a schema from one database to another, or compare two similar schema to see how they differ. The Export Schema feature can help you with tasks like these. This feature writes the DDL and/or the table data for all or selected database objects in a schema to a file or another destination.


Figure: The Export Schema dialog

You launch the Export Schema dialog by selecting the schema you want to export in the object tree and choosing Export Schema either from the right-click menu or from the Actions menu.

The following sections describe the different options you can use. When you are happy with all the settings, click Export to start the process. Log messages are displayed during the export process. 

Output Format

You can export the schema objects in a number of formats. With the SQL or XML formats you can export the DDL for all supported object types as well as the data for tables, while the other formats only apply to table data.

If you choose SQL, the objects are exported as DDL statements (CREATE TABLE, CREATE VIEW, etc.) and, if you choose to include table data, as INSERT statements. This is the format to use if you want to recreate the schema somewhere else.

If you want to compare one schema to another, you may want to pick the XML format instead. The object declarations are then exported as XML documents, like this example:

<?xml version="1.0" encoding="MacRoman"?>

<TABLE>
<SCHEMA>
HR
</SCHEMA>
<NAME>
JOBS
</NAME>
<COLUMNS>
<COLUMN>
<NAME>
JOB_ID
</NAME>
<DATA_TYPE>
VARCHAR(10)
</DATA_TYPE>
</COLUMN>
<COLUMN>
<NAME>
JOB_TITLE
</NAME>
<DATA_TYPE>
VARCHAR(35)
</DATA_TYPE>
</COLUMN>
<COLUMN>
<NAME>
MIN_SALARY
</NAME>
<DATA_TYPE>
INTEGER
</DATA_TYPE>
<NULLABLE/>
</COLUMN>
<COLUMN>
<NAME>
MAX_SALARY
</NAME>
<DATA_TYPE>
INTEGER
</DATA_TYPE>
<NULLABLE/>
</COLUMN>
</COLUMNS>
<CONSTRAINTS>
<CONSTRAINT>
<NAME>
JOB_ID_PK
</NAME>
<TYPE>
PRIMARY KEY
</TYPE>
<COLUMNS>
<COLUMN>
<NAME>
JOB_ID
</NAME>
</COLUMN>
</COLUMNS>
</CONSTRAINT>
<CONSTRAINT>
<NAME>
JOB_TITLE_NN
</NAME>
<TYPE>
CHECK
</TYPE>
<EXPRESSION>
"JOB_TITLE" IS NOT NULL
</EXPRESSION>
</CONSTRAINT>
</CONSTRAINTS>
</TABLE>
The encoding choice specifies which character encoding to use for the data when you export to a file, and it is also used as the encoding in XML header when you use the XML format. The default choice is based on your systems default encoding.

The table data is exported in the same format as described for Export Grid Data below.

Output Destination

Destination Description
File This option outputs the data to the named file.
SQL Commander This option writes the export data to an SQL Commander editor. It is primarily useful when exporting with the SQL output format.
Clipboard
Exporting to the (system) clipboard is convenient if you want to use the exported data in another application without the extra step of exporting to file first.

Object Types

In the Object Types area, you select the object types or individual objects you want to export. Checking the check box for a type, e.g. Tables, selects all objects of that type. Expand the type node to select individual objects instead, e.g. just a few tables.

Options

The Options area contains different options depending on the selected Output Format. Most options are the same as for Export Grid Data, but for the SQL format you can also choose to Generate CREATE and DROP Statements and to Include Table Data and Table Indexes for the exported tables. You can also select which SQL Block delimiters to use when generating CREATE statements for objects like procedures and functions. For instance, if you intend to run the generated SQL script with the MySQL command line interface, you can specify "DELIMITER //" as the begin delimiter and "DELIMITER ;" as the end delimiter. For an Oracle SQL*Plus script, you may instead use "/" as the end delimiter and no begin delimiter. For the XML format, you can also choose to include the DDL, table indexes and table data.

If you choose to include table data, you can also change how the values for different data types are formatted in the output by clicking the Data Format Settings button.


Figure: The Data Format Settings dialog

Settings

Clicking the Settings button reveals a a menu with options for saving and loading settings to and from a file.

Logging

By default, log messages about the export process are shown in the Log tab. If you instead want to write the messages to a file, open the Log tab and specify the file before clicking Export.

Export an Individual Object

When you select a node in the objects tree, such as a table, view or procedure, you can open the corresponding Export Object dialog from the right-click menu or the Actions menu. It has exactly the same options as Export Schema, except that it only exports the selected object.


Figure: The Export Table dialog


Figure: The Export Procedure dialog

Export Grid data

The Export wizard is launched using the Export button in the grid toolbar () or from the grid's right-click menu. If you want to export just some of the grid rows and columns instead of all data in the grid, select the data to export and launch the wizard with the Export Selection right-click menu choice. 

Settings page

The first wizard page is the Settings page, containing general properties for how the exported data should be formatted. All settings in the settings page can be saved to a file for later use in the export wizard or in the SQL Commander when exporting result sets using the @export editor command.


Figure: The grid export wizard

Read the sections below for detailed information about each field and the settings you can use.

Output Format

Grid data can be exported in the following formats.

Format Description
CSV The CSV format (Character Separated Values) is used to export the grid of data to a file in which each column is separated with one or more characters. You may also specify the row delimiter (aka newline sequence of characters).
5,Hepp,59248
15,Hopp,41993
16,Hupp,44115
The above example use a "," as the column delimiter and a "\n" sequence as the row delimiter (invisible above).
HTML The data is exported in HTML format using the <TABLE> and associated tags.
TXT TXT formats all output data in fixed width columns based on the size of the widest data in each column.
Column1 Column2        Column3
------- -------------- -------
5 Hepp Happ Hipp 59248
15 Hopp 41993
16 Hupp 44115
SQL The SQL format simply creates an SQL INSERT statement for each row in the grid. It also uses the column names from the grid to define the column list in the SQL statement.
insert into table1 (Column1, Column2, Column3) values (5, 'Hepp', 59248);
insert into table1 (Column1, Column2, Column3) values (15, 'Hopp', 41993);
insert into table1 (Column1, Column2, Column3) values (16, 'Hupp', 44115);
XML The XML format is handy when importing or using the exported data in an XML enabled application. The default structure of the XML format is:
<ROWSET>
<ROW>
<Column1>5</Column1>
<Column2>Hepp</Column2>
<Column3>59248</Column3>
</ROW>
<ROW>
<Column1>15</Column1>
<Column2>Hopp</Column2>
<Column3>41993</Column3>
</ROW>
<ROW>
<Column1>15</Column1>
<Column2>Hupp</Column2>
<Column3>44115</Column3>
</ROW>
</ROWSET>
Alternatively, you can choose between the commonly used XmlDataSet and FlatXmlDataSet formats.
XLS
Use the XLS format if you want to work with the exported data in Microsoft Excel or a compatible spreadsheet application, such as Open Office.

Encoding

The encoding choice specifies which character encoding to use for the data. It is also used to set the encoding in the HTML and XML headers. The default choice is based on your systems default encoding.

Data Format

The data format settings define how the data for each of the data types will be formatted.

Quote Text Data

Defines whether text data should appear between quotes. Use the Duplicate Embedded option to properly deal with text that contains the quote character when you export as SQL or CSV.

Options

The options section is used to define settings that are specific for the selected output format.
CSV

Figure: CSV specific export options

HTML


Figure: HTML specific export options

TXT

Figure: TXT specific export options

SQL


Figure: SQL specific export options

XML

Figure: XML specific export options

XLS

Figure: Excel specific export options

Settings

Clicking the Settings button reveals a a menu with options for saving and loading settings to and from a file:

Data page

Clicking the Next button in the wizards moves you to the Data page. Use the columns list to control which columns to export and how to format the data for each columns. The list is exactly the same as the column headers in the original grid, i.e., if a column was manually removed from the grid before launching the Export wizard, then it will not appear in this list.


Figure: The grid export wizard

The Table Rows fields show you how many rows are available and let you specify the number of rows to export. This setting along with the Add Row button is especially useful when you use the test data generation feature described in the next section.

Here follows information about the columns in the list.
Field Description
Export Defines whether the column will be exported or not. Uncheck it to ignore the column in the exported output.
Name The name of the column. This is used if exporting in HTML, XML, XLS or SQL format. Column headers are optional in the CSV output format.
Type The internal DbVisualizer type for the column. This type is used to determine if the column is a text column (i.e., if the data should be enclosed by quotes or not).
Text Specifies if the column is considered to be a text column (this is determined based on the type) and so if the value should be enclosed in quotes.
Value The default $$value$$ variable is simply be substituted with the column value in the exported output. You can enter additional static text in the value field. This is also the place where any test data generators are defined.

Generate Test Data

The test data generator is useful when you need to add random column data to the exported output.

The Value field specifies the data to be in the exported output. By default, it contains the ${value}$ variable, which is simply replaced with the real column value during the export process. You can also add static values before and after the ${value}$ field, to be exported as entered.

Alternatively, you can use test data generator variables in the Value field. The choices are available in the right-click menu when you edit the Value field.


Figure: Right-click menu with the test data generator functions

Function Name Function Call Example
Generate random number ${var||randomnumber(1, 2147483647)}$ Generates a random number between 1 and 2147483647
Generate random string of random size ${var||randomtext(1, 10)}$ Generates random text with a length between 1 an 10 characters
Generate random value from a list of values ${var||randomenum(v1, v2, v3, v4, v5)}$ Picks one of the listed values in random order
Generate sequential number ${var||number(1, 2147483647, 1)}$ Generates a sequential number starting from 1. The generator re-starts at 1 when 2147483647 is reached. The number is increased with 1 every time a new value is generated.
Test data generator example
Here is an example of how to use the test data generators to try out planned changes to the data. Consider this initial data:


Figure: Sample of grid data

After the changes, the JOB column should not appear in the output and the new JOB_FUNCTION should contain abbreviated job function codes. To test this, we simply uncheck the Export field for JOB entry and set the Value for the JOB_FUNCTION to use the Generate random value from a list of values function.


Figure: Customized columns list with a generator function

Previewing the data (or exporting it) in CSV format results in this:


Figure: Result of generated test data

Preview

The third wizard page is the Preview page, showing the first 100 rows of the data as it will appear when it is finally exported. This is useful to verify the data before performing the export process. If the previewed data is not what you expected, just use the back button to modify the settings.

Output Destination

The final wizard page is the Output Destination page. The destination field specifies the target destination for the exported data.


Figure: The output destination and final page for grid export

Destination Description
File This option outputs the data to a named file.
SQL Commander This option writes the export data to an SQL Commander editor. It is primarily useful when exporting with the SQL output format.
Clipboard Exporting to the (system) clipboard is convenient if you want to use the exported data in another application without the extra step of exporting to file first. CSV formatted data can even be pasted into a spreadsheet application such as Excel or StarOffice, and the cells in the grid will appear as cells in the spreadsheet. Read more about the CSV format in the Format section.

Export Text data

The wizard for exporting result sets in Text format is very simple, as it is only possible to specify a file for the exported output. The dialog looks slightly different on different platforms.


Figure: Export window for text format result sets

Export Graph data

When you export a References or Navigator graph, it is exported with the same zoom level as it appears on the screen. The Export wizard pages when exporting a graph looks like this:



Figure: Export window for graphs

The graph can be exported to a File in the JPEG, GIF, PNG, SVG or PDF formats.

Export Chart data

The options when exporting charts are similar to those for graphs, but in addition you can set the size and orientation to use for the chart in the file.


Figure: Export window for charts

A chart can be exported to a File in the JPEG and PNG formats. The optional Layout settings are used to control orientation and size of the image. The default width and height are the same as the size of the chart as it appear on the screen. The Size list when clicked shows a list of well known paper formats. The Width and Height are changed to match the selected size. Whether setting the width and height manually or selecting a predefined size, the exported image is scaled accordingly.

Import Table Data

The Import Table Data feature is used to import files containing data organized as rows of columns with separator characters between them, such as CSV files.

Note:
The first row in the source file can be used to name the columns.

The destination for the imported data can be a database table or a grid in DbVisualizer. The grid option is convenient for smaller files, as the features available for a DbVisualizer grid can then be used to do various things with the data. An example is that a CSV file can easily be converted into an XML file or a HTML document by importing the data to a grid and then use the Export Wizard in the grid to output the grid data in the desired format.

The Import Wizard can be used in two ways. To import data into an existing table, select the table node in the objects tree and launch the wizard via the right-click menu or via the Actions menu.


Figure: Import Table Data action in the right-click menu for a table object

If you instead want to create a new table for the imported data, select the Tables node in the objects tree and then launch the wizard.


Figure: Import Table Data action in the right-click menu for the Tables object

Source File

In the first wizard page, select the source file to import and then click the Next button.


Figure: The Source File import wizard page

Settings

In the Settings page, you specify how the data in the file is organized. The Data section at the bottom of the page shows a preview of the parsed data in the Grid tab and the original source file in the File tab. If a row in the Grid tab is red, it indicates that the row will be ignored during the import process. This happens if setting any of the Options settings results in rows not being qualified.

In the Delimiters section, define the character that separates the columns in the file. If you enable Auto Detect, DbVisualizer tries the following characters:
You may use up to 4 characters in sequence as the column delimiter.
Use the Options section to further define how the data should be read. 


Figure: The Settings wizard page

The following shows the preview grid with some rows in red. The reason is that the Skip First Row(s) and Skip Rows Starting With are set, i.e., the first two rows and the rows starting with 103 will not be imported.


Figure: The Settings wizard page

Data Formats

The Data Formats page is used to define formats for some data types. The first row in the preview grid contains a data type drop-down lists. DbVisualizer tries to determine the data type for each column by looking at the value for the number of rows specified as Preview Rows. If this data type is incorrect for a column, use the drop-down lists to select the appropriate type.


Figure: The Data Formats wizard page

The following is displayed when selecting the drop-down box in the preview grid.


Figure: The data type drop down

If you need to change the data type for a number of columns, e.g. set them all to String, you can Copy/Paste the data type. First change it for one of the columns using the drop-down, select and copy that new data type value and then select the data type for all other columns and use Paste to change them all at once. If you make a mistake, you can change the Preview Rows value to let DbVisualizer determine the types again.

If you import to an existing table, there is yet another way to adjust the data types for the file columns, described in the next section.

Import Destination

The Import Destination page provides two options: Grid and Database Table. The Grid choice is used to import the data into a grid that will be presented in its own window in DbVisualizer.

When the Database Table choice is selected, the page shows information about the table in which the data will be imported.

If you are importing into an existing table, the Map Table Columns with File Columns grid shows the columns in the selected database table and the columns in the source file.

DbVisualizer automatically associates the columns in the source file with the columns in the target table in the order they appear. If the columns appear in a different order in the file than in the table, but they are named the same, you can use the auto-mapping menu in the upper right corner of the Map Table Columns with File Columns grid to automatically map the columns by name. Map by Column Name and Map by Column Index do exactly what it sounds like. Map File Data Type = Table Data Type sets the File Data Type for each column to the type of the corresponding table column.



Figure: The auto-mapping menu for import into an existing table


If the column names are different between the file and the table and also appear in different order, you can manually map them using the drop-down lists in the File Column Name field. Choose the empty choice in the columns drop-down to ignore the column during import.
You can use copy/paste of the values in the File Column Name and File Data Type fields to quickly fill the selection of cells instead of manually selecting the correct data in the drop-downs.

Figure: The column mapping drop down

When you import into a new table, you are presented with a field for the table name and a number of tabs for column and constraint declarations. The Columns tab is filled out based on the source data and the data types from the Data Formats page.



Figure: The table declaration form for importing into a new table

Note that it is not always possible to find a database specific type for the data format specified on the Data Format page. You must then pick the correct type from the Data Type drop-down menu. The size for string column types may also need to be adjusted. By default, the size is set to the maximum number of characters found for the column in the number of rows specified as Preview Rows, adjusted up to the next power of then. You can ignore certain columns by removing them in the Columns tab. Keys and other constraints can be created using the other tabs.

You can go back to the Data Format page and increase the Preview Rows value if you believe that it will help DbVisualizer to pick better defaults. If you do so, you need to click the Reload button when you come back to this page to rescan the source data and get new default values.

If you make a mistake, or if the import fails, so you have to go back and make adjustments before you import again, make sure you enable Drop existing table, if any. It is disabled by default to prevent you from accidentally dropping an existing table when you intend to import to a new table, but if the import fails, the new table may already have been created so it needs to be dropped before a new table with your adjusted input can be created.

Import Process

The last wizard page is used to start and monitor the import process. Here you can select whether all rows in the source file should be imported or only a portion. You can also specify that you want to log to the GUI or to a file, and that you want keep the window open when the import is completed, so that you can see the log messages when logging to the GUI. If you want to stop the processing on the first error, check the Stop on Error check box.

If any errors occur during the import process, error messages are presented in the log and the window stays open regardless of the Keep Window after Import setting.


Figure: The import process page

Exporting and Importing Binary/BLOB and CLOB Data

Columns declared as Binary/BLOB and CLOB can be exported and imported using DbVisualizer as SQL or CSV files. The data for each such cell is stored in a separate file, referenced from the SQL or CSV file as a DbVisualizer variable. Here's an example of an SQL INSERT statement with a Binary/BLOB variable:
insert into "BLOB_TEST" ("COL1") values (${data1-0||||BinaryData||noshow vl=file}$);

Exporting Binary/BLOB and CLOB Data

All of the export dialogs described earlier in this section (Export Schema, Export Table, and Export Grid) can be used to export Binary/BLOB and CLOB data. You enable this by choosing File as the data format for Binary/BLOB and/or CLOB data. Optionally, you can specify the directory for the data files. If you do not specify a directory, the operating system's default directory for temporary files (e.g. C:\TEMP or /tmp) is used.



Figure: Data format File for export of Binary/BLOB and CLOB data

Importing Binary/BLOB and CLOB Data

If you have exported Binary/BLOB and CLOB data as an SQL script, you just run the script in the SQL Commander to import it. When the SQL Commander encounters a variable that refers to a file, it reads the file and inserts the content as the column value.

If you exported to a CSV file, use the Import Table Data feature to import it. On the Data Format page, ensure that the format for the source file column is set to BLOB or CLOB.



Figure: Data format BLOB for import of Binary/BLOB data

Using Variables and Exporting to Multiple Files

You can use some of the pre-defined DbVisualizer variables (${dbvis-date}$, ${dbvis-time}$, ${dbvis-timestamp}$ and ${dbvis-object}$) in all fields that holds free text (e.g. title and description fields) and as part of the file name in all export dialogs.

Use the ${dbvis-object}$ variable as part of the file name in Export Schema if you want to export the DDL and/or data to a separate file for each object. The variable is replaced with the object type and object name, e.g. ${dbvis_object}$.sql becomes table_COUNTRIES.sql for a table named COUNTRIES.


Copyright © 2011 DbVis Software AB. All rights reserved.