import data from openoffice Calc

On Tuesday 01 December 2009 7:21:45 am Le-shin Wu wrote:

Hi,

I use OpenOffice Base as the front end tool to connect a postgreSQL server.
It works great. But when I am trying to create a table by copying data from
OpenOffice Calc and then pasting to my postgreSQL database (connected
through OpenOffice base), I always got an invalid input syntax error for
type "date". My original data showing in Clac is "12/17/99", but when
OpenOffice base tries to insert this data into a table, it became "36509".
The actual error is as below, can anyone help me to fix this problem.
Thanks a lot.

pq_driver:[PGRES_FATAL_ERROR]ERROR: invalid input syntx for type date:
"36509"
(caused by statement 'INSERT INTO "public"."DF"
("STK_NO","Date","Comments") VALUES ('11','36509','small inversion'))

LW

The problem is that dates in spreadsheets are stored as days from some date. For
a more complete answer see:
http://www.lexicon.net/sjmachin/xlrd.html

On the above page is a link to the OO documentation for spreadsheets. The trick
is to copy the formatted date not the underlying value. I know I have done that
in the past but at this point in time I cannot remember how. You might want to
Google OO base spreadsheet date conversion or something similar.

--
Adrian Klaver
aklaver@comcast.net

On Tuesday 01 December 2009 7:40:26 am Adrian Klaver wrote:

On Tuesday 01 December 2009 7:21:45 am Le-shin Wu wrote:

Hi,

I use OpenOffice Base as the front end tool to connect a postgreSQL
server. It works great. But when I am trying to create a table by copying
data from OpenOffice Calc and then pasting to my postgreSQL database
(connected through OpenOffice base), I always got an invalid input syntax
error for type "date". My original data showing in Clac is "12/17/99",
but when OpenOffice base tries to insert this data into a table, it
became "36509". The actual error is as below, can anyone help me to fix
this problem. Thanks a lot.

pq_driver:[PGRES_FATAL_ERROR]ERROR: invalid input syntx for type date:
"36509"
(caused by statement 'INSERT INTO "public"."DF"
("STK_NO","Date","Comments") VALUES ('11','36509','small inversion'))

LW

The problem is that dates in spreadsheets are stored as days from some
date. For a more complete answer see:
http://www.lexicon.net/sjmachin/xlrd.html

On the above page is a link to the OO documentation for spreadsheets. The
trick is to copy the formatted date not the underlying value. I know I have
done that in the past but at this point in time I cannot remember how. You
might want to Google OO base spreadsheet date conversion or something
similar.

I remember now. I exported the data as a csv file and then loaded into Postgres.
The export converts the dates to strings representing their formatted values
not the underling integer.

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:

The problem is that dates in spreadsheets are stored as days from some
date. For a more complete answer see:
http://www.lexicon.net/sjmachin/xlrd.html

Somebody should file a bug/RFE against OpenOffice, suggesting that there
ought to be an option to store dates as dates when storing into SQL
databases.

regards, tom lane

Hi Adrian,

Thanks for your information. I think your suggestion will be my last
approach, if I really can not find a way to solve my problem. Actually, I
tried to format the cells type (the "date" column in my sheet) in Calc as
"Date" before I copy and past, but somehow it works for only once. This also
confused me. Thanks again.

LW

On Tue, Dec 1, 2009 at 11:04 AM, Adrian Klaver <aklaver@comcast.net> wrote:

----- "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Adrian Klaver <aklaver@comcast.net> writes:

The problem is that dates in spreadsheets are stored as days from

some

date. For a more complete answer see:
http://www.lexicon.net/sjmachin/xlrd.html

Somebody should file a bug/RFE against OpenOffice, suggesting that
there
ought to be an option to store dates as dates when storing into SQL
databases.

regards, tom lane

Someone beat me to it:
http://www.mail-archive.com/debian-openoffice@lists.debian.org/msg22449.html

Adrian Klaver
aklaver@comcast.net

----- "Le-shin Wu" <leshin@gmail.com> wrote:

Hi Adrian,

Thanks for your information. I think your suggestion will be my last
approach, if I really can not find a way to solve my problem.
Actually, I tried to format the cells type (the "date" column in my
sheet) in Calc as "Date" before I copy and past, but somehow it works
for only once. This also confused me. Thanks again.

LW

Once the column is formatted as "Date" the underlying value will be the integer value. I did a little test and found the following. If I preformatted a column as "Text" and then entered date strings for example "12/01/09" it stayed a string when cut and pasted. If I just started entering a date string in a column the column would become a "Date" column. Changing the formatting to "Text" would change the date strings "12/01/09" to the underlying integer 40147. Any new date strings entered however would stay as "12/01/09". Hope this helps.

Adrian Klaver
aklaver@comcast.net

HI Adrian,

Thanks for pointing out the reporting bug information about my problem. I
have tried your suggestion and it works great. But this approach can only
solve one part of my problem, because I also need to open an existing db
table through Clac, edit some tuples, and then save it as a new table. For
this case, I can not convert my original "date" data to text (because it
will become an integer after converting as you mentioned). Thanks again.

LW

On Tue, Dec 1, 2009 at 6:38 PM, Adrian Klaver <aklaver@comcast.net> wrote:

----- "Le-shin Wu" <leshin@gmail.com> wrote:

HI Adrian,

Thanks for pointing out the reporting bug information about my
problem. I have tried your suggestion and it works great. But this
approach can only solve one part of my problem, because I also need to
open an existing db table through Clac, edit some tuples, and then
save it as a new table. For this case, I can not convert my original
"date" data to text (because it will become an integer after
converting as you mentioned). Thanks again.

LW

Might be easier to use the CREATE TABLE AS command:
http://www.postgresql.org/docs/8.4/interactive/sql-createtableas.html

Adrian Klaver
aklaver@comcast.net