Search code examples
export-to-csvprecisionlibreofficefloating-accuracylibreoffice-calc

Prevent Libreoffice converted CSV from stripping trailing zero?

I have a Calc spreadsheet containing =ROUND(1.001,2) which results in 1.00 when formatted as a number with -1234.57 (aka Format code 0.00)

However, when I convert it to CSV via libreoffice --convert-to csv the trailing 0 are removed resulting in 1. How can I get the proper 1.00 output into the CSV?

Note: If I increase precision =ROUND(1.001,3), then the expected 1.001 is output.

Converting single cells explicitly to TEXT (eg =TEXT(ROUND(1.001,2), "0.00")) works, but I would like something that works at the document level as this is quite error-prone / tedious.

Solution

  • "How can I get the proper 1.00 output into the CSV?" The short answer is you cannot. This is because CSV is an awful file format (see: https://www.google.com/search?q=whyt+is+csv+a+bad+file+format )

    But when you have a file in OpenOffice like this (let's name it: Untitled 1.ods): enter image description here

    And you need/want to export it with trailing zeroes, you can do:

    C:\Program Files\LibreOffice\program\soffice --convert-to html "Untitled 1.ods"

    Which produces and Html file named Untitled 1.html

    After this you can use xmlstarlet to do this:

    xmlstarlet sel -t -m //tr -m td -v . -o "," -b -n "Untitled 1.html"

    Which produces next output:

    a,b,c,d,e,f,
1,00,2,00,3,00,1,00,2,00,3,00,
4,00,5,00,6,00,4,00,5,00,6,00,
7,00,8,00,9,00,7,00,8,00,9,00,

    This only works because exporting to HTML keeps the format which was applied to the cells, and exporting to CSV just exports the values, and does not care for any formatting done.

    P.S. The output just shows why "Comma Separated Value (CSV)" should never have been invented 😁.