Re: 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