CSV Import throws invalid input syntax for integer »2«

Question

Solved: The integer column was set on auto increment, just left it empty on the import...
we have a lot of data, that is currently in excel. I made a VBA skript, that builds me a CSV to import into our Database / PostgreSQL Table. I'm trying to import with the import/export feature of PgAdmin.
The table has columns of type ([PK] Integer, string, string, JSON). When I try to import it throws me an error, right at the beginning, saying that »2« is no valid integer. The file is UTF-8 encoded.
This is the command PgAdmin generates:

--command " "\copy public.stocknew (stockid, stockname, stockbarcode, stockjson) FROM '//DESKTOP-G86U473/temp/Test.csv' DELIMITER ',' CSV ENCODING 'UTF8' QUOTE '"' ESCAPE '''';""

Not a regular question asker, so please comment, if anything needs clarification.
Here is the first entry of the CSV file.

2,"W12345","35","{
'"Manufacturer'":'"ExampleValue'",
'"Supplier'":'"ExampleValue'",
'"SupplierName'":'"ExampleValue'",
'"Category'":'"ExampleValue'",
'"SubCategory'":'"ExampleValue'",
'"Partvalue'":'"868MHz - 928MHz, 2.400MHz - 2.500MHz'",
'"Tolerance'":'"ExampleValue'",
'"Dimension'":'"10,4 x 49,6mm'",
'"Temperature'":'"-34°C + 76°C'",
...*This keeps going for a while*...
'"Example'":2,
'"Example'":3,
'"Example'":4
}"

Welcome welcome. Did you try to export the Excel file as CSV (from Save As) and using PostgreSQL's COPY to load the data? Errors might not always be accurate; they could just be in the general direction you should be looking at. Here, my guess would be that, the line isn't formatted correctly. Nor are escape characters. For example, I assume " is set as the encapsulating character. But, the JSON data doesn't escape " that are part of the data. In addition, I wouldn't use newline within a field value. JSON can load well without newlines. — edd
– edd, Commented Aug 6, 2021 at 12:36
@edd Thank you, thank you. I sadly can't export the file straight out of excel, the json information is spread around the sheet and I am collecting it with my script. Weirdly enough I have another method that actually creates an csv that works for import. Problem is, even though it specifies UTF-8 it doesn't show characters like Ä Ü Ö °. Otherwise it looks excactly the same. For example, the CSV looks like this for Temperature: ....'"Temperature'":'"-34?C + 76?C'".... But I still took your advice and kicked the line breaks in the JSON. Sadly didn't fix. — ds600
– ds600, Commented Aug 6, 2021 at 12:55

edd · Accepted Answer · 2021-08-06 13:14:21Z

0

The following data successfully loads

"2","W12345","35","{\"Manufacturer\":\"ExampleValue\",\"Supplier\":\"ExampleValue\",\"SupplierName\":\"ExampleValue\",\"Category\":\"ExampleValue\",\"SubCategory\":\"ExampleValue\",\"Partvalue\":\"868MHz - 928MHz, 2.400MHz - 2.500MHz\",\"Tolerance\":\"ExampleValue\",\"Dimension\":\"10,4 x 49,6mm\",\"Temperature\":\"-34°C + 76°C\"}"

postgres=# create table pew (i1 int, s1 varchar(30), s2 varchar(30), j1 jsonb);

postgres=# copy pew from '/tmp/somedata.csv' with (format CSV, quote '"', escape '\');
COPY 1

postgres=# select * from pew;
 i1 |   s1   | s2 |                                                                                                                 
                                  j1                                                                                                
                                                   
----+--------+----+-----------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
  2 | W12345 | 35 | {"Category": "ExampleValue", "Supplier": "ExampleValue", "Dimension": "10,4 x 49,6mm", "Partvalue": "868MHz - 92
8MHz, 2.400MHz - 2.500MHz", "Tolerance": "ExampleValue", "SubCategory": "ExampleValue", "Temperature": "-34°C + 76°C", "Manufacturer
": "ExampleValue", "SupplierName": "ExampleValue"}
(1 row)

postgres=# select version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

Looks like extraneous ' and missing escaping ".

Pardon my ugly SQL

answered Aug 6, 2021 at 13:14

edd

1,43712 silver badges13 bronze badges

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

2 Comments

ds600 Over a year ago

The ' was my escaping symbol. I changed everything up to match your sql and file format. I couldn't quite get it to work, but you gave me a spark of hope and I'll get right back at it next week. I'll give you a checkmark as soon as I fix my stuff up.

ds600 Over a year ago

So, turns out I am dumb and the integer column was set on autoincrement. Just had to remove the column from my import / change the column...

Collectives™ on Stack Overflow

CSV Import throws invalid input syntax for integer »2«

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related