0

I have exported files of the same format of varying sizes from a database > Excel > save to CSV, and attempted to import them into my database using PgAdmin 4.

The tables are all created, no problem. The files under 150,000 rows or so, no problem importing them.

When I get to my files of ~600,000 rows, I get

ERROR: unterminated CSV quoted field CONTEXT: COPY TABLE, line 661017: "07,Contra,Jones,P,X County,70174770,GA000..."

The values are from all over the table, but "line 661017" is always the last row of data in the spreadsheet, in this case a file with 661017 active rows, including the header, which is toggled on for import.

I'm tried re-exporting these files from my original source, cutting them into smaller files, and the one without the last row values uploads no problem, but whatever trimmed down file houses the last row outputs the same error.

Any help is appreciated!

Lily_Star
  • 3
  • 1
  • 2
  • Excel is known to produce bad CSV. Somewhere in your data is a double quote that is never closed. Perhaps you should use the `QUOTE` option to specify a different quoting character. – Laurenz Albe Sep 03 '20 at 08:04
  • Thanks! Since I use the drop down Import/Export table option to specify delimiter, can you suggest the code that I should enter into query builder instead when importing with a different quoting character? – Lily_Star Sep 03 '20 at 16:14
  • I don't use pgAdmin, but I am sure it allows you to specify the quoting character. – Laurenz Albe Sep 03 '20 at 19:04

1 Answers1

0

Line number in this error is misleading, it just means that an error happened somewhere. There is a double-quote or in some cases single-quote (depending on the data) somewhere in your file that is never closed. Tracking down all the instances where this might be happening is time consuming.

So as a workaround,

  • 'Save as' your excel file as a text file. Conversion wraps string values with double-quotes in the text file so using find/replace option in your editor, replace the double-quotes with an empty string. This will also remove any unwanted double-quotes from the string values.
  • In PgAdmin, right-click the table and select import. From the import dialog, select format as 'text' and delimiter as 'tab'. If using older version of Excel, also select the encoding as 'WIN1251'.

Clicking 'Ok' on the dialog should start the import process.

Pushpak
  • 301
  • 3
  • 6