cpvalente
Import from spreadsheet does not seem to parse smart entry times correctly
Installation: v3.0.2 via Docker (Synology NAS), Microsoft® Excel for Mac 16.84.
Import from spreadsheet does not seem to parse smart entry times correctly. This may be used error or an Excel version incompatibility.
For example, I can not get a one hour duration successfully parsed as "60" (string or number)
Warning times of "15" parse as 15 minutes, but "10" doesn't parse at all
(It seems like maybe actual Excel booleans also don't get parsed for e.g., Link start, Public, Skip… they have to be strings "false" vs. "true.)
Installation: v3.0.2 via Docker (Synology NAS), Microsoft® Excel for Mac 16.84.
Import from spreadsheet does not seem to parse smart entry times correctly. This may be used error or an Excel version incompatibility.
For example, I can not get a one hour duration successfully parsed as "60" (string or number)
Warning times of "15" parse as 15 minutes, but "10" doesn't parse at all
(It seems like maybe actual Excel booleans also don't get parsed for e.g., Link start, Public, Skip… they have to be strings "false" vs. "true.)
Hi!
I wonder if the issue might have to do with the formatting of the fields.
Are you able to send me a template excel file with the issue to [email protected]?
Here's an example. All the data in the yellow-shaded cells doesn't successfully for me.
Note that warning/danger durations in minutes seem to parse only as long as they're strings (not numbers) and ≥13.
Hi @lukestein , thank you for this.
I will do some investigation on our side and hopefully have a fix in a release soon
(Not sure whether better to comment here or at #973 ; sorry)
duration
"60"gets parsed as0the documentation suggests that the parsing of time strings is the same as the interface. this is almost correct, we first try to create a date from the string, if that fails we attempt parsing the string according to the UI rules. Unfortunately"60"is a valid input to the Date constructor and makes our logic fail. I have attempted to use a regex to look for ISO 8601 strings and parse the string using theforgivingTimeString()otherwise
I'm not sure what Date constructor is being used here, but note that Excel is famously willing to convert anything to a date. This sounds hard and it may be good just to offer some guidance in the docs about caution rather than expect to catch every case.
For example, just occurring to me for example that smart entry should read 1 as 00:01:00but1.0as01:00:00. Of course, Excel will gladly turn 1into1/1/1900 12:00:00 AM`…
No need to apologise @lukestein, your help is very appreciated.
I am aware of how painful it is to get excel to not mess up with your dataset.
From our experiments, excel will generally take a timestamp like 09:00:00 and transform it into a iso date string like 1899-12-30T09:00:00.000Z. The user can of course format the cell as a string and have full time input.
Specifically for the excel imports. I feel that this a) happens outside the app (obviously) and b) can be done by someone who is not aware of the flow of the data or of Ontime altogether. For this I believe it is extra important to be lenient here, at least within reasonable expectation
I believe that we should cover the general cases of 60 09:10 09:10AM 9h 10m and consider edge cases on a case-by-case basis as users report them
Having said that, I completely agree with you, would you have a suggestion of how we could word this in the excel documentation. I fear this part of the documentation is already complex and wordy
Do e.g., p and p+1h and +30 work? Those are the only things in smart entry that really add functionality rather than just convenience.
Do e.g.,
pandp+1hand+30work? Those are the only things in smart entry that really add functionality rather than just convenience.
Agreed.
No, it doesn't work But it should
Installation: v3.0.3 via Docker (Synology NAS)
A new spreadsheet parsing weirdness I'm noticing in 3.0.3: 1 h (with space) parses as an hour, but 1h (no space) parses as a minute.
Not exactly this issue, but if "Link start" is True but there's no (unskipped) event to set the link start to, the import gives a confusing error:
Smart time seems to be working nicely for me in spreadsheet import. Hours and minutes with and without spaces… all good right now. Kudos and thanks!
I note in 3.1.1 I still get the "Internal Server Error" I screenshot above if "Link start" is true but there's no (unskilled) event to link start to. Maybe a clearer error message (even a generic "Spreadsheet formatting/import error" or something like that?)
Hi @lukestein , any feedback on how this is going in the latest release?
Thank you @lukestein , I will extract the comment into a bug ticket and push a fix for next release
Thank you @lukestein , I will extract the comment into a bug ticket and push a fix for next release
Great. You can probably close this issue then, as far as I'm concerned 🎉