Author Topic: [SOLVED] Loading data from a csv file  (Read 1924 times)

0 Members and 1 Guest are viewing this topic.

Offline Jago6060Topic starter

  • Enthusiast
  • Posts: 115
    • View Profile
[SOLVED] Loading data from a csv file
« on: September 18, 2009, 01:08:35 PM »
Hi all,

I've spent some time googling trying to find a tut to show me how to load data into a postgresql database but haven't found anything that great.  Can anyone point me in the direction of a good one?

Offline artacus

  • Devotee
  • Posts: 915
  • Gender: Male
  • World domination? Never heard of it.
    • View Profile
    • Flex and Specs()
artacus

-- Trust your leaders, depend on your government, don't question the media... and this won't hurt a bit. --

Offline Jago6060Topic starter

  • Enthusiast
  • Posts: 115
    • View Profile
Re: Loading data from a csv file
« Reply #2 on: September 23, 2009, 08:59:01 AM »
http://www.postgresql.org/docs/8.3/static/sql-copy.html

Ok Just one quick question...the table that I'm importing a CSV into has some 20+ columns/fields but I need to copy csvfile.column1 into table.column18.  Via your provided link, I can't see a way to delineate what columns of the CSV file go into what columns of the table.

Offline artacus

  • Devotee
  • Posts: 915
  • Gender: Male
  • World domination? Never heard of it.
    • View Profile
    • Flex and Specs()
Re: Loading data from a csv file
« Reply #3 on: September 23, 2009, 02:14:23 PM »
No, you won't be able to do that with copy.

Is this a one time thing or is it something you need to process on a continual basis?

If it's a one time thing you can copy/reorder the columns in Excel. You have several options for continual process.
One would be to copy to a staging table and then transform into your regular table. There is pgloader. It will allow you to do transforms before outputting to copy.
http://www.postgresql.org/docs/8.3/static/pgcrypto.html

Another option would be to use an ETL tool. There are loads of them out there.

artacus

-- Trust your leaders, depend on your government, don't question the media... and this won't hurt a bit. --