Importing data with the INPUT statement

You can import data into a database from a text file or from a comma delimited (CSV) file using Interactive SQL.

Prérequis

You must be the owner of the table, or have the following privileges:

INSERT privilege on the table, or the INSERT ANY TABLE system privilege
SELECT privilege on the table, or the SELECT ANY TABLE system privilege

Contexte et remarques

Because the INPUT statement is an Interactive SQL statement, you cannot use it in any compound statement (such as an IF statement) or in a stored procedure.

Task

Create a text file named newSwimwear.csv with the following values and save it to a C:\LocalTemp directory:

ID,Name,Description,Size,Color,Quantity,UnitPrice
800,Swimsuit,Lycra,Small,Blue,10,81.00
801,Swimsuit,Lycra,Medium,Blue,10,81.00
802,Swimsuit,Lycra,Large,Blue,7,85.00

Open Interactive SQL and connect to the SQL Anywhere sample database.

Type an INPUT statement in the SQL Statements pane.

INPUT INTO Products
FROM C:\LocalTemp\newSwimwear.csv
FORMAT TEXT
SKIP 1;

In this statement, the name of the destination table is Products, and newSwimwear.csv is the name of the data file. The first line of the file containing column names is skipped. The file is located relative to the client computer.

Execute the statement.

If the import is successful, the Messages tab displays the amount of time it to took to import the data. If the import is unsuccessful, a message appears indicating why the import was unsuccessful.

Résultat

The data is imported into the specified database.

Exemple

Perform the following steps to input data from an Excel CSV file using the INPUT statement.

In Excel, save the data from your Excel file into a CSV file. For example, name the file newSales.csv.
In Interactive SQL, connect to a SQL Anywhere database, such as the sample database.
Create a table named imported_sales and add the required columns.

Execute an INPUT statement using the SKIP clause to skip over the column names that Excel places in the first line of the CSV file.

INPUT INTO imported_sales FROM 'C:\\LocalTemp\\newSales.csv' SKIP 1;

Importing data with the INPUT statement

Task

See also