Why am I getting "Invalid input syntax for type integer" in postgresql when importing a CSV?

Question

I'm trying to import a .csv file to my postgresql DB. I created a table as follows:

CREATE TABLE accounts
(
    acc_id integer,
    acc_name text,
    website text,
    lat numeric,
    longe numeric,
    primary_poc text,
    sales_rep_id integer
)

Then I used the following command to import the .csv file

COPY accounts(acc_id,acc_name,website,lat,longe,primary_poc,sales_rep_id) 
FROM 'D:\accounts.csv' DELIMITER ';' CSV ;

And my .csv file contains the following:

1;Walmart;www.walmart.com;40.23849561;-75.10329704;Tamara Tuma;321500
2;Exxon Mobil;www.exxonmobil.com;41.16915630;-73.84937379;Sung Shields;321510
3;Apple;www.apple.com;42.29049481;-76.08400942;Jodee Lupo;321520

However, this doesn't work and the following message appear:

ERROR:  invalid input syntax for type integer: "1"
CONTEXT:  COPY accounts, line 1, column acc_id: "1"
SQL state: 22P02

Try: \copy accounts from 'D:\accounts.csv' DELIMITER ';' CSV; — Luuk
– Luuk, Commented Feb 16, 2020 at 15:47
Do you have a header? If so, try adding "HEADER" ...FROM 'D:/accounts/csv' DELIMITER ';' CSV HEADER; — LydiaHendriks
– LydiaHendriks, Commented Feb 16, 2020 at 16:06
just tested, it does (on Windows), indeed a feature, so do powershell "format-hex accounts.csv" to chheck for the BOM, If it starts with a BOM you will see EF BB BF as first 3 bytes. — Luuk
– Luuk, Commented Feb 16, 2020 at 16:38
Never trust Excel spreadsheets, always load to a staging table where they can be validated and corrected before loading to production tables. You think they're safe. Think again. — Belayer
– Belayer, Commented Feb 16, 2020 at 17:17

wildplasser · Accepted Answer · 2020-02-16 16:33:38Z

3

Maybe there is a BOM in the CSV?

hexdump the file, and inspect the first three characters
(and) use an editor to remove the BOM
(or) export again, without the BOM (there should be a checkmark, even in the Microsoft "software")

answered Feb 16, 2020 at 16:33

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why am I getting "Invalid input syntax for type integer" in postgresql when importing a CSV?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related