It was over the last holiday season that I wanted to take some time and play around a bit with data. The outcome was rather different than I initially expected. Rather than having worked with the data, I spent a lot of time trying to get some CSV data loaded into a database. I knew that there are great tools such as SQL Developer out there that make loading CSV data trivial, but I wanted something that I could execute via the command line, not requiring a GUI. The simple reason was that I wanted to script the loading part, rather than having to execute some manual, potentially error prone steps whenever loading some data again. Too late I found out, actually just last week, that SQLcl already had what I was after, a load command that does exactly what I wanted:

SQL> help load
LOAD
-----

Loads a comma separated value (csv) file into a table.
The first row of the file must be a header row. The columns in the header row must match the columns defined on the table.

The columns must be delimited by a comma and may optionally be enclosed in double quotes.
Lines can be terminated with standard line terminators for windows, unix or mac.
File must be encoded UTF8.

The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.

LOAD [schema.]table_name[@db_link] file_name

Unfortunately back then Google didn’t offer me this insight when looking for “load csv data into oracle” and so I was stuck with the methods I already knew and that I found as too cumbersome to deal with. After all, all I wanted was a simple tool that loaded data from CSV files into a table and match the column names with the header of the file:

cat people.csv

"first_name","last_name","city"
"Gerald","Venzl","San Francisco"
"Andreas","Pumberger","New York City"

SELECT * FROM PEOPLE

FIRST_NAME  LAST_NAME  CITY
----------  ---------  -------------
Gerald      Venzl      San Francisco
Andreas     Pumberger  New York City

Continue reading “csv2db – the little CSV command line loader you always wanted” →