Libreoffice Calc and importing simple timestamp values
February 24, 2023 6:32 AM Subscribe
I have a very simple CSV file that looks like 01:23:45,100 and I'm trying to import it into LibreOffice Calc but it's fighting me at every turn. Can you tell me the 'right' way I'm supposed to do this?
The root problem is that no matter what I do, LibreOffice thinks these HH:MM:SS values are text, not a value. (And no, the CSV file doesn't have any quotes.) They do not show up as blue if Value Highlighting is enabled. Doing anything with cell formatting is a waste of time -- it either ends up forcing them to text (with a leading single quote) or turning them into nonsense.
The CSV import dialog (also available as Data -> "Text to columns") offers a pathetic selection of column types: Standard, Text, Date, US English, Hide. Where is Time? None of these work.
The only workable solution I've found so far is to use an external tool to add a date in front of the time, so that the file looks like 2023-01-02 03:04:05,100. Then I can choose Date when importing, the cells are blue, and *THEN* I can select a time format to have them display as HH:MM:SS. I don't consider that a solution. This is a f'ing spreadsheet, it should be able to do this.
This is the most frustrating part: If I type 01:23:45 into an empty cell, LibreOffice happily recognizes that as a time, making it a blue value and changing its formatting to "01:23:45 AM" (why does it force AM/PM nonsense on me when I have Windows set to 24 hour format?! But that's a separate gripe.) It knows damn well how to handle this format, but it just refuses to do it with any consistency. Where is the "pretend I manually typed in every value in this column" button?
I suspect someone is going to say I have to write a formula that uses sub-string indexing to manually pull out each of the fields and multiply them to create some kind of timestamp value. That sucks for multiple reasons: first, it requires I have this stupid extra column of the original data in a useless form. (Or convert the formulas to hard values and delete the original column, also sucks.) Second it requires that every time I do this I have to look up all the details of string indexing functions and all that baloney. This is like saying you can get to work by pushing your car there. While it's technically a solution, it's garbage.
I'm using LibreOffice version 7.4.5.1, which I believe is the latest stable.
The root problem is that no matter what I do, LibreOffice thinks these HH:MM:SS values are text, not a value. (And no, the CSV file doesn't have any quotes.) They do not show up as blue if Value Highlighting is enabled. Doing anything with cell formatting is a waste of time -- it either ends up forcing them to text (with a leading single quote) or turning them into nonsense.
The CSV import dialog (also available as Data -> "Text to columns") offers a pathetic selection of column types: Standard, Text, Date, US English, Hide. Where is Time? None of these work.
The only workable solution I've found so far is to use an external tool to add a date in front of the time, so that the file looks like 2023-01-02 03:04:05,100. Then I can choose Date when importing, the cells are blue, and *THEN* I can select a time format to have them display as HH:MM:SS. I don't consider that a solution. This is a f'ing spreadsheet, it should be able to do this.
This is the most frustrating part: If I type 01:23:45 into an empty cell, LibreOffice happily recognizes that as a time, making it a blue value and changing its formatting to "01:23:45 AM" (why does it force AM/PM nonsense on me when I have Windows set to 24 hour format?! But that's a separate gripe.) It knows damn well how to handle this format, but it just refuses to do it with any consistency. Where is the "pretend I manually typed in every value in this column" button?
I suspect someone is going to say I have to write a formula that uses sub-string indexing to manually pull out each of the fields and multiply them to create some kind of timestamp value. That sucks for multiple reasons: first, it requires I have this stupid extra column of the original data in a useless form. (Or convert the formulas to hard values and delete the original column, also sucks.) Second it requires that every time I do this I have to look up all the details of string indexing functions and all that baloney. This is like saying you can get to work by pushing your car there. While it's technically a solution, it's garbage.
I'm using LibreOffice version 7.4.5.1, which I believe is the latest stable.
Here's what I might try:
See if you can do a custom CSV import in LibreOffice where the delimiter is ':' as well as ',' so that everything comes into separate columns. If the first three columns (the components of the timestamp) are imported as numeric, it should be fairly easy to write a formula to combine them into an actual time-type timestamp in another column. (Then maybe somehow copy the result into yet another column as a non-formula value) Then delete the imported columns you're not using.
I don't remember if LibreOffice's import lets you specify multiple delimiters. If not, 1) import with ',' as the delimiter, which will give you your column of text times; 2) delete the second column, so you just have the text time column; then 3) export that, and re-import just that column with the ':' delimiter - then combine the three numbers in the formula as you describe.
Am I 100% sure this will work? No, sorry - but it's an idea that seems easier than complicated text-to-time values; OTOH it might be just as much work, depending on how comfy you are with that kind of thing. I personally would find it easier (and interesting :).
posted by amtho at 7:27 AM on February 24, 2023
See if you can do a custom CSV import in LibreOffice where the delimiter is ':' as well as ',' so that everything comes into separate columns. If the first three columns (the components of the timestamp) are imported as numeric, it should be fairly easy to write a formula to combine them into an actual time-type timestamp in another column. (Then maybe somehow copy the result into yet another column as a non-formula value) Then delete the imported columns you're not using.
I don't remember if LibreOffice's import lets you specify multiple delimiters. If not, 1) import with ',' as the delimiter, which will give you your column of text times; 2) delete the second column, so you just have the text time column; then 3) export that, and re-import just that column with the ':' delimiter - then combine the three numbers in the formula as you describe.
Am I 100% sure this will work? No, sorry - but it's an idea that seems easier than complicated text-to-time values; OTOH it might be just as much work, depending on how comfy you are with that kind of thing. I personally would find it easier (and interesting :).
posted by amtho at 7:27 AM on February 24, 2023
If Excel was behaving like this, I would try replacing the colons with another character like Z, format as time, and then replacing that Z with a colon again. That will often force a revaluation of the data.
The formula solution is the "right way" to clean up data when it doesn't import correctly. If this is something you do on a regular basis, then writing a macro to insert a column with the formula, converting it to hard data, and then removing the extra columns would be the accepted solution. Yes it is a tiresome workaround.
posted by soelo at 8:56 AM on February 24, 2023
The formula solution is the "right way" to clean up data when it doesn't import correctly. If this is something you do on a regular basis, then writing a macro to insert a column with the formula, converting it to hard data, and then removing the extra columns would be the accepted solution. Yes it is a tiresome workaround.
posted by soelo at 8:56 AM on February 24, 2023
I don't remember if LibreOffice's import lets you specify multiple delimiters.
It does (tab, comma, space, semicolon and one 'other'), and you can even specify one to use as string delimiter.
posted by Stoneshop at 9:10 AM on February 24, 2023
It does (tab, comma, space, semicolon and one 'other'), and you can even specify one to use as string delimiter.
posted by Stoneshop at 9:10 AM on February 24, 2023
Best answer: I just tried this - leave the column type set to "standard" and tick the "Detect Special Numbers" option. My import of your line shows the first column as time, and the second as a regular number.
posted by jquinby at 10:00 AM on February 24, 2023
posted by jquinby at 10:00 AM on February 24, 2023
Screenshot of results and options, using V 7.3.7.2/Linux
posted by jquinby at 10:04 AM on February 24, 2023
posted by jquinby at 10:04 AM on February 24, 2023
Response by poster: THANK YOU. "Detect special numbers" was the magic bullet. And is the actual, proper solution that I was asking about, not cobbled together hacks. (I already have my hack solution in the form of a sed oneliner to add a date.)
posted by Rhomboid at 10:04 AM on February 24, 2023
posted by Rhomboid at 10:04 AM on February 24, 2023
Response by poster: Also congratulations to the LibreOffice UI team for hiding something relevant to time formatting under the label "special numbers" and making it non-default, you are certainly earning that degree in stupid.
posted by Rhomboid at 10:10 AM on February 24, 2023 [1 favorite]
posted by Rhomboid at 10:10 AM on February 24, 2023 [1 favorite]
I'm all for sed one-liners as needed. To be candid, I asked ChatAI first and got back a fairly dopey step-by-step set of import instructions that use a "Time" column type which - ha ha - doesn't actually seem to exist. That led me deep into the Librecalc docs to this page, which mentions:
Detect special numbers
When this option is enabled, Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation.
Hooray AI, maybe?
posted by jquinby at 10:11 AM on February 24, 2023
Detect special numbers
When this option is enabled, Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation.
Hooray AI, maybe?
posted by jquinby at 10:11 AM on February 24, 2023
To address the followup, All date formats are dependent on the locale that is set in Tools - Options - Language settings - Languages. English(UK) will default to little-endian DD/MM/YY and English(Canada) will give big-endian YYYY-MM-DD. You can then reset the default currency to greenbacks. It does not appear possible to set the default date format directly within registrymodifications.xcu. You can hack the source code, create your own locale, or try the above workaround. Sorry.
posted by backwoods at 4:02 PM on February 25, 2023
posted by backwoods at 4:02 PM on February 25, 2023
« Older Getting cataract surgery. Freaking out about... | In your opinion is shyness (not introversion)... Newer »
This thread is closed to new comments.
posted by Rhomboid at 6:55 AM on February 24, 2023