I regularly need to open CSV files of a certain format in Libre Office Calc (on Linux Mint). The format is always a comma separator and the first column a date as D/M/Y.

Could anyone suggest a way to open these files without having to repetitively specify the format on the Text Import form, which normally appears when a CSV is opened.

I will happily consider command-line options, any script or macro or even an AutoKey script. The only restriction is that it should not interfere with the opening of CSVs that don’t fit this format.

Example data:

11 Jul 19, “Description 1”, 12.34
06/8/19, “Description 2”, 56.78
31/7/19, “Description 3”, 90.00

Edit - date formats

The mixed date formats are handled by Calc, when you set column A to be of type Date (on the Import Text dialogue). The solution should include this functionality.

  • 1
    You have two formats of date in your data, you want to convert them to one format? – guillermo chamorro Aug 14 '19 at 11:19
  • Create a script that opens your file with libreoffice calc and the format options you need and open those files with that script instead of calc directly – Philippos Aug 14 '19 at 13:36
  • @guillermochamorro - please see edit.. – Peter bill Aug 14 '19 at 14:15
  • @Philippos - that's the point of the question - how? My reading of the command-line options indicates no such facility. – Peter bill Aug 14 '19 at 14:15

After much searching, I came to this answer. A comment by @Adobe links to https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Filter_Options_for_the_CSV_Filter, which describes filter options.

This command worked for me:

libreoffice --calc mydata.csv --infilter=”CSV:44,34,0,1,4/2/1”

Explanation of the contents of the infilter argument

After the type, there are five comma-separated fields:

1. Separator, 44 is the ASCII value of comma
2. Text delimiter, 34 is the ASCII value of double quote
3. Character set to use, 0 is the system default
4. Line number to start conversion. I have no header line, so it is 1
5. Cell format per column, separated by /. It does not seem to matter if there are a different number of columns in the data: Extra columns default to Standard, extra formats are ignored.
   4 = date d/m/y; 2 = text; 1 = standard

Once the file is opened in Calc, I can sort chronologically and do arithmetic on the number column.

There are lots of other options in the reference, all just as comprehensive.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.