[ Master documentation index ]
Introduction
The export feature is used to export data that has been fetched
and presented in DbVisualizer. The export wizard dialog looks
different depending on whether a grid,
graph
or chart
data is going to be
exported. The following sections describe the settings that can be
made in each of these contexts. There are major differences between DbVisualizer Free and Personal when
exporting grid data. This document explains the complete functionality
in the Personal edition even though it implicitly covers the export
functionality in DbVisualizer Free.
The import feature is used to import data stored in CSV (Character Separated Values) format from files.
The printing feature can be used to print grid and graph data to printer or file.
Exporting very large result sets using the standard export feature may
fail with memory problems since all data must first be
presented in DbVisualizer. Using the @export
client side command in the SQL Commander solves this problem
since the data is exported on the fly while it is fetched from the
database.
Export
Grid data
The Export wizard is primarily initiated using the File->Export
main menu choice. This operation examines the current context and
displays the appropriate wizard. The File->Export
Selection main menu choice is
specifically for Grid
contexts and is used to export
the current selection instead of all data in the grid. It is only
enabled if the current context is a grid and if there are any selected
cells in it. In addition all grids throughout DbVisualizer offer the
right click menu choice for Export
Selection. It is a shortcut for the File->Export
Selection main menu
operation.
Settings
page
There are a number of options to configure how the data should be
exported. The settings page contain general properties that control 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
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>
|
Encoding
The encoding choice controls what encoding the data will be exported
in. This will also 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
The Settings button lists when pressed a menu with options to save and load settings to and from
a file.
- Use
Default Settings
Press this button to initiate the settings with default values. Some of
the settings will be fetched from the general tool properties dialog.
- Load
Press this button to open the file choose 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
The columns list is used to control what columns will be exported
and the format of their data. 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 export then it will not appear in this list.
Figure:
The grid export wizard
The Table Rows
fields tells how many rows that are available and the choice to
optionally specify the number of rows to export. This setting along
with the Add Row
button is especially useful if using 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 will be surrounded in 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 will be enclosed in quotes.
|
Value
|
The
default "$$value$$"
variable will simply
be substituted by the actual 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 actual value of the data that is going
to be in the exported output is referenced by the $$value$$
variable in the Value field. This variable is simply replaced by the
real value during
the export process. Additional static data can be added before and
after the $$value$$
field
and will be exported as entered. The value field is also the place to
setup any test data generators. While in the editing mode of the value
field there is a right click menu with the supported generator
functions.
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 follows an example that utilizes the test data generators.
Consider
this data:
Figure:
Sample of
grid data
The Data page will look like this based on exporting the previous grid.
Figure:
The export window
The JOB column should not appear in the output and the new JOB_FUNCTION
should contain abbreviated job functions. To accomplish this we simply
uncheck the Export field for JOB entry. The Value for
the JOB_FUNCTION is set 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 preview page shows 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 then just use the back button to modify the settings.
Output
Destination
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
|
Export
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. 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 when 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 will export the graph in the same zoom
level as it appears on the screen. The export window when exporting
graphs looks like this:
Figure:
Export
window for graphs
The
export
window is quite limited as compared to when exporting grids. The graph
can only be exported to a File
in the JPEG
or GIF
formats.
Export of graphs cannot be previewed or exported to any other
destination then file.
Export
Chart data
Exporting charts adds the capabilities to set the size and orientation
of the exported file.
Figure:
Export
window for charts
A chart can only 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 initial 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
will be changed to
match the selected size. Setting the width and height or selecting a
pre-defined size will scale the exported image accordingly.
Export of charts
cannot be previewed or be exported to any other destination then file.
Import Table Data
The import table data feature is used to import files whose data is
organized as columns with separator characters between them. The
destination for the imported data can be to a database table or a grid
in DbVisualizer. The grid option is convenient for smaller files as the
grid functionality then can be used to do various things with the data.
An example is that a CSV file rather easily can be converted into an
XML file or a HTML document by using the data import feature to grid
and then use the export functionality in the grid to output the grid 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 is used to find out the actual 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.
Figure: The Source File import wizard page
Settings
In the settings page you specify options how the data in the file is organized. The Data section at the bottom of the page lists a preview of the parsed data in the Grid tab while the File
tab shows the original source file. If a row in the Grid tab is red
then it indicates that the row will be ignored during the import
process. This happens if setting any of the Options that will result in rows not being qualified.
In the Delimiters section define what character that separates the columns in the file. If enabling the Auto Detect choice then DbVisualizer will try 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 red. The reason is that the Skip First Row(s) and Skip Rows Starting With is 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 here contains a drop down box which lists
the actual data types. Just select the appropriate type for the 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 initially shows 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 it will show information about the table in which
the data will be imported. The Map Table Columns with File Columns grid
will show what
columns are in the selected database table and a column with the
columns in the source file. You can here select what fields in the
source file should be imported into what columns.
DbVisualizer automatically assigns the columns in the source file with
the first columns in the target table. You can then manually assign
them. 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
will be presented in the log.
Figure: The import process page
Print
The printing support in DbVisualizer supports printing of Grids and
Graphs. The print dialog looks somewhat different depending on what is
printed.
Note: Printing of charts is currently not supported. The workaround is simply
to export the chart and then use your favorite printing
tool to get it on paper (a standard web browser is sufficient).
Grid
Printing a grid in DbVisualizer causes the visual grid to be output on
paper. This includes the table headers, sort and primary key indicator,
etc. It can be output as a screen shot that spans several pages
depending
on the number of rows and columns that are printed. The other solution
to printing grids is to export to HTML and then use a web browser to
print
it. The choice of which is more attractive than the other is up to
you to decide.
Figure:
Standard
print dialog
The content and layout of the print dialog is
platform specific. The above screen shot is from Linux/RedHat.
Graph
The graph printing setup dialog adds a step before the standard
printing dialog is displayed.
Figure:
Print
options when printing graphs
It is possible to specify the number of rows (pages)
and columns (pages) that the complete image will be divided into. It is
also possible to select whether the view as it appears on the screen
will be
printed or the complete graph.
Print
Preview
The File->Print
Preview
feature is used to preview a grid or graph before print.
Grid
|
Graph
|
 |
 |
Figure:
Grid and graph print
previews
Copyright © 2006 Onseven Software AB. All rights reserved.
