Calc - Read/Write CSV without any conversion

[vince_isg]

Hi everybody!

I want to read a CSV file, change it, and save it again. In the CSV file, I have a column with numbers. They are floats, but the value is “integer-like”: 1.0, 2.0, 0.0, etc.
When I open the CSV file, Libreoffice converts this floats to integers. When I save it back to CSV, integers are saved. So the file is different, even if I don’t change anything.
My problem is, I want to inject this file to a database (InfluxDB), and in my case the database wants floats. So after open/save in Libreoffice, I can’t inject the file anymore.

My question: Is there a way to prevent this automatic convertion? Is it possible to read and write a CSV file “as is”?

Thanks in advance!

Vince

UPDATE: How to reproduce:

1. Create a test.csv file with a text editor, like:

header_col1
1.0
2.0
3.0

2. open it with Libroffice Calc
3. Save it as test2.csv
4. Open test2.csv with a text editor. It will look like this:

header_col1
1
2
3

[anon73440385]

If an answer works for you, please consider to click the check mark () next to the answer. (That’s the way to indicate a correct answer, there is no need to close a question). Thanks in advance …

(Do not use [SOLVED])

[vince_isg]

okay, done, thanks for the hint.

[mikekaganski]

Libreoffice converts this floats to integers

In fact, it converts strings to doubles, and the doubles resulting from strings like 1.0 are represented without decimals.

If you need as much as possible similarity, I’d suggest you to import everything as text, marking all columns as such in the import dialog.

[vince_isg]

Okay, that was it, thank you! In the CSV import dialog, click on on column, then Ctrl+A to select all, then set column type to “Text” and import. On save, make sure “Save cell content as shown” is set. Is there any way in the prefs to set “Text” as standard method for importing?

[mikekaganski]

Is there any way in the prefs to set “Text” as standard method for importing?

No; see also tdf#74580 (but that doesn’t discuss default column import settings IIUC).

[anon73440385]

Hello,

Libreoffice converts this floats to integers

No - it shows 'em in the standard format for numbers, but there is no conversion to integers (in fact there is no integer type in calc at all. “Integer” is a subtype of “number” and called a “pseudotype” in OASIS ODF specs). See also Format -> Cells... -> Tab: Numbers which doesn’t show a Category Integer.

Hence to get them “as before”:

• Format the data columns after the import as they’d looked before the import
• In File -> Save As... dialog set option [x] Edit filter settings
• Assure option [x] Save cell content as shown is set.

Hope that helps.

[sokol92]

While they are fixing the bug pointed out by Mike, you can try the following macro.

' Opens a csv file, interpreting all fields as texts.
' Params:
' fileName file name. If omitted or equal to empty string, then it is requested.
' tokens   the first 4 tokens for the import filter, see
'          https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options.
'          If omitted or equal to empty string, then field separator is comma,
'          field delimiter is double quotes, encoding is UTF8.
' Returns an object related to the open document or Nothing.
Function OpenCsvAsText(Optional ByVal fileName As String, Optional ByVal tokens As String) As Object
   Dim args(1) as new com.sun.star.beans.PropertyValue, s As String, i As Long, oFileDialog
   Const MaxFields As Long=200  ' largest possible number of fields 
   
   OpenCsvAsText=Nothing   
   If IsMissing(fileName) Then fileName=""
   If IsMissing(tokens) Then tokens="" 
   
   If tokens="" Then tokens="44,34,UTF8,1"  ' comma, double quotes, UTF8, from 1st line
   If fileName="" Then
     oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
     With oFileDialog
       .appendFilter "Csv-files", "*.csv"
       If .Execute=0 Then Exit Function 
       fileName=.selectedFiles(0)
     End With  
   End If

   For i=1 To MaxFields 
     s=s & "/" & i & "/2"
   Next i
   If IsMissing(tokens) Then tokens="44,34,UTF8,1"  ' see https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
   args(0).Name="FilterName"   : args(0).Value="Text - txt - csv (StarCalc)"
   args(1).Name="FilterOptions": args(1).Value=tokens & "," & Mid(s, 2)
   OpenCsvAsText = StarDesktop.loadComponentFromURL(ConvertToUrl(fileName), "_blank", 0, args)   
End Function

[vince_isg]

Thank you! But unfortunately it doesn’t work, either with “save cell content as shown” set or unset… If I set the the standard format for number like 0.00 it works. BUT: I have some values with more decimals and some with less, and I would like to keep this “as is”. Is there a way to set the standard number format to just do NOTHING?

[anon73440385]

No, there is no way to do that. What you want to achieve: Change the format of a cell according to the decimal numbers given in the input on a “per cell”-basis. Finally: If your final intention is to import the csv to a database, then I don’t understand the problem of having all numbers the maximum number of significant decimal places. The database probably makes its own representation of the floats any way.

(In addition: I can’t understand the statement: So after open/save in Libreoffice, I can’t inject the file anymore. As stated in my answer: There is no conversion and hence you must have some import constraint in your database import procedure)

[vince_isg]

cool, thanks!

[vince_isg]

Yes, InfluxDB will throw an error if values with different types are overwritten. That’s why I need to keep my floats…

[anon73440385]

if values with different types are overwritten.

… but the explanation was: “There are no different types”. You stated: “It needs floats” and my answer was: “They are still floats (decimal numbers) just differently represented”

[vince_isg]

Yes, but if I save the file, they will be saved as integer inside the csv-file (see the UPDATE section in the question). The solution for now is opening the .csv as “Text”, so nothing is changed to the original csv (text) file, and then “save cell content as shown”.

[anon73440385]

but if I save the file, they will be saved as integer inside the csv-file

No - If you only had read my answer which states: Assure option [x] Save cell content as shown is set. and this assures that, if you format to have 12 decimal digits, you get 12 decimal digits in the csv output - and believe me: I do test, before posting. The thing is: You decided to not want to format accordingly.

[vince_isg]

Yes, you are totally right! I think I was not clear enough… My use case is, that I have some columns with different amount of decimals and I want to keep this exactly as is. Anyway, my problem is solved with the import as “Text”.