36
20
I've imported a CSV file having the first column to be date-time values in ISO 8601 format like
2012-01-01T00:00:00.000Z
for the first moment of the year 2012.
Then, willing to make LibreOffice to recognize the format (as I was looking forward to plot a diagram), I've selected the column, chosen Format Cells...
and entered the custom time format as
YYYY-MM-DDTHH:MM:SS.000Z
And this seems to work if... I edit a cell to remove a hidden single-quote from its beginning (which serves to protect a cell content from being interpreted) as all the newly formatted cells now store values like
'2012-01-01T00:00:00.000Z
(note the single quote - it is only visible when you edit a particular cell).
And I am to do so for all the cells in the column. How can I automate this?
UPDATE: I've already found a solution for the particular case of mine: it helps to set a column format to "time" in the CSV import dialogue. But I am still curious how could this be done in case I wouldn't have the original .csv data file to import but only the .ods file with the data already imported without the format specified at the import time.
2Thank you. This is so weird, it beggars belief. Why on earth there should be this single quote character after importing? – r0berts – 2015-05-16T13:47:10.210
Please, developers: do not "fix" this "feature" :) – fbicknel – 2019-03-22T17:35:32.800
4Thank heavens for the internet and for you, sir. This was exactly right. – queso – 2012-03-05T21:44:19.320
5How the heck does it work if it even has no actual quote symbol (nor its code) in it? – Ivan – 2012-03-26T00:03:24.467
5@ivan - It's a messy hack, but it doesn't actually replace the quote at all. Basically, from the regex engine's perspective, the quote isn't there at all (it's a special flag on the cell). As such, what it does is match the entire contents of the cell (which doesn't have the quote in the string, remember). Then, what it does is delete the cell contents (which includes clearing the "plain text" flag), and then reinsert the contents. The data autodetection engine then sees a number, and interprets the reinserted data as such. – Fake Name – 2013-06-05T22:10:06.917
It's worth noting that this will probably delete any formatting that relies on characters that set special cell flags, as well as the "plain-text" number flags. – Fake Name – 2013-06-05T22:10:48.490