Exporting data to Excel and CSV files

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

Prerequisites

None.

Context and remarks

Many.

 Export data to an Excel file using the OUTPUT statement (Interactive SQL)

  1. In Interactive SQL, connect to a SQL Anywhere database.

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

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

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

    Note that the Microsoft Excel driver is a 32-bit driver so the 32-bit version of Interactive SQL is required for this example.

 Export data to a CSV file

  1. In Interactive SQL, connect to the SQL Anywhere database.

  2. Execute an OUTPUT statement with the clauses FORMAT TEXT, QUOTE '"', and WITH COLUMN NAMES to create a comma-delimited format with the column names in the first line of the file. String values will be enclosed with quotation marks. 

    SELECT * FROM SalesOrders; 
OUTPUT TO 'c:\\test\\sales.csv' 
    FORMAT TEXT
    QUOTE '"'
    WITH COLUMN NAMES;

Results

The data is exported to the specified Excel or CSV file.

Next

None.

Example

The following example exports the data from the Employees table in the SQL Anywhere sample database to a .txt file named Employees.txt. 

SELECT * FROM Employees;
OUTPUT TO Employees.txt
    FORMAT TEXT;

The following example exports data from the Employees table in the SQL Anywhere sample database to a new table in a SQL Anywhere database named mydatabase.db

SELECT * FROM Employees;
OUTPUT USING 'driver=SQL Anywhere 12;UID=dba;PWD=sql;DBF=C:\Tobedeleted\mydatabase.db;CON=''''' 
    INTO "dba"."newcustomers" 
    CREATE TABLE ON;

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1