LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 04-03-2007, 03:55 AM   #1
sailu_mvn
Member
 
Registered: Sep 2004
Location: Hyderabad
Distribution: FEDORA,REDHAT,HOST
Posts: 440
Blog Entries: 1

Rep: Reputation: 30
load a csv file into postgresql


Hi,
I have a 1000 records to be loaded into postgres database.
I know COPY command works for this purpose.Listen to me what the problem is.
I have a table with 19 colums, i have to only insert values in 9 columns using CSV file.How to do it. Pls assist
 
Old 04-04-2007, 01:59 PM   #2
benjithegreat98
Senior Member
 
Registered: Dec 2003
Location: Shelbyville, TN, USA
Distribution: Fedora Core, CentOS
Posts: 1,019

Rep: Reputation: 45
http://codewalkers.com/seecode/585.html

This is a php download that can convert your csv file into sql insert statements. Then you can get that into the database. I'd create a 2nd csv file that will have only the 9 needed columns and copy and paste that into this.

I've never used it so I can't vouch for it. I found it by searching google for convert csv to sql
 
Old 04-04-2007, 02:27 PM   #3
michaelk
Moderator
 
Registered: Aug 2002
Posts: 11,753

Rep: Reputation: 719Reputation: 719Reputation: 719Reputation: 719Reputation: 719Reputation: 719Reputation: 719
The painful method would be to use a spreadsheet application to select the desired fields and then save it to another csv file. Then use the Postgresql copy method.

A quick google did not find any good scripts that would select certain fields.

You can use OpenOffice if installed as described below.
http://grass.gdf-hannover.de/wiki/Op..._SQL_Databases

Last edited by michaelk; 04-04-2007 at 02:28 PM.
 
Old 04-08-2007, 10:30 PM   #4
kj_synack
Newbie
 
Registered: Apr 2007
Posts: 3

Rep: Reputation: 0
Quote:
Originally Posted by michaelk
The painful method would be to use a spreadsheet application to select the desired fields and then save it to another csv file. Then use the Postgresql copy method.

A quick google did not find any good scripts that would select certain fields.

You can use OpenOffice if installed as described below.
why not cut out the required fields from CSV file and use sed to reformat the file as per your requirements?

# cut out fields delimited with ","
synack@deimos ~ $ cut -f1,3,6,9 -d"," source_file.csv >> new_source_file.csv
synack@deimos ~ $ cat new_source_file.csv
10,linux,bash,magic
20,windows,promt,bsd

# getting the source file formatted with double quotes
# maybe you don't need this?
synack@deimos ~ $ cat new_source_file.csv | sed -e 's/^/"/g; s/,/","/g; s/$/"/g' >> selected_values.csv
synack@deimos ~ $ cat selected_values.csv
"10","linux","bash","magic"
"20","windows","promt","bsd"

# prepare a sql file
synack@deimos ~ $ cat selected_values.csv | sed -e 's/^/insert into table_name (field1, field3, filed10) values (/g; s/$/);/g' >> sql_file.sql
synack@deimos ~ $ cat sql_file.sql
insert into table_name (field1, field3, filed10) values ("10","linux","bash","magic");
insert into table_name (field1, field3, filed10) values ("20","windows","promt","bsd");
 
Old 04-16-2007, 10:28 PM   #5
sailu_mvn
Member
 
Registered: Sep 2004
Location: Hyderabad
Distribution: FEDORA,REDHAT,HOST
Posts: 440
Blog Entries: 1

Original Poster
Rep: Reputation: 30
Thank you,thats a great one.
What happens if the fields have a empty string
suppose the values are alex,,,tuned
see ,,,.
 
Old 03-26-2010, 02:56 PM   #6
roybal
LQ Newbie
 
Registered: Sep 2009
Posts: 8
Blog Entries: 8

Rep: Reputation: 0
A lot late, but a better answer for anyone still looking...

cat file.csv | psql -c "COPY schema.table(field1,field3,field7) FROM STDIN WITH CSV HEADER;"
 
  


Reply

Tags
bash, postgresql, sed


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
help extracting data from csv file willinusf Linux - General 10 10-27-2006 09:10 PM
Removing quotes from a CSV file with Perl Grafbak Programming 5 07-25-2006 03:23 AM
Shell script to read from csv file hendemeg Programming 1 05-11-2004 08:23 PM
csv to fixed-length file roballen Programming 0 03-11-2004 03:12 AM
CSV File AMMullan Programming 2 11-10-2003 12:49 AM


All times are GMT -5. The time now is 08:14 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration