All numbers are imported as floats by XLS(X) importer

[frafra]

To Reproduce

Steps to reproduce the behavior:

1. Export a table containing an integer column

Current Results

A cell showing the integer 123 is exported as 123.0. This makes SQL exports fail with the following error: MESSAGE: 123.0 is not compatible with column type :INT.

Expected Behavior

What you see is what you get: the software should not export cells in a different format.

Screenshots

Versions

• Operating System: Linux
• Browser Version: Firefox
• JRE or JDK Version: OpenJDK Runtime Environment 17.0.4+8-nixos
• OpenRefine: 3.6.2

Datasets

Additional context

[elebitzero]

Hi @frafra, I tried to reproduce this with the food-small.csv test data, and was not able to. I exported two of the number columns Magnesium and Phosphorus as type INT in SQL Export, and it looks fine. No decimal point was added.

Example output:

CREATE TABLE food_small_csv (
Magnesium INT NULL,
Phosphorus INT NULL
);
INSERT INTO food_small_csv (Magnesium,Phosphorus) VALUES
( 2,24 ),
( 2,23 ),
( 0,3 ),
( 23,387 ),
( 24,451 ),
( 20,188 ),
( 20,347 ),
( 22,490 ),
( 28,512 ),
( 21,464 )

[frafra]

Could it be that is just Excel-files related?

[ostephens]

I don't think the issue is that an integer is being cast to a float, but rather that what looks like an integer in the OpenRefine UI is actually a Java Double. OpenRefine rather glosses over the different number types in the user facing parts of the system, but behind the scenes the number can be stored as either a Long or a Double. For doubles like "1.0" only "1" is shown in the UI, but on export 1.0 will be used

@elebitzero if you run a transformation like
value/1.0

on the values in one of the columns before trying the export I think you'll see the issue manifest (because dividing by 1.0 will force the number to a double in the background but shouldn't change the display of the number in the UI)

[tfmorris]

This would be a lot easier to triage with example data which demonstrates the problem, so that folks don't have to guess.

[ostephens]

Try this project;
sql-numbers-export.openrefine.tar.zip

See in the history I've divided the value in row 2 by 1.0

Facet by star and select 'true' to isolate the second row. Try the SQL export with Numbers column set to export as INT. See error
Remove star facet and Facet by flag and select 'true' to isolate the first row. Try the SQL export with Numbers column set to export as INT. See success

[tfmorris]

Yes, I'm very familiar with how OpenRefine's number processing works . I have no idea if it's relevant to what @frafra reported, because they haven't included an example. I would fully expect the SQL exporter to export floats with decimal points in them. The simplest explanation is that the data isn't actually integer data, but an example would make this obvious.

[ostephens]

What I was trying to do is recreate the situation described by @frafra . I understand you are saying it would be good to get this directly from @frafra and anything else is speculation - fair enough. I was just trying to respond to your request to see some example data which demonstrates the problem - which I've done.

[frafra]

Here is the smallest example I could produce. I just opened LibreOffice and generated an XLSX file containing integer as first column and 123 under it.

openrefine-issue-5389.xlsx

I have the same issues with XLSX files generated by Microsoft Excel.

[ostephens]

@frafra thank you. Importing this seems to interpret the incoming number as a double rather than an integer. To solve your immediate issue you need to covert to an integer - e.g. you could first run value.round() on your column. As round() gives an integer output then you'll be able to export with the SQL Exporter to an INT column

I'm not sure what the correct fix for the underlying issue is here at the moment, but if you need an immediate working solution I think you'll find that round() works