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:
- 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.
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.
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’
COPY employees FROM '/path/to/employees.csv' DELIMITERS ',' CSV;
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!