CSV Export Filter - How to Preserve Large Integer Values Without Affecting Dates and Decimals?

English
#1

Hello,

I’m using LibreOffice 7.6 in headless mode to convert Excel files (.xlsx) to CSV format. I’m encountering an issue with the CSV filter parameters and hoping someone can help.

Current Command:
libreoffice7.6 --headless --convert-to csv:"Text - txt - csv (StarCalc)":44,34,UTF8,1,,0,false,true,false,false,false,-1 input.xlsx --outdir /output/path/

The Problem:
When Excel columns contain large integer values like 120224962901900000, the converted CSV file shows them in scientific notation: 1.202249629019E+017. I need these values preserved as-is.

What I’ve Tried:
I changed Position 9 (Save cell content as shown) from false to true:
csv:"Text - txt - csv (StarCalc)":44,34,UTF8,1,,0,false,true,true,false,false,-1

This preserves the large integers, but creates new problems:

  • Decimal values are rounded: 35.4202456 becomes 35.42
  • Date formats change: 06/18/2025 becomes 18-Jun-2025

What I Need:
I need a filter configuration that:

  1. Preserves large integer values (no scientific notation)
  2. Keeps decimal precision intact
  3. Maintains original date formats
  4. Handles all other data types normally

Questions:

  1. Is there a combination of filter parameters that achieves this?
  2. Is there any official documentation for all 12 CSV filter parameters? I’ve been unable to find comprehensive docs.
  3. I do not know before hand which column contains large numeric values, so I cannot tweak position 5 parameter beforehand in this case. Right?

Additional Context:

  • Converting multiple sheets to separate CSV files (which is why I need all 12 parameters)
  • Excel files come from external sources, so I cannot pre-format cells
  • Processing hundreds of files daily, so manual intervention isn’t feasible

Any guidance would be greatly appreciated!

#2

In the preview section at the bottom of the import dialog, click the column with the ID numbers and mark the column as “Text”.

#3

If your source XLSX had dates like “06/18/2025”, and numbers like “35.4202456”; and after export to CSV with “Save cell content as shown”, they became “18-Jun-2025” and “35.42” - that means either:

  • There may be a bug, that needs investigating and fixing; or
  • The data in XLSX actually does not have specific format, and rather uses some default, even without locale data. Then you need to make sure that LibreOffice uses the correct locale (in its profile) when --convert-to.
#5

Let’s conduct an experiment.

Create a new workbook in Excel, enter 120224962901900000 in A1. Save the file and examine the contents of \xl\worksheets\sheet1.xml (file fragment):

<row r="1" spans="1:1" x14ac:dyDescent="0.25">
<c r="A1">
<v>1.202249629019E+17</v>
</c>
</row>
#6

Cross posted on Reddit.

If you cross post, as a courtesy please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

#9

@ms777
Thank you for sharing the documentation. That was not easy to find from google search.

Attached are the three excel files that resembles the files we use at work.

  • test.xlsx → column B: This is where the large numeric gets converted to scientific notation.
  • test1.xlsx → column E: This is where the values are rounded off.
  • test2.xlsx → column J: This is where the exported date format in csv is different than the one in excel.
    test.xlsx (27.5 KB)
    test1.xlsx (17.3 KB)

@Villeroy thank you for the suggestion.
Unfortunately, we do not use macro in our data processing pipeline. Not sure if we can introduce the macro in our pipeline also (need to check).

Our main programming language is python and we call the libreoffice script to convert the excel files to csv. These steps are done in an automated manner.
The issue described in the question happens rarely (perhaps 1 out of 50 excel files have this issue) and the existing libreoffice script works pretty amazing for all of the other cases.

#10

@robleyd apologies for cross posting and not giving a heads-up here.

#11

It’s not for the pipeline. It is for development. Once you have the right filter options, you can drop the macro.

1 Like
#13

In an Excel file, the above data is stored in “scientific” format, but is displayed as integers due to the cell format.

The file stores the above data with a large number of decimal places and, when displayed, rounds it according to the cell format.

As far as I understand, the topic author’s request is the following: when converting a file (Excel, Calc) to CSV format, display numbers and dates according to the cell format.
I’m not aware of any built-in options for this.

#14

CSV filter parameters

#7

CSV Filter parameters is probably the best documentation

Please note that all format settings are per column settings. I understand that you do not know in which xlsx columns which kind of data types are found?! That makes it difficult, if not impossible …

Can you post some of the xlsx files?

1 Like
#8

Open one of your files interactively with all necessary options set and run the following Basic macro. Copy the filter options string from the message box.

Sub showFilterOptions()
Dim args(),i%
   args() = StarDesktop.CurrentComponent.getArgs()
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox args(i).Name,"",cStr(args(i).value)
   next
End Sub
1 Like
#12

Maybe, this refcard
LibOBasic_07_Files_Flat_A4_EN_v300.pdf (334.9 KB)
might be of use, though not official :slight_smile:
(look at the back)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.