Issues changing CSV column type to date in LibreOffice

Question

I encountered some trouble changing cell types in LibreOffice Calc from text to dates.

This happens when I import a CSV file. The import into rows and columns works fine. However, the dates are not recognized as such. When I try to change the cell type manually via "Format Cells..." to the appropriate date format Calc only prepends a ' to the cell.

Example: The text "19.03.24" reads "'19.03.24" after manually changing the cell format to date.

I suppose there is some "hidden" setting that prevents me from changing the cell type.

Answer 1

Okay, I found a solution here.

However, any comment on how that magic worked or why date formatting fails will still be appreciated.

Answer 2

CSV, comma separated values, though other separator characters, such as tab, are also used, has no concept of specific format, not as date, currency, Boolean or other data type.

All data in a CSV is treated as numeric, unless the data field contains specific characters, e.g., begins with a single quote, ', or is surrounded by double quotes, ".

Look at the CSV data in a text editor. If there are quotes at the beginning of a field, MS Excel, LibreOffice Calc and other spreadsheets treat that as text.

To remove initial single quotes, use the text editor to search and replace them. If there are internal quotes to be preserved,

• Search on the separator and quote, e.g., ,', and replace with just the separator, e.g., ,.
• For the initial field of a line, search on the end-of-line sequence and quote, e.g., \r\n', using regex, and replace with just the separator, e.g., ,.