5

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.

4
  • 1
    You have two formats of date in your data, you want to convert them to one format? Aug 14, 2019 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, 2019 at 13:36
  • @guillermochamorro - please see edit..
    – Peter bill
    Aug 14, 2019 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, 2019 at 14:15

1 Answer 1

6

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.

3
  • This works and is extremely useful. Thanks very much for posting this answer. Actaully I found that you don't even have to specify all the fields you listed above. For example, libreoffice --calc foo.csv --infilter="CSV:44,34,0" specifies a separator, text delimiter, and character set, and apparently uses default values for the other fields. Oct 19, 2023 at 9:08
  • Here is more up-to-date documentation about the available CSV filter parameters. Oct 19, 2023 at 12:30
  • A couple more points. When you import a CSV in this way, by default Calc will interpret formulas in CSV fields, i.e. '2 + 3' will become a formula computing 5. Also, if you edit the CSV file and save it, by default Calc will quote all numbers. For me these behaviors are undesirable. You can import with --infilter="CSV:44,34,0,,,,,,true,,,,false" to enable the option "Save cell contents as shown", which will avoid quoting the numbers, and to disable the option "Import as formulas". Oct 28, 2023 at 9:24

You must log in to answer this question.

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