Bug 137651 - Importing CSV automatically converts data with no possibility of tweaking
Summary: Importing CSV automatically converts data with no possibility of tweaking
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.1.2 release
Hardware: x86-64 (AMD64) All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: CSV-Dialog
  Show dependency treegraph
 
Reported: 2020-10-21 10:39 UTC by Andrei
Modified: 2021-05-11 17:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Andrei 2020-10-21 10:39:48 UTC
Description:
Hello,

I have a CSV with lines in the form of:


A0050,VP 2015-01,P301,ASR Schadeverzekering N.V.,02500,auto,Motor,02008,Wettelijke Aansprakelijkheid,02001,303,144,447,m,x,x,x

As not all fields are properly quoted, data like "02500" is automatically converted to "2500" as a number. It doesn't seem possible to ask Calc to leave everything as text or some other manual tweaks to prevent loss of data in a case like this.

I am quite sure that in 6.x checking "format quoted fields as text" also left this data untouched as text, as I've imported data in this form before.

A definite issue:
- the preview part of the CSV import shows my data correctly (eg 02500 instead of 2500), however after the import it becomes 2500 with no possibility of preventing this.

Steps to Reproduce:
1. Have a CSV file in the form described above
2. Open it
3. Observe the data preview
4. complete the import and observe the same data

Actual Results:
preview data: "02500"
after import: "2500"

Expected Results:
1. preview data consistent with data after import
2. have an option to not convert data at all (eg: leave everything as text)


Reproducible: Always


User Profile Reset: No



Additional Info:
Expected result #2 is more of a feature request, it would definitely be useful
Comment 1 V Stuart Foote 2020-10-21 21:52:32 UTC
You simply need to spend some time setting up the Text Import dialog. 

Each field (a 'comma' separator in your text line) can be set as to handling it needs. The import settings will reapply next usage--or you can save it and reuse.  Help is here [1], or check the Calc guide or Getting started guide.

Set the 'comma' delimiter, and then assign Column types for each field. Should clear things up for you.  If so, please close out this BZ issue.

=-ref-=
[1] https://help.libreoffice.org/7.1/en-US/text/shared/00/00000208.html?&DbPAR=WRITER&System=WIN
Comment 2 Andrei 2020-11-17 10:20:38 UTC
Hi,

yes, that's part of the issue. So I either need to tweak the handling of columns every time I do an import or save it to be used every time automatically.

Neither is a practical choice, as while I do these kinds of imports often enough that the first option is not practical (I have things with dozens of columns) I also definitely don't want to save a preset to be applied all the time.

So there are 2 points:
- it would be great to have the option to "don't covert anything" as a checkbox
- the import preview is still not working as a preview (although it says it's a preview, it doesn't show the outcome as my columns don't appear converted leaving the impression they won't be)

Thanks
Comment 3 Jean-Baptiste Faure 2021-05-11 17:17:15 UTC
(In reply to Andrei from comment #0)
> [...]
> Expected Results:
> 1. preview data consistent with data after import

Solution given in comment #1. Note that the case of numbers with leading 0 like phone numbers is explicitly mentioned in the help.

> 2. have an option to not convert data at all (eg: leave everything as text)

I think that is not a so good idea because CSV are mainly used for numbers, not for text, and there is probably very few examples of CSV files without numbers.
So, even you have 100 columns of text, it is enough you have 1 column of numbers, to make this option useless.
Note that this option would be useless for a CSV file with the kind of data given in the bug description.

So I close this bug report as worksforme. Please feel free to reopen if you disagree. In that case thank you for adding arguments.

Best regards. JBF