Exporting a Grid
You can export any grid using the Export Wizard, e.g. a result set grid, a grid showing tables in a schema, or the Data tab for a table or view.
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.
Select an output format, file encoding (it is also used to set the encoding in the HTML and XML headers, if you select one of those formats), and how to quote text data.
Only in DbVisualizer Pro
With the DbVisualizer Free edition, only the CSV and HTML formats are supported.
The Options section is used to define settings that are specific for the selected output format, for instance the column and row delimiters for the CSV format, or the Excel or Excel 2007 format for XSL.
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.
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.
The columns in this page's grid can be used like this.
| Column | Descriptions |
|---|---|
| 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, JSON or SQL format. Column headers are optional in the CSV output format. |
| Label (Alias) | When you export a result set grid for a SELECT statement that uses column aliases, this column holds the alias. If you have also enabled Use any Label (Alias) in the Options section, this value is used in place of the name. |
| 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. |
Generating Test Data
The test data generator is useful when you need to add random column data to the exported output.
The Value column in the Data page grid 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}$ variable, to be exported as entered.
Alternatively, you can use test data generator variables in the Value column. The choices are available in the right-click menu when you edit the Value column.
| 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. |
Here is an example of how to use the test data generators to try out planned changes to the data. Consider this initial data:
After the changes, the JOB_ID column should not appear in the output and the new JOB_CODE should contain abbreviated job codes. To test this, we simply uncheck the Export checkbox for JOB entry and set the Value for the JOB_CODE to use the Generate random value from a list of values function. By unchecking the "Export" checkbox for the column JOB_ID this column will not be included in the export.
Previewing the data (or exporting it) in CSV format results in this:
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, one of File, SQL Commander or Clipboard.
Click Export on this page to export the grid data to the selected destination.
Settings Menu
If you often use the same settings, you can save them as the default settings for this assistant. If you use a number of common settings, you can save them to individual files that you can load as needed. Use the Settings drop-down button menu to accomplish this:
- Save as Default Settings Saves all format settings as default. These are then loaded automatically when open an Export Schema dialog
- Use Default Settings Use this choice to initialize the settings with default values
- Remove Default Settings Removes the saved defaults and restores the regular defaults
- 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 Copies the settings to the system clipboard
You can also use settings saved here with the @export client side command.