pasting data from calc: decimal numbers altered

[Philip Marlowe]

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

[Villeroy]

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 http://forum.openoffice.org

[Philip Marlowe]

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