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

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

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

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

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

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