Exporting data to an Excel file

In Interactive SQL, you can export data from your database to an Excel file by using the OUTPUT statement.

Prerequisites

You must have SELECT privilege on the table, or SELECT ANY TABLE system privilege.

If the Excel ODBC driver is a 32-bit driver then you must use a 32-bit version of Interactive SQL to avoid an architecture mismatch error.

Task

In Interactive SQL, connect to a SQL Anywhere database.

Execute an OUTPUT statement using the READONLY clause. For example:

SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);DBQ=sales.xls;READONLY=0' 
INTO "newSalesData";

A new Excel file, named sales.xls, is created. It will contain a worksheet called newSalesData.

Results

The data is exported to the specified Excel file.

Example

The following example exports data from the Employees table in the sample database to a new table in a database named demo2. The user ID used to connect to the second database must have the CREATE TABLE, CREATE ANY TABLE, or CREATE ANY OBJECT system privilege.

SELECT * FROM Employees;
OUTPUT USING 'DRIVER=SQL Anywhere 16;UID=DBA;PWD=sql;DBN=demo2;CON=''''' 
    INTO "newEmployees" 
    CREATE TABLE ON;

Exporting data to an Excel file

Task

See also