I would like to use ISO8601 format for dates when importing from CSV into Calc. How do I make this happen? So far, it looks like I am forced to accept the date format configured for my locale.
Specific formats can’t be assigned in the import filter. After import select date column, apply ISO date format.
Thank you for confirming this for me. I will need to ask for a feature, then.
Just to emphasise the point, when you import a csv you must import it formatted in its original date order, not what you want it to be, nor even your locale format.
This Q&A is littered with problems caused by trying to format the date to something different during import.
I’m not sure what you mean.
I want to apply a date format to the imported column after the import is finished. I am importing the date correctly by marking it in the import dialog with the correct date order.
For example, if the date in the CSV file is 10/23/2019, then I’m correctly marking that column as “MDY”, but I want the resulting imported column to automatically be formatted as my desired date format “YYYY-MM-DD” and not the locale default of “MM/DD/YYYY”.
I hope this clarifies my position.
Yup. It is a two stage process
when you import a csv you must import it formatted in its original date order, not what you want it to be, nor even your locale format
This is unreasonable. Doing that would make resulting import be filled with zillions of directly formatted cells, each potentially having separate format. Adjacent dates could get different formats: 21/2/2021, 21/12/2020, 1/12/2020, 1/2/2021 might have four different formats: dd/m/yyyy, dd/mm/yyyy, d/mm/yyyy, and d/m/yyyy. All of them fit the DMY of the import dialog… of course, something could be done in this regards to minimize the harm, but then it’s only a sample that came in the first seconds when reading the proposal. There would be lots more.
Reading CSV as values, not as text, should produce not same-looking sheet, but sheet with least formatting. That is what is currently done. And that’s good.
Given that for dates, there’s always a number formatting applied to cells (or else the underlying data would be displayed as number), it seems possible to have a configurable format applied to such data (one or the other, it’s still formatting). But then it would look logical to also expect user-configurable number format for numbers … and that would result in sheets containing formatting where previously there were none.
IMO the current state (detected data is shown in a default formatting in sheets imported from CSV; any custom formatting is applied manually afterwards, and not comes from CSV or import process) is consistent and logical.
It seems that what I want is some kind of “preferred number format” in general and the ability to apply that preferred number format easily to columns after they’ve been imported.
Doing this by hand every single time is logical and consistent and very tedious and quite error prone.
It might depend on your use case. You may use a locale which uses ISO date format: like en-CA. Or you may create a macro for importing the data and formatting it at the same command. Or - if you are importing new versions of the same CSV - you may import the CSV as a linked sheet to an ODS once, have it formatted as you want, and have it updated automatically when you replace the CSV with a new version…
Anyway, note that no matter what I (or anyone else) write here, you are always welcome to file an enhancement request - but you need to do that to the proper place. This site is not a proper place for this.