Times in CSV files when used in Excel

[pfoulkes]

When times are exported in CSV results files, the format for times under an hour is "00:00" and for times over an hour is "00:00:00".
When these are used in Excel, Excel interprets the two different formats differently.
The times over an hour are interpreted correctly by Excel as hours, minutes, seconds.
The times under an hour are interpreted differently by Excel, typically as hours and minutes.

I organise a number of events which require more complicated scoring than standard events, and I use Excel to manipulate my results.
It is of course possible to manipulate the dates in Excel, but it isn't straightforward and the mismatch in times is very confusing when first encountered.

Would it be possible to modify the CSV format so that all times (both under an hour and over an hour) are exported in the format "00:00:00"?    I.e. for times under an hour, just prepend the time in the CSV file with "00:".
Then Excel would interpret them correctly.

Thanks
Peter

[Peter Effeney]

Peter

Thanks for the question.

Unfortunately the csv result line is generated by the MapRun App, pushed to the server and simply aggregated into a combined result file for each event. The format is OE CSV and has been the same since the inception of MapRun and was modelled on what Winsplits and similar systems accept.

A change the format would be best done by changing it in the App ... but obviously then everyone in your events would need to be using that version of the App.

You will have seen our first steps in moving results away from Routegadget (email to Admins about the latest version of the MapRun Console) and therefore this old .csv format... Associated with this has been an active discussion on API access to MapRun results.

There is now a Google Group discussion (very recent) you could join on this topic: [e-mail]

Some people are screen-scaping the results pages into Excel and there are working samples available. But there is also a new API that gives results in JSON format. Some work has been done to parse this in Excel, and this is a convenient format for use by websites/downstream systems. A feature of this JSON is that it gives total time and split times in an integer value for seconds, to make sorting and processing simpler.

The format of the JSON is still open for input.

Screen-scraping the existing results pages will ultimately not work once we move away from RouteGadget.

Let me know if this hasn't address the question ... but the more detailed discussion is best handled on the Google Group mentioned above.

Peter

[MichaelRaz]

Hi Peter F,
I share you pain with excel trying to automatically determine data types.  I've been web scraping results for a while (main use an Adventure Race) and when I first started writing the excel code I quickly came the conclusion that, in the end, it was easier add a single ' to each imported field to force Excel to just assume it was text.  I could do more adjustments as I brought the data in but decided to just make the one change and then work from there.  I know this is unlikely to fix your use case but thought I would mention it - if it something you are doing often then easier to write a bit of VBA (although could be down with formulas as you are aware.  Also possible in the code to turn off this addition of the single ' then "look" for the time fields and adjust at this state (before populating the spreadsheet).

I have posted a sample sheet with this portion of the code in the Google Group Peter E mentioned (under "Excel Code to pull Score-O results directly into sheet")  note that downloading from Google Groups seems to change the extension from .xlsm to .xlsx so you would need to rename it.  The sample is currently designed for Score-O events as it forms the correct url from the event name and this would be slightly different for a pt-pt although easy to change of modify to handle either.

As also mentioned, this method will eventually go away so I've done a prototype to get the json results into excel which I will be fixing up with more user configurations in the near future.  Funny thing, again with excel, the prototype does not add the quote so when I combined  the list of punch times into a single field something like 345.346.234.567,765,348 excel assumed it was a large number and changed it scientific notation.

[pfoulkes]

Hi Peter
Thanks for the reply.

Thanks for the link to the JSON discussion.

I am able to use the CSV in the current format.  I just need to be careful with it – processing the time fields before I use them in Excel.  I’ve set something up that makes it relatively quick for me to process a new CSV results file.
I raised the question today because I was talking to someone who had exactly the same confusion with Excel choosing to interpret 00:00 format times as hours and minutes.

After updates are made to MapRun, will the existing CSV format file still be available or will the JSON file be the only option?

Regards

[pfoulkes]

Hi Michael
Thank you for your notes.
I have read through your posts about the JSON prototype and will look at your work in a little more detail later.
Thank you for sharing it - I'm sure it will be useful.
The solution I have been using for a year or so starts with a CSV results file.  I use a power query to pull the data into Excel as text strings.  That has a similar result as you adding ' to the start of each time string.
Then I use some VBA to add "00:" to the front of all time strings that are only 5 characters long before converting time strings back to times.
Regards
Peter

[Peter Effeney]

Peter

In the longer-term, I think we should move away from the csv file format. We won't be removing it any time soon ...

However, I would rather agree a JSON format with the community that can serve the dual purpose of:
- Updating local spreadsheet-based event admin systems for flexible, "finish tent" functions of deriving team results, re-scoring results if needed, showing non-finishers against a list of entrants etc. The different functions required seem to be quite specific to the different contexts, and so keeping this as a flexible spreadsheet system, may be best (rather than enhancing MapRun)
and
- Usable for the retrieval of MapRun results into Club Website results systems.

Suggestions welcome.

Peter

[MichaelRaz]

For the basics you are really doing exactly what I do except I went with the parsing in VBA directly from the HTML rather than power scripting the file.  This has more to do with my use case as my actual Excel workbook is way way more complex given it's purpose is to dynamically update results as runners come in then map the Maprun results  against registered competitors, allow for adjustments in punches/time and produce final results.  The sample code I posted in the Google Group ("Excel Code to pull ScoreO results directly into sheet") was the basic parsing portion only.  I'm happy to share the full application but I would need to say that the code is really messy (written over time for me only) and would not handle people changing columns etc, thus might be of little use to anyone else (other than to show one way to do it).

The JSON parsing code I'm working on is far more robust and flexible (I'm part way through adding additional configuration options to include fields based on excel formulas).  To me it is really just a question of parsing JSON vs parsing HTML but I fully understand that this will be very different for many people.

[MichaelRaz]

Hi Peter E,

As you may have seen I'm not having any real issues parsing the JSON into Excel.  I would suggest that there are some fields that might be calculated on the server side (categories, placings), but nothing that can't be done client side.

I understand the advantages to going JSON (well, that's an exaggeration as I really know little about JSON) and I will end up with a useful "application" to flexibly parse JSON results into Excel for myself and anyone who want to use my workbook.  I guess the question becomes should there remain a server side production of a csv file available to those that have very simple needs and just want to cut and paste, or do an import of a csv file into something(that may be different than excel such as a web site)?  This may be a good compromise with most regular results displaying to the MapRun interface through JSON but, for those putting up the results elsewhere who can't deal with the JSON nor want to deal with someone's homegrown (unsupported?) tool, the ability to "request" the generation of a csv file.

Michael