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

----- "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
thefollowing. If I preformatted a column as "Text" and then entered date strings for example "12/01/09" it stayed a
stringwhen 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
newdate strings entered however would stay as "12/01/09". Hope this helps. 


Adrian Klaver
aklaver@comcast.net

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