Exporting data from a database

User's Guide
PART 1. Working with Databases
CHAPTER 11. Importing and Exporting Data

Exporting data from a database

You can export data from your database using one of the following methods:

The UNLOAD TABLE statement, for efficient export of text files
The Interactive SQL OUTPUT statement, for slower but more flexible export to a variety of file formats
The DBUNLOAD utility, for text export of more than one table

This section describes each of these methods, and also describes some tips for dealing with NULL output.

Unloading data using the UNLOAD TABLE statement

The UNLOAD TABLE statement is used for efficient export of data from a database table to a text file. You must have SELECT permission on the table to use the UNLOAD TABLE statement.

Example 1

The following statement unloads the department table from the sample database into the file dept.txt in the server's current working directory. If you are running against a network server, the command unloads the data into a file on the server machine, not the client machine.

UNLOAD TABLE department
TO 'dept.txt'

The dept.txt file has the following contents:

100,'R & D',501
200,'Sales',902
300,'Finance',1293
400,'Marketing',1576
500,'Shipping',703

Notes

Each row of the table is output on a single line of the output file.
No column names are exported.
The columns are separated, or delimited, by a comma. The delimiter character can be changed using the DELIMITED BY clause.
The fields are not fixed-width fields. Only the characters in each entry are exported, not the full width of the column.
The character data in the dept_name column is enclosed in single quotes. The single quotes can be turned off using the QUOTES clause.
The data is exported in order by primary key values. This makes reloading quicker. You can export data in the order in which it is stored using the ORDER OFF clause.
The file name is relative to the server's current directory, not the current directory of the client application. Also, the file name is passed to the server as a string. It is recommended that you escape backslash characters in the file name to prevent misinterpretation if a directory of file name begins with an n (\n is a newline character). For example, the following statement unloads a table into the file c:\temp\newfile.dat:
```
UNLOAD TABLE employee 
TO 'c:\\temp\\newfile.dat' 
```

For Info For more information on the syntax, see UNLOAD TABLE statement.

Example 2

The following example uses explicit settings for the DELIMITED BY and QUOTES clauses:

UNLOAD TABLE department
TO 'dept.txt'
DELIMITED BY '$'
QUOTES OFF

The resulting dept.txt file has the following contents:

100$R & D$501
200$Sales$902
300$Finance$1293
400$Marketing$1576
500$Shipping$703

If a delimiter character appears within a value and the QUOTES option is turned off, the character is replaced by its hexadecimal value preceded by \x. For example:

UNLOAD TABLE department
TO 'dept.txt'
DELIMITED BY '&'
QUOTES OFF

yields the following output file.

100&R \x26 D&501
200&Sales&902
300&Finance&1293
400&Marketing&1576
500&Shipping&703

Exporting query results using Interactive SQL

You can export queries to a file from Interactive SQL either by using the OUTPUT statement or by redirecting output.

Using the output statement

Data can be exported from a database to a variety of file formats using the Interactive SQL OUTPUT statement. This statement exports the results of the current query (the one displayed in the Interactive SQL Data window) and puts the results into a specified file. The output format can be specified on the output command.

For example, the following commands extract the employee table to a dBaseIII format file:

SELECT *
FROM employee;
OUTPUT TO employee.dbf
FORMAT dbaseiii;

Using output redirection

Output redirection can be used to export data as an alternative to the OUTPUT statement.

The output of any command can be redirected to a file or device by putting the ># redirection symbol anywhere on the command. The redirection symbol must be followed by a file name, as follows:

SELECT *
FROM employee
># filename

Do not enclose the file name in quotation marks.

Output redirection is most useful on the SELECT statement. The SET OUTPUT_FORMAT command can be used to control the format of the output file.

Redirecting with error messages

The >& redirection symbol redirects all output including error messages and statistics for the command on which it appears. For example:

SELECT *
FROM employee 
>& filename

outputs the SELECT statement to the file, followed by the output from the SELECT statement and some statistics pertaining to the command.

Do not enclose the file name in quotation marks.

The >& redirection is useful for getting a log of what happens during a READ command. The statistics and errors of each command are written following the command in the redirected output file.

If two > characters are used in a redirection symbol instead of one (>># or >>&.), the output is appended to the specified file instead of replacing the contents of the file. For output from the SELECT statement, headings are output if and only if the output starts at the beginning of the specified file and the output format supports headings.

NULL value output

The most common reason to extract data is for use in other software products. The other software package may not understand NULL values.

There is a Interactive SQL option (NULLS) that allows you to choose how NULL values are output. Alternatively, you can use the IFNULL function to output a specific value whenever there is a NULL value.

For information on setting Interactive SQL options, see SET OPTION statement.

Unloading a database using DBUNLOAD

The dbunload utility is used to unload an entire database in ASCII comma-delimited format and to create the necessary Interactive SQL command files to completely recreate your database. This may be useful for creating extractions, creating a backup of your database, or building new copies of your database with the same or a slightly modified structure.

If you want to rearrange your tables in the database, you can use dbunload to create the necessary command files and modify them as needed.

For Windows 3.x, the DBUNLOAD executable is named dbunloaw.exe.

For Info For a full description of dbunload utility command-line switches, see the section The Unload utility.

Exporting a list of tables

The dbunload utility can also export a list of tables, rather than the entire database. This is useful for retrieving data from a corrupted database that cannot be entirely unloaded.

The following statement unloads the data from the sample database (assumed to be running on the default database server with the default database name) into a set of files in the c:\temp directory. A command file to rebuild the database from the data files is created with the default name reload.sql in the current directory.

dbunload -c "dbn=asademo;uid=dba;pwd=sql" c:\temp