Problem importing decimal data from CALC sheet to Firebird Base table

[frofa]

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?

[JRvZ]

Thank you for this insight.

I have now had the same experience. I am told from time to time that Firebird is now deprecated, and do I want to migrate to Firebird. For now I am sticking to HSQLB which works fine for me.

[Ratslinger]

Hello,

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.

[JRvZ]

I have looked at your examples. I am not clued up enough on the details to fully understand them, but one thing I noticed is that the problem is not seen as urgent. From a user point of view many database reports use a lot of decimal information, so having problems importing spreadsheets containing decimal data into a database greatly reduces the usefulness of Firebird. I am sticking to HSQLB for now.