Re: import data from openoffice Calc

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(at)comcast(dot)net