4

I am using LibreOffice 5.0.5.2 in an Ubuntu 15.10 docker container to convert arbitrary incoming spread sheet files into canonical CSV files:

/usr/lib/libreoffice/program/soffice.bin \
    --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,1/2/2/2/3/2,1031,true,false,true' \
    --outdir '/tmp/outgoing' \
    'incoming.csv'

As I understand the documentation, this command line should faithfully convert spread sheets to a CSV, using:

  • , as a field separator ("44")
  • " as an enclosure character ("34")
  • UTF-8 as the incoming encoding ("76")
  • start with the first row in the file ("1")
  • format columns 1, 2, and 3 as text ("1/2/2/2/3/2"),
  • use UTF-8 has the outgoing encoding ("1031")
  • quote all text cells ("true")
  • do not detect special numbers ("false")
  • save cell contents as shown ("true")

This works well, except for columns that contain certain text with leading zeroes. For example, if incoming.csv is this:

"0123456789"

The resulting exported file is this:

123456789

My understanding is that if the column is marked as text on import, that will retain the leading zeroes, and then going the other way, marking "quote all text cells" on export will keep those preserved zeroes. But that does not appear to be the case for some set of inputs.

For example, these keep their leading zeroes, which I presume is because they contain non-numeric characters:

  • 0x3E
  • 0 123 456

Using the command line, how can I coax LibreOffice to keep the leading zeroes during export?

1 Answer 1

3

Finally, I found three sources that help me put it together:

  1. https://stackoverflow.com/a/30465397/2908724
  2. https://bugs.documentfoundation.org/show_bug.cgi?id=36313
  3. https://unix.stackexchange.com/a/259434/50240

What's missing from my command above is --infilter, which instructs LibreOffice on the import settings. The --convert-to flag specifies only the export settings.

This command fully preserves the leading zeroes in all test cases. The magic is the terminal true, which means "quote all text cells" and is equivalent to the checkbox of the same name on the import dialog.

/usr/lib/libreoffice/program/soffice.bin \
    --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1' \
    --infilter='CSV:44,34,76,1,,1031,true' \
    --outdir '/tmp/outgoing' 'incoming.csv'

As a side note, the argument order matters: --outdir must follow both --infilter and --convert-to.

Also, it seems like multiple --infilter may be specified to declare the specific combinations and settings that are allowed.

2
  • Aren't you glad you posted a bounty? :-) A question on your explanation: it looked like text was already being quoted; the issue was the need to basically quote every value so that number strings were treated as text. 'The magic is the terminal true, which means "quote all text cells".' I'm having trouble seeing why that fixed the problem, since the problem values weren't intrinsically text, which was the underlying issue.
    – fixer1234
    Sep 19, 2016 at 21:33
  • 2
    @fixer1234 My understanding is that "quote all text cells" deactivates heuristics LO normally performs to detect cell content and format it for display. In the UI, there is a corresponding checkbox that seems to also have this effect. The conflated use of the word "quote" in this context is certainly confusing.
    – bishop
    Sep 20, 2016 at 14:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .