Importing CSV Files to SQL Databases

CSV is a data exchange format that most software can export to, since it is a simple list of values separated by commas (hence the name). Spreadsheet programs like OpenOffice Calc, Microsoft Excel, Google Docs Spreadsheet and others commonly have an ‘Export to CSV’ option, often found under the File menu.

There are scripts that convert CSV to SQL, but most modern database systems support reading CSV data such as:

• PostgreSQL
• MySQL
• Oracle
• Microsoft SQL Server
• Sybase Adaptive Server Enterprise (which uses the same Transact-SQL dialect as MS-SQL).

As they all use different syntax to import CSV data, examples are provided.

Laying Out the Data

This reference relies on a few assumptions. Firstly, it assumes you have obtained your data in CSV format. Secondly, good sense assumes you would not be doing this if you had a better way. Important metadata is lost by using CSV as an intermediate format. Details like Foreign Keys, column constraints, and so on.

For the purposes of this guide, we will be using the following dataset.

"id","firstName","lastName"
12345,"Ingrid","Insert"
23456,"Eric","Example"
12543,"Dave","Dataset"

All examples that follow use this CSV set.

Creating the Table

A simple example of a table we are importing the CSV file to.

CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY,
firstName VARCHAR(32) NOT NULL,
lastName VARCHAR(48) NOT NULL
);

Importing the Data

This process isn’t terribly complicated, but it does vary according to the database system you are importing to.

MySQL

LOAD DATA INFILE '/path/to/employees.csv' INTO TABLE employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Microsoft SQL Server and Sybase ASE

BULK INSERT employees FROM ‘c:\path\to\employees.csv’ WITH (
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
);
GO

PostgreSQL

COPY employees FROM '/path/to/employees.csv' DELIMITERS ',' CSV;

Oracle

Offhand I don’t know a way to import a CSV file into Oracle using only SQL or PL/SQL, but there’s a great article on Dev Shed that covers how to do it with the Oracle 10G XE administration interface. Here is a convenient link to the good part, since if you’re reading this, you have likely already exported the CSV data, and want to know how to import that CSV to SQL.

Alternatively, you could convert your CSV file to SQL INSERT statements, and then execute those INSERTS. A simple CSV to SQL Conversion Tool can do this effectively.

If anyone knows how to import CSV files into Oracle without using the admin GUI or an external tool, please share!

References

• http://dev.mysql.com/doc/refman/5.1/en/load-data.html
• http://www.ensode.net/postgresql_csv_import.html
• http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

No related posts.