CSV Files and Postgres (and Ruby)

C

Clark Jefcoat

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
 
N

NAKAMURA, Hiroshi

Hi,
From: "Clark Jefcoat" <[email protected]>
Sent: Friday, October 03, 2003 11:28 AM
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.

ruby -rcsv -e '
CSV.generate("foo.tsv", ?\t) do |f|
CSV.parse("foo.csv", ?,) do |row| # ?, is just for symmetry.
f.add_row(row)
end
end
'

should work.

Regards,
// NaHi
 
C

Charles Hixson

Hi,


...

But 5 lines long. :)
I haven't tested, but try this translation:

ruby -rcsv -e '
CSV.generate("foo.tsv", ?\t) { |f| CSV.parse("foo.csv", ?,) { |row| f.add_row(row) }}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top