[ 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, while the Export
Data feature writes different types of data presented in DbVisualizer
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 grid 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 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 feature reads data stored in CSV (Character Separated
Values) format from files.
The Printing feature prints grid and graph data to a printer or a file.
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. The Export Schema
dialog is closed if the export is successful, otherwise it stays open
so you can find out what went wrong by reading the log messages.
Output Format
You can export the schema objects as SQL or XML.
If you choose SQL, the objects will be 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.
Output Destination
| Destination |
Description |
| File |
This option outputs the data to the named file.
|
| SQL Commander |
This destination will transfers the export data to the SQL
Commander editor. It is primarily useful when exporting 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
In the Options area, you can choose to Generate DROP Statements (this
option only applies to the SQL format), to Include Table Data and Table
Indexes for the exported tables. 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 @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
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 on each field and what
settings
that can be made.
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 a character or several. It is even
possible to 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.
|
| 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.
|
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 defines how the data for each of the data
types will be formatted.
Quote Text Data
Defines if text data should appear between quotes or not. Selecting the
ANSI choice will automatically prefix any single quotes with an
additional one.
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
Settings
Clicking the Settings button reveals a a menu with options for
saving and loading settings to and from
a file.
- 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 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 @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.
| 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 only used if exporting
in HTML, XML 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 destination will transfers the export data to the
SQL Commander editor. It is primarily useful when exporting
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 where the
exported output should go.
Figure:
Export
window for text format result sets
Export
Graph data
Exporting references graphs exports the graph 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 or GIF formats.
Export Chart data
The options when exporting charts are the same as for graphs, but also
let you 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
the 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.
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 using the data import feature to grid
and then use the Export Wizard in the grid to output the grid data in
the desired format.
The Import Wizard is launched via the right-click menu for table
objects or via the Actions menu.
Figure: Import Table Data action in the right-click
menu for table objects
Note 1: The first row in the source file can be used
to name the columns.
Note 2: The Import Wizard can not be used to import
binary data.
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 Settings 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. 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 assigns the
columns in the source file with
the first columns in the target table. 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
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
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
Print
The printing support in DbVisualizer supports printing of grids,
graphs, charts and plain text, such as the content of an SQL Editor.
The print dialog looks somewhat different depending on what is
printed. In all cases, you launch the print dialog by clicking on the
Print button in the toolbar for the object you want to print, or by
choosing Print from the right-click menu. The right-click
menu
also contains a Print Preview choice, if you want to see what the
printout will look like before you actually print.
Printer
Setup
If you want to set the page orientation (e.g., portrait or landscape)
and paper size, you must launch the Printer Setup dialog, using the File->Printer Setup
main menu option, before you print. Printing varies widely between
platforms, so even though the Print dialog (as opposed to the
Printer Setup dialog) on some platforms also lets you choose a page
orientation and other options, they may be ignored if
specified
in that dialog. The only supported way to specify the page orientation
and other options is via the Printer Setup dialog.
Grid,
Chart and Plain Text
For a grid, chart and plain text, DbVisualizer launches the platform's
native Print dialog, so it looks different on different platforms. The
two options available on all platforms are a choice of printer and the
page range. On some platforms, the dialog may offer additional options,
but they may be ignored by DbVisualizer. Use the Printer Setup dialog
to set other options than which printer to use and the page range, as
described above.
Figure:
Standard
print dialog
The figure above shows how the Print dialog looks on the Linux
platform.
When you print a grid in DbVisualizer, the grid is printed as it is
shown on the screen, i.e., with the table headers, sort and primary key
indicator,
etc. It is printed as a screenshot that may span several pages,
depending
on the number of rows and columns that are printed. For a grid, the
right-click menu contains a Print Selection choice that you can use if
you just want to print selected rows and columns.
An alternative to printing a grid as a screenshot is to export the grid
to HTML and then use a web browser to
print
it.
Printing a chart scales the chart to the size of the paper. Plain text
is printed as-is and may span multiple pages, both in height and width.
Graph
Printing a graph adds a custom dialog before the native Print dialog is
displayed.
Figure:
Print
options when printing graphs
You can specify the number of rows (pages)
and columns (pages) that the complete image will be split into. You can
also select whether the view as it appears on the screen
or the complete graph should be printed. When you click Ok, the native
Print dialog is displayed, where you can select the printer.
Print
Preview
Use the File->Print
Preview
feature to preview what the printout will look like before you actually
print it.
| Grid |
Graph |
 |
 |
Figure: Grid and graph print previews
Copyright © 2008 Onseven Software AB. All rights reserved.
