Skip to main content
Previous section   

Importing and Exporting SQL Data

In the Management Portal, there are tools for importing and exporting data:

These tools use Dynamic SQL, which means that queries are prepared and executed at runtime. By default, the maximum size of a row that can be imported or exported is 32,767 characters. This limitation can be greatly expanded by configuring long string operations.

You can also import data using the %SQL.Import.Mgr class, and export data using the %SQL.Export.Mgr class.

Importing Data from a Text File

You can import data from a text file into a suitable Caché class. When you do so, the system creates and saves new rows in the table for that class. The class must already exist and must be compiled. To import data into this class:

  1. From the Management Portal select System Explorer, then SQL (System, SQL). Select a namespace with the Switch option at the top of the page; this displays the list of available namespaces.

  2. At the top of the page, click the Wizards drop-down list, and select Data Import.

  3. On the first page of the wizard, start by specifying the location of the external file. For The import file resides on, click the name of the server to use.

  4. Then enter the complete path and filename of the file.

  5. For Select schema name, click the Caché package into which you want to import the data.

  6. For Select table name, click the class that will contain the newly created objects.

  7. Then click Next.

  8. On the second page of the wizard, click the columns that will contain the imported data.

  9. Then click Next.

  10. On the third page of the wizard, describe the format of the external file.

    • For What delimiter separates your columns?, click the option corresponding to the delimiter in this file.

    • Click the First row contains column headers? check box if the first line of the file does not contain data.

    • For String quote, click the option that indicates the quote delimiter character this file uses to start and end string data.

    • For Date format, click the option that indicates the date format in this file.

    • For Time format, click the option that indicates the time format in this file.

    • For TimeStamp format, click the option that indicates the timestamp format in this file.

    • Click the Disable validation? check box if you do not want the wizard to validate the data upon import.

    • Click the Defer Index Building with %SortBegin/%SortEnd? check box if you do not want the wizard to rebuild indices during import. If Defer Index Building is checked, the wizard calls the %SortBegin method for the class before inserted the imported data into the table. When the import is done the wizard calls the %SortEnd method. No validation is done (same as an INSERT with %NOCHECK). This is because indices cannot be checked for uniqueness during SQL insert when %SortBegin/%SortEnd is used. If Defer Index Building is checked, the imported data is assumed to be valid and will not be checked for validity.

    • Optionally click Preview Data to see how the wizard will parse the data in this file.

  11. Click Next.

  12. Review your entries and click Finish. The wizard displays the Data Import Result dialog box.

  13. Click Close. Or click the given link to view the background tasks page.

    In either case, the wizard starts a background task to do the work.

Exporting Data to a Text File

You can export data for a given class to a text file. To do so:

  1. From the Management Portal select System Explorer, then SQL. Select a namespace with the Switch option at the top of the page; this displays the list of available namespaces.

  2. At the top of the page, click the Wizards drop-down list, and select Data Export.

  3. On the first page of the wizard:

    • Enter the complete path and filename of the file that you are going to create to hold the exported data.

    • From the drop-down lists, select a Namespace, Schema Name, and Table Name from which you want to export the data.

    • Optionally select a character set from the Charset drop-down list; the default is Device Default.

    Then click Next.

  4. On the second page of the wizard, select which columns to export. Then click Next.

  5. On the third page of the wizard, describe the format of the external file.

    • For What delimiter separates your columns?, click the option corresponding to the delimiter in this file.

    • Click the Export column headers? check box if you want to export column headers as the first line of the file.

    • For String quote, click an option to indicate how to start and end string data in this file.

    • For Date format, click an option to indicate the date format to use in this file.

    • For Time format, click an option to indicate the time format to use in this file.

    • Optionally click Preview Data to see what the results will look like.

    Then click Next.

  6. Review your entries and click Finish. The wizard displays the Data Export Result dialog box.

  7. Click Close. Or click the given link to view the background tasks page.

    In either case, the wizard starts a background task to do the work.