Export and Import

• Introduction
• Export Schema
  • Output Format
  • Output Destination
  • Object Types
  • Options
  • Settings
  • Logging
• Export Table
• Export Grid data
  • Settings page
    • Output Format
    • Encoding
    • Data Format
    • Quote Text Data
    • Options
      • CSV
      • HTML
      • SQL
      • XML
      • XLS
    • Settings
  • Data page
    • Generate Test Data
      • Test data generator example
  • Preview
  • Output Destination
• Export Text data
• Export Graph data
• Export Chart data
• Import Table Data
  • Source File
  • Settings
  • Data Formats
  • Import Destination
  • Import Process
• Exporting and Importing Binary/BLOB and CLOB Data
  • Exporting Binary/BLOB and CLOB Data
  • Importing Binary/BLOB and CLOB Data
• Using Variables and Exporting to Multiple Files

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

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. Similarly, with XML format you can 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.

• Save as Default Settings
  Saves all format settings as default. These are then loaded automatically when DbVisualizer is started
  
• Use Default Settings
  Use this choice to initialize the settings with default values
  
• Load
  Use this choice to open the file choose dialog, in which you can select a settings file
• Save As
  Use this choice to save the settings to a file
• Copy Settings to Clipboard
  Use this choice to copy all settings to the system clipboard. These can then be pasted into the SQL Commander to define the settings for the @export editor commands.

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 Table

When you select a table node in the objects tree, you can open the Export Table 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 table.

 

Figure: The Export Table 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.

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

SQL

Figure: SQL specific export options

XML

Figure: XML specific export options

XLS

Settings

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

• Save as Default Settings
  Saves all settings as default. These are then loaded automatically when DbVisualizer is started
  
• Use Default Settings
  Use this choice to initialize the settings with default values. Some of the settings will be fetched from the general tool properties dialog.
• Load
  Use this choice to open the file chooser dialog, in which you can select a settings file
• Save As
  Use this choice to save the settings to a file
• Copy Settings to Clipboard
  Use this choice to copy all settings to the system clipboard. These can then be pasted into the SQL Commander to define the settings for the @export editor commands.

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.

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

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

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:

• comma ","
• tab "TAB"
• semicolon ";"
• percent "%"

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, 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

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.



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.

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. 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.

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.

---