Ask Your Question

Problem importing decimal data from CALC sheet to Firebird Base table

asked 2019-03-06 12:06:03 +0200

frofa gravatar image

updated 2019-03-06 12:12:52 +0200

I have been able to setup a LO v6.2 embedded Firebird database with a single table having an ID primary key plus a couple of other DECIMAL(X,2) columns for currency data. I used an SQL statement run under the Tools>SQL menu to do that job.

The input data-source is a CALC sheet with columns formatted as NUMERIC XXXX.XX type destined for the DECIMAL columns in BASE. My import steps are: 1. Select all the CALC file populated cells 2. Click-select the BASE/FB table in the table list as the destination for the import. 3. Do the paste.

RESULT: All the data are imported properly except for the two DECIMAL columns in which the numerical data now displays with the decimal point shifted two spaces to the left - i.e. a CALC cell has 12.65 and the BASE/FB table corresponding value appears as .12 (without the least-significant digits). Is there anything I can do to correct this problem?

However, I suspect this might be a problem with the FB importing mechanism because doing the same procedure into a SPLIT HSQLDB Base setup works perfectly.

Also, I wonder is this related to the problem as described in the 'Firebird Migration: Number [ NUMERIC ] Field Data Corruption' post HERE?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-03-06 19:07:14 +0200

Ratslinger gravatar image


Yes this is related and reported in tdf #116893, specifically in comment #12 -> Calc numeric shifting. Using a type of 'DOUBLE' as mentioned does seem to work.

tdf #123591 may also be related to the problem.

Also be aware of possible incorrect conversion of numeric and decimal fields. See -> tdf #118043. See attached screenshot there.

There are a number of different issues regarding NUMERIC and DECIMAL types in Firebird (all seem to be conversion related). There was mention of a correction to fix the conversion problem in v6.2.0.x but that seems to have gone by the wayside.

As mentioned in a previous answer, have typically found that importing as text field & then copying to correct field type has been the safest approach although have used the 'DOUBLE' type on occasion. Just be sure to verify column totals on numeric and decimal input vs output.

I have previously converted a DB with thousands of financial records and after the initial conversion, had no problems during a two month test of nearly daily use.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-06 12:06:03 +0200

Seen: 58 times

Last updated: Mar 06