Thread: Import CSV date issues

Hello everyone,

I am just setting up a db using PostgreSQL-9.1 and having issues with
importing from a CSV file. I have created a table "durations" in
"mydb" using the following SQL command,

CREATE TABLE durations ( startdate date, enddate date, starttime time
without time zone, endtime time without time zone, days integer, hours
integer, minutes integer, somefield text, locid integer  );

mydb has the proper date style set as well as shown with this,

mydb=> SHOW datestyle;
 DateStyle
-----------
 ISO, DMY
(1 row)

However, when I try to import data into "durations" table from a CSV
file, I get this error which is puzzling me because the date in the
CSV file is in the same DMY format that the db should be expecting. I
have also tried by giving "durations(startdate, enddate, ....)" format
as well but same result.

mydb=> \copy durations FROM 'myfile.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type date: "12/09/2011"
CONTEXT:  COPY durations, line 1, column startdate: "12/09/2011"

The lines in the CSV file look like the following,

12/09/2011,12/09/2011,11:12,12:12,0,1,60,NO,82889

Any suggestions please? Thank you for reading through :)

Sarfraz Nawaz <sarfraz@gmail.com> writes:
> However, when I try to import data into "durations" table from a CSV
> file, I get this error which is puzzling me because the date in the
> CSV file is in the same DMY format that the db should be expecting. I
> have also tried by giving "durations(startdate, enddate, ....)" format
> as well but same result.

> mydb=> \copy durations FROM 'myfile.csv' DELIMITER ',' CSV;
> ERROR:  invalid input syntax for type date: "12/09/2011"
> CONTEXT:  COPY durations, line 1, column startdate: "12/09/2011"

That certainly *looks* right, and it does work in testing here.
However, since this is the first column of the first line of the
file, my bet is that you've got some invisible characters at the
start of the file that are screwing up COPY.  Text editors that
think they should put UTF8 "BOM" marks into text files are a
frequent cause of this on Windows.

            regards, tom lane

Tom you are a legend! There were invisible characters at the start of
the file which were not being shown with "head" and "cat". Removing
them fixed the issue and copy worked like a charm. Thank you!

On Sun, Mar 3, 2013 at 3:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sarfraz Nawaz <sarfraz@gmail.com> writes:
>> However, when I try to import data into "durations" table from a CSV
>> file, I get this error which is puzzling me because the date in the
>> CSV file is in the same DMY format that the db should be expecting. I
>> have also tried by giving "durations(startdate, enddate, ....)" format
>> as well but same result.
>
>> mydb=> \copy durations FROM 'myfile.csv' DELIMITER ',' CSV;
>> ERROR:  invalid input syntax for type date: "12/09/2011"
>> CONTEXT:  COPY durations, line 1, column startdate: "12/09/2011"
>
> That certainly *looks* right, and it does work in testing here.
> However, since this is the first column of the first line of the
> file, my bet is that you've got some invisible characters at the
> start of the file that are screwing up COPY.  Text editors that
> think they should put UTF8 "BOM" marks into text files are a
> frequent cause of this on Windows.
>
>                         regards, tom lane

On Sun, Mar 3, 2013 at 2:48 PM, Sarfraz Nawaz <sarfraz@gmail.com> wrote:
> Tom you are a legend! There were invisible characters at the start of
> the file which were not being shown with "head" and "cat". Removing
> them fixed the issue and copy worked like a charm. Thank you!

yup -- many utilities (especially on windows) are aware of BOM and
will suppress it.  hexdump is a good way to catch it out.  Ideally you
can disable BOM creation on whatever is producing your csv.

merlin