load a csv file into postgresql
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
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.
# 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
# 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
# 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
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 08:50 PM.|