LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)
-   -   load a csv file into postgresql (http://www.linuxquestions.org/questions/linux-server-73/load-a-csv-file-into-postgresql-542891/)

sailu_mvn 04-03-2007 03:55 AM

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

benjithegreat98 04-04-2007 01:59 PM

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

michaelk 04-04-2007 02:27 PM

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

kj_synack 04-08-2007 10:30 PM

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");

sailu_mvn 04-16-2007 10:28 PM

Thank you,thats a great one.
What happens if the fields have a empty string
suppose the values are alex,,,tuned
see ,,,.

roybal 03-26-2010 02:56 PM

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;"


All times are GMT -5. The time now is 12:54 PM.