Search code examples
postgresqlcsvgtfspgadmin-4

Importing csv file into postgres db using pgadmin with special characters

I'm importing data from csv file into postgres db using pgadmin 4 Everything is ok but I get an issue when I try to import a file which contain some data like this

“‘t Zand, Vlotbrug”

“Dussen, `t Middeltje”

as you can see, the data contains

`

and

'

I also tried to import the file with utf-8 encoding but could not.

Anyone knows how to solve this issue?

Updated

Structure:

stop_id,stop_code,stop_name,stop_lat,stop_lon,location_type,parent_station,stop_timezone,wheelchair_boarding,platform_code,zone_id

Data:

stoparea:123953,,"De Zande, 'Koelucht'",52.5184475,5.956368,1,,,0,,

Error:

ERROR:  unterminated CSV quoted field
CONTEXT:  COPY stops, line 69400: "stoparea:123953,,"De Zande, 'Koelucht'",52.5184475,5.956368,1,,,0,,
stoparea:120536,,"Poortvliet, Zu..."

Updated 2

Command:

"/Applications/pgAdmin 4.app/Contents/SharedSupport/psql" --command " "\copy transit.stops (stop_id, stop_code, stop_name, stop_lat, stop_lon, location_type, parent_station, stop_timezone, wheelchair_boarding, platform_code, zone_id) FROM '/Users/tvtan/Desktop/gtfs-nl/stops.txt' DELIMITER ',' CSV HEADER QUOTE '\"' ESCAPE '''';""

UI:

enter image description here

Solution

  • From the command line it looks like you have defined the ESCAPE character as a single quote. The single quote appears in your data but is not escaped.

    The default ESCAPE character is the same as the QUOTE character.

    More information here