OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

pasting data from calc: decimal numbers altered

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94

PostPosted: Thu May 24, 2012 4:29 am    Post subject: pasting data from calc: decimal numbers altered Reply with quote

i work with informix database with base as frontend
i've installed a driver and make it work somehow: now i can access data through base and write sql but not everything is working fine.

the most annoying problem is:
i want to populate a table i created in informix pasting data from a calc spreadsheet: everything working fine with varchar values, but with decimal values is a disaster. sometimes informix magically multiply values per 10, sometimes can't recognize digits after the comma...
how can i do it?
is it possible to paste numbers as strings (which works fine) then convert them as decimal with a command like alter table cast(something... ???

help please
_________________
Libreoffice Base HSQLDB not Embedded
Back to top
View user's profile Send private message Send e-mail
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 24, 2012 6:33 am    Post subject: Reply with quote

Normally the desaster comes from the spreadsheet side of the problem when all types of data occur in the same column.

You can compare =ROWS(A$2:A$999) with =COUNT(A$2:A$999)+COUNTBLANK(A$2:A$999). Quite often this reveals some text (an emtpy string is a string too).

To import everything as text simply import into a VARCHAR column.
Array function =MAX(LEN(A$2:A$999)) reveals the required char length.

In HSQLDB CAST("TextImport" AS DECIMAL(9,2)) converts the string in field "TextImport".
CAST(Replace("TextImport", ',', '.') AS DECIMAL(9,2)) does the same while replacing any decimal comma with decimal point.
But this should be documented for your particular database driver.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94

PostPosted: Thu May 24, 2012 8:19 am    Post subject: Reply with quote

i think i brillantly solved that!
given the table where i wanted to paste the decimal values into, i created a mirror table that is exactly the same except for the fact that i declared varchar values instead of decimal.
then i pasted data from the calc spreadsheet into the mirror table.
then i did that:
insert into table (field1,field2,...)
select filed1,field2,...
from mirror_table
now i've my table with the decimal numbers exactly the same i've in the calc spreadsheet.
proud of myself!
_________________
Libreoffice Base HSQLDB not Embedded
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group