Importing and Exporting SQL Data

In the InterSystems IRIS® data platform Management Portal, there are tools for importing and exporting data. Internally 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.Mgr class, and export data using the %SQL.Export.Mgr class.

Importing Data with LOAD DATA

To import data into an SQL table within InterSystems IRIS, you must create a table to contain the data first (if one does not already exist). You can do this by defining a persistent class which projects itself as a table or by issuing a CREATE TABLE command.

Once you have created the table which will contain your data, you can import the data using one of the following methods:

Issue a LOAD DATA SQL command. This utility can be used to import data either from a file or from a table accessed with JDBC. 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.
If your data is stored in a .csv or .txt file: use the Management Portal’s Data Import wizard. This wizard can insert records into the table from the file, mapping the file’s columns onto existing columns in the table. To do this:
1. From the Management Portal, select System Explorer, then SQL.
2. At the top of the main pane, click the Wizards drop-down list, and select Data Import.
3. On the first page of the wizard:
  - Select the radio button which corresponds to the file system where your file is located: you can choose to import data from your machine’s local file system (My Local machine) or from the instance’s host environment (identified by its host name), in deployments where this is different from the machine’s local file system—when the instance is running within a container, for example.
  - Select Browse, and then use the dialog window to specify the path and name for the .csv or .txt file which contains the data that you would like to import.
  - Optionally: if the file uses a Charset other than the system default, select it from the drop-down list.
  - From the drop-down lists, select the namespace, schema name, and table name for the table into which you would like to import your data.
  Then click Next.
4. On the second page of the wizard, use the arrow buttons to select the table columns into which you would like to import your data from the list of Available columns. (By default, all of the columns in the table are Selected.) Use the up and down arrows to ensure that the selected columns are listed in same order in which the analogous columns appear in your data file. Then click Next.
5. On the third page of the wizard:
  - For What delimiter separates your columns?, select the option which identifies the delimiter used to separate columns in your file. If columns are separated by a particular character, use the text box to specify the character.
  - Ensure that the First row contains column headers? checkbox is selected if the first row of your data file contains the file’s column headers. Clear the checkbox if it does not.
  - For String quote, use the drop-down list to select the character which the file uses to indicate the start and end of strings.
  - For Date format, click an option to indicate the format that the file uses for date values.
  - For Time format, click an option to indicate the format that the file uses for time values.
  - For TimeStamp format, click an option to indicate the format that the file uses for Timestamp values.
  - If you do not want the wizard to attempt to validate the data during the import process, select the Disable validation? checkbox. If checked, the wizard specifies %NOCHECK in the restriction parameter of the INSERT command that it issues.
  - Select Defer Index Building with %SortBegin/%SortEnd? if you want the wizard to build indexes after the data has been inserted. When selected, the wizard calls the class's %SortBegin() method prior to inserting the data in the table. This causes the index entries to be written to a temporary location for sorting. They are written to the actual index location when the wizard calls the %SortEnd() method, after all rows have been inserted.
    InterSystems recommends that you do not defer index building if the destination table contains unique indexes, because it prevents InterSystems IRIS from catching violations of the unique constraint during the import process.
  - Optionally: click Preview Data to view some of your file’s contents.
  Then click Next.
6. Review your entries and click Finish. The wizard starts a background task to perform the import, and displays the Import Result dialog window.
7. Within the Import Result window, click the link provided to view the background tasks page. From there, you can check the status of the import task and review any errors which may have occurred.
8. Click Close to close the Import Result window.

Exporting Data to a Text File

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

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.
At the top of the main pane, click the Wizards drop-down list, and select Data Export.
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: if the file uses a Charset other than the system default, select it from the drop-down list.
Then click Next.
On the second page of the wizard, select which columns to export. Then click Next.
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. If columns should be separated by a particular character, use the text box to specify the character.
- 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 the file should indicate the start and end of strings.
- For Date format, click an option to indicate the format that the file should use for date values.
- For Time format, click an option to indicate the format that the file should use for time values.
- Optionally: click Preview Data to see what the results will look like.
Then click Next.
Review your entries and click Finish. The wizard displays the Data Export Result dialog box.
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.