Importing and Exporting Data

You can import data into an existing table, or export data from a table or view into a file.

This section covers the following topics:

Import/Export File Types

Cloudview uses two types of files when importing and exporting data:

Note Regarding Import and Autoincrement Default Columns

NEW: In version 3.6 you can specify an autoincrement default for int type columns.

You cannot import values into a column defined with an autoincrement default. To import into a table with an autoincrement default, you must first modify the table to drop the default. Once the import is complete, you can re-add the column default.

For more information, see "Adding or Dropping an Autoincrement Default".

Opening the Import/Export Window

To import data into a table or export data from a table or view:

  1. Highlight the table or view name in the left panel.
  2. Do one of the following to display the Export file browser:
  3. From the file browser, choose or specify a data file.

    The Import/Export window appears.

The title bar of the Import/Export window indicates the name of the table or view that you are importing data into or exporting data from.

Use Advanced Import/Export to import or export data using custom or explicit settings for separators, column definitions, field start and end characters, or to import/export using a control file. To perform Simple Import/Export, use the General tab of the Import/Export window.

Simple Import/Export

Use the General tab to perform a simple import/export. In a simple import/export, you can optionally specify codeset, locale, a delimiter symbol, and quote characters or strings.

  1. Choose the General tab.
  2. Choose separator and quote characters or strings.
  3. Optionally, specify a Codeset type and Locale. The codeset and locale fields default to the locale or codeset of your Java System.
  4. Click OK.

For example, you could use the default settings to export or import the following data:

"barbie","9.50","0"
"beanie baby elephant","6.50","0"

Advanced Import/Export

Use the Advanced tab to perform an import/export under the following conditions:

If you are importing/exporting delimited fields, see "Delimited Data". If you are importing or exporting fixed-width fields, see "Fixed-Width Data".

Delimited Data

Use the Advanced tab to import/export delimited or fixed width data, data with explicit column definitions, or data with custom field and record separators. You can also choose whether to replace a table's existing data on import.

  1. Choose the Advanced tab.
  2. Choose Delimited in the Type field.

You can specify the following values:

NEW: The ability to specify terminating delimiters is new in version 3.6.

All these settings (except Column Definition and End Delimiter, which are either true or false) can be either single characters or strings. You can indicate a tab with "\t" and a backslash with "\\".

For example, the following settings:

Field Separator: Semicolon
Field Start: `
Field End: '
Record Separator: Empty line
Column Definition: true
Delimiter at End: false

might export the following data into the data file:

`TOY' `PRICE' `QUANTITY'
`barbie';`9.50';`0'
`beanie baby elephant';`6.50';`0'

Fixed-Width Data

To create or read from a data file that uses fixed-width columns instead of field separators:

  1. Choose the Advanced tab in the Import/Export window.
  2. Choose Fixed Width in the Type field.
  3. Set the record separator in the Record Separator field, and enter in the Null field the string that represents null values.
    The grid displays the column names in the table or view and (for export) default column widths.
  4. If the default widths are acceptable, click OK. To change the defaults, do one of the following:

Control Files

Control files store data file formatting information.

To save the current Import/Export window settings to a control file:

  1. Choose the Advanced tab in the Import/Export window.
  2. Click Save in the Control File box.
  3. Use the file browser to name and situate the file.

To retrieve settings from a control file:

  1. Choose the Advanced tab in the Import/Export window.
  2. Click Open in the Control File box.
  3. Use the file browser to find and open the control file.

NOTE: Use Cloudview, not a text editor, to create and alter control files. Control file formats may change from release to release. Cloudscape supports creating and changing control files only if performed using Cloudview.