Re: import data from openoffice Calc

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

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