Обсуждение: 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 :)
My experience is that the best date format is year-month-day YYYY-MM-DD
Otherwise you have to set the "set date " in sql
From: Sarfraz Nawaz <sarfraz@gmail.com>
To: pgsql-novice@postgresql.org
Sent: Sunday, March 3, 2013 7:13 AM
Subject: [NOVICE] 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 :)
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Otherwise you have to set the "set date " in sql
From: Sarfraz Nawaz <sarfraz@gmail.com>
To: pgsql-novice@postgresql.org
Sent: Sunday, March 3, 2013 7:13 AM
Subject: [NOVICE] 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 :)
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
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