[ Master documentation index ]
Introduction
The Export feature writes data that has been fetched
and presented in DbVisualizer to a file. The Export Wizard dialog looks
different depending on whether a grid,
graph
or chartdata
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.
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.
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.
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
The Data page that produced the grid in the previous figure looks like
this:
Figure:
The export window
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:
EMPNO,
ENAME, JOB_FUNCTION,
MGR, HIREDATE, SAL, COMM, DEPTNO
7369, SMITH, adm, 7902, 2005-01-24 12:11:08, 800, (null), 20
7499, ALLEN, adm, 7698, 2005-01-24 12:11:08, 1600, 300, 30
7521, WARD, eng, 7698, 2005-01-24 12:11:08, 1250, 500, 30
7566, JONES, adm, 7839, 2005-01-24 12:11:08, 2975, (null), 20
7654, MARTIN, eng, 7698, 2005-01-24 12:11:08, 1250, 1400, 30
7698, BLAKE, eng, 7839, 2005-01-24 12:11:08, 2850, (null), 30
7782, CLARK, eng, 7839, 2005-01-24 12:11:08, 2450, (null), 10
7788, SCOTT, eng, 7566, 2005-01-24 12:11:08, 3000, (null), 20
7839, KING, eng, (null), 2005-01-24 12:11:08, 5000, (null), 10
7844, TURNER, eng, 7698, 2005-01-24 12:11:08, 1500, 0, 30
7876, ADAMS, fin, 7788, 2005-01-24 12:11:08, 1100, (null), 20
7900, JAMES, eng, 7698, 2005-01-24 12:11:08, 950, (null), 30
7902, FORD, eng, 7566, 2005-01-24 12:11:08, 3000, (null), 20
7934, MILLER, fin, 7782, 2005-01-24 12:11:08, 1300, (null), 10
7939, MILLER, fin, 7782, 2005-01-24 12:11:08, 1300, (null), 10
...
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 boxes.
Just select the appropriate type for each column.
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. You can then manually reassign
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 data type 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. Errors that occur during the import process
are presented in the log.
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 © 2007 Onseven Software AB. All rights reserved.
