1

For some reason Postgresql wont read my CSV files in the form:

2017-10-20T21:20:00,124.502,CAM[CR][LF]
2017-10-20T21:21:00,124.765,CAM[CR][LF]

(thats an ISO compliant timestamp right?) into a table defined as:

    CREATE TABLE ext_bsrn.spa_temp (
      spadate TIMESTAMP WITHOUT TIME ZONE,
      spa_azimuth NUMERIC,
      station_id CHAR(3)  ) 
    WITH (oids = false);

It returns this error:

ERROR:  invalid input syntax for type timestamp: "?2015-01-01T00:00:00"
CONTEXT:  COPY spa_temp, line 1, column spadate: "?2015-01-01T00:00:00"

I don't understand why the '?' is shown inside the quotes in the error message, there's no characters before 2015 in my file (checked it in Notepad++ with noprint characters shown.)

I tried both windows (CRLF) and unix (LF) line ends, but neither makes any difference.

I also tried seperate date & time columns but then it just throws a similar error re the date field. "invalid input syntax for type date"

Does line 1 mean the first line or the second line (if there is a Line 0)?

Improve this question
3
  • 1
    It looks like it might be a UTF8 BOM, which won't be shown in Notepad++. You can remove it via the "Encoding" menu. Commented Jan 16, 2017 at 11:48
  • Ah... Yes, I had look using the Notepad++ hex editor plugin and sure enough it has  at the start of the file. So Postgresql doesn't like the BOM? I'll go back to the Delphi program which writes the CSV and change it to a non BOM format. Thanks! Commented Jan 16, 2017 at 12:06
  • Yes, changed the program writing the CSV files so the hidden  BOM character is no longer written at the beginning of the file and it is now importing into Postgresql fine with the COPY command Commented Jan 16, 2017 at 14:28
Related questions
14
Ignore quotation marks when importing a CSV file into PostgreSQL?
1
Import csv file into psql db
4
PostgreSQL - CSV file import error
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.