From: Clark Jefcoat <cjefcoat@...>
Date: 2003-10-03T11:28:43+09:00
Subject: CSV Files and Postgres (and Ruby)

Hi,

I had some CSV files that I needed to load into a postgres 
database.

I looked on the web and could not find a straightforward 
way to load CSV files into postgres.

The following lines of ruby will take the name of a CSV file as 
input and will output tab delimited lines to stdout:

--

require 'csv'

CSV.open(ARGV[0], "r") do |row|
  puts row.to_a.join("\t")
end

--

Run the script above on your csv file and save the output 
into a file.  From within psql, create the table that you 
will be inserting into. The resulting tab delimited file 
can be loaded by using the following from within psql:

--

copy tablename from 'path to tab file'
     with null as '';

--

Things to be aware of:  (1) you won't get the result you want 
if the original CSV file contains tab characters,  (2) if you 
don't want the blank rows to insert nulls into the table don't 
use the "with null as ''" in the psql command.

I found a perl script to do the CSV to tab conversion,
but just wanted to put a ruby version online (even if it is
only 3 lines long).  To run the script I used ruby 1.8 which
includes the csv library.

Clark