CODEWITHSUNDEEP
sqlpostgresqlcsvpgadmin
Betafish
Betafish

Reputation: 1262

ERROR: extra data after last expected column : PGADMIN

I'm trying to import a CSV (Cities.csv) onto my Postgres using pg-admin. I understand this is a common problem, found few answers on SO. But, how do we address this through pg-admin?.

enter image description here

Error

Upvotes: 0

Views: 739

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

I wouldn't rely on a tool like pgAdmin to import data. It indeed provides a nice user interface and is quite intuitive, but psql is imho still by far the most flexible choice.

Note: Your CSV file has an empty line in the end, which is probably causing the error. One option to filter it out is to use sed (see example below). In that way the COPY command already gets the records clean and ready to be imported.

$ sed -e /^$/d cities.csv | psql -d mydb -c "COPY ei_test FROM STDIN WITH CSV HEADER;"

And your data is ready to use ..

SELECT * FROM ei_test LIMIT 10;

 latd | latm | lats | ns | lond | lonm | lons | ew |       city       | state 
------+------+------+----+------+------+------+----+------------------+-------
   41 |    5 |   59 |  N |   80 |   39 |    0 |  W |  Youngstown      |  OH
   42 |   52 |   48 |  N |   97 |   23 |   23 |  W |  Yankton         |  SD
   46 |   35 |   59 |  N |  120 |   30 |   36 |  W |  Yakima          |  WA
   42 |   16 |   12 |  N |   71 |   48 |    0 |  W |  Worcester       |  MA
   43 |   37 |   48 |  N |   89 |   46 |   11 |  W |  Wisconsin Dells |  WI
   36 |    5 |   59 |  N |   80 |   15 |    0 |  W |  Winston-Salem   |  NC
   49 |   52 |   48 |  N |   97 |    9 |    0 |  W |  Winnipeg        |  MB
   39 |   11 |   23 |  N |   78 |    9 |   36 |  W |  Winchester      |  VA
   34 |   14 |   24 |  N |   77 |   55 |   11 |  W |  Wilmington      |  NC
   39 |   45 |    0 |  N |   75 |   33 |    0 |  W |  Wilmington      |  DE
(10 Zeilen)

Upvotes: 1

Related Questions