Skip to main content

Importing and Exporting SQL Data

In the InterSystems IRIS® data platform 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. InterSystems IRIS cannot import a row that exceeds the string length limit, and within InterSystems IRIS, no row can exceed that limit.

You can also import data using the %SQL.Import.MgrOpens in a new tab class, and export data using the %SQL.Export.MgrOpens in a new tab class.

Importing Data with LOAD DATA

You can import data into InterSystems IRIS using the LOAD DATA SQL command. This utility can be used to import data either from a file or from a table accessed with JDBC. Before calling LOAD DATA, you must first use CREATE TABLE to define the table and its columns.

If the table is empty when loading data, LOAD DATA populates the table with rows from the data source. If the table already contains data, LOAD DATA inserts rows into the table without overwriting any data in the table.

The following example creates a table and loads data into it using a file stored on a local system, named people.csv:

>>> CREATE TABLE Sample.Person (
        Name VARCHAR(25),
        Age INT,
        DOB DATE)

>>> LOAD DATA FROM FILE 'C://sampledata/people.csv' INTO Sample.Person

LOAD DATA also provides options for speeding up the loading operation through use of the BULK keyword. Refer to the LOAD DATA reference for a full description of this behavior.

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 by clicking the name of the current namespace displayed 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.

FeedbackOpens in a new tab