February 10, 2019 at 11:31 am
#16817
Keymaster
While Importing CSV files with text fields that contain double quotes using postgres COPY .
By following steps you can neglect the double quotes at the time of using postgres COPY restoration
1. connecting windows server
D:
cd D:\PostgresPlus\9.4AS\bin
psql -U enterprisedb -d Health_DC
2. Sample Data’s :
"city_name"|"pincode"|"taluka_name"|"district_name"|"state_name"
"Arong"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
"Sawai"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
"Chuckchucha"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
"Kinyuka"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
"Big Lapati"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
"Perka"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
3. following copy command will neglect the double quote
2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\InCampusDB/city_master_data.csv' DELIMITER '|' CSV HEADER;
ERROR: character with byte sequence 0x9d in encoding "WIN1252" has no equivalent in encoding "UTF8"
CONTEXT: COPY citytemp, line 358646
You need to change the character set on your windows command line before running the script with chcp. Or in postgresql you can:
SET CLIENT_ENCODING TO 'utf8';
Before importing the file.
2ndquadrant.in=# SET CLIENT_ENCODING TO 'utf8';
SET
2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\InCampusDB/city_master_data.csv' DELIMITER '|' CSV HEADER;
COPY 679249