Bug 136973 - Calc: Long (19 digit integer) Numbers imported from .CSV file as Text appear to be a number though it is too long to be represented as a number
Summary: Calc: Long (19 digit integer) Numbers imported from .CSV file as Text appear ...
Status: NEEDINFO
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.1.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: CSV-Import
  Show dependency treegraph
 
Reported: 2020-09-23 09:55 UTC by John Murrell
Modified: 2020-09-23 18:37 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description John Murrell 2020-09-23 09:55:20 UTC
Description:
I was importing a .CSV file into calc. The first field contains a 19 digit integer reference number. I have previously attempted to import this as a number but due to the length it is truncated and shown as in exponential format. Since I wanted to concatenate additional text to it I selected Text as the import type for the first column.

Having imported the data I inserted a column to the right and this appeared to be already formatted as text as entering a formula in it appeared as text and was not executed.

I then repeated the formula which was to concatenate some fixed text with the 'text' in the first column. The formula failed as the text in the first column was being interpreted as a number even though it had been imported as text. I then used the TEXT function to convert the value in the first column to a number and it truncated it - not much use as I need all 19 digits to reference the database. 

At that point I gave up and used TOPCAT to concatenate the strings

I can send a copy of the .CSV file if required it is from a public (Gaia DR2) ftp server.

Steps to Reproduce:
1.As above
2.
3.

Actual Results:
Long integer values imported as text appear to be a number though too long to be represented in Calc

Expected Results:
Data should have been imported as text as this had been selected as the import type for this column even though it is an integer number


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Data should have been imported as text as this had been selected as the import type for this column even though it is an integer number.

Source .CSV file can be supplied if required for testing / rectification

Result after concatenation was intended to be as follows ( other imported columns have been deleted)

GAIA DR2 1002377510699543296
GAIA DR2 1033149641409442432
GAIA DR2 1056014230522694912
GAIA DR2 1189102863162477952
GAIA DR2 1223491257550872192
..............
Comment 1 John Murrell 2020-09-23 10:14:50 UTC
I have tried this again and while the import type for the 19 digit integer was selected as text when imported the cell format shows it as a 19 digit number despite the text selection in the import wizard.

If the imported 19 digit integers are formatted as text using cell - format - text it appears that calc then believes they are text and you can concatenate them as expected.

It appears the problem is somewhere between the import wizard and calc itself. The selected text format is being over written as it looks like a number even though it is too large to be shown.
Comment 2 m.a.riosv 2020-09-23 18:37:59 UTC
Selecting the column as text, works fine for me, with your sample text, pasted as non formatted.
Version: 7.1.0.0.alpha0+ (x64)
Build ID: dba1a27be2438b5dbd1f8d253acb3850c9c044da
CPU threads: 4; OS: Windows 10.0 Build 20180; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US Calc: CL

But maybe attaching a sample file where you reproduce the issue it's the better for test.