As more race organisers are using CloudTimer in combination with the dashboard, it is important to get a bit further into detail about uploading start lists.
It is possible to upload a .CSV file in a fixed format. We are working on a more flexible way of uploading but for now we have to stick to a certain protocol.
The protocol is a set of column titles of the start list. The columns are separated by commas. Therefore it is named a comma separated value file. The column titles are: Bib,Name (Team),First Name,Prefix,Last Name,Gender,Category,Tags,Club,Country
- A bib should be a unique number to keep things simple.
- A team name is not necessary, it can be lest blank
- Members of a team go into the list under the same bib-number as the team name. (as shown in the tble below for #513)
- Most parts of this protocol are quite obvious. Some might wonder what the prefix is. In some counties there are prefixes to surnames for example: Van, Le, Mac, Von etc.)
- The category has to match the categories in the CloudTimer competition exactly. Men and Women are default categories.
- There can als be tags to a competitor. This can be useful when there are some remarks that you want to add on the results list. For example: A junior competing in a senior race (add U18) or someone competing Hors Concourse (add HC).
- Multiple tags can be added to one competitor, separated by a ; (semicolon)
- The countries have to be exactly as listed in the CloudTimer database
Here is an example of a start list:
|Bib||Name (Team)||First Name||Prefix||Last Name||Gender||Category||Tags||Club||Country|
|516||Jack||Dönner||male||Men||LA Lakers||United States of America|
|517||Jack||Dönner||male||Men||LA Lakers||United States of America|
|518||Jack||Dönner||male||Men||LA Lakers||United States of America|
How to create a proper .CSV file?
The easiest was is probably to use google sheets. It can be combined with a subscription form and is free to use. A .CSV can be made by choosing File->download as->Comma Separated Values (.csv)
An example of a google sheet document can be found here. It contains some useful pages for printing a start list, invoices and default values (for clubs and countries etc)
Using excel is a bit more complicated. For some reason, Microsoft excel does not use the international standard for .CSV. Although they provide the option to save as .csv, the file created is actually a ; separated file. Another reason not to use “save as .CSV”, is that you immediately loose your formulas in excel, if you did not save as an excel sheet before.
So, what to do when using excel? Use an extra column to you start list (in the format as shown above), and add the header into the first cell (K1):
Bib,Name (Team),First Name,Prefix,Last Name,Gender,Category,Tags,Club,Country
Then add the following formula into the second cell of this column. (it should be K2).
This formula concatenates all values of the columns A to J, separated by ,
The tags are places into “” and separated by ;
Now copy the formula down for each row containing competitors or teams.
Then select the header and all data in column K, and copy the data to the clipboard (ctr+c)
Then open notepad, and paste the data from the clipboard into the notepad file.
Then save as a .CSV file. To do so, choose *.all as file format and just add .csv to the filename.
When using special characters such as é,è,ö etc. you should use the UTF-8 protocol
Using a macro in excel
Excel provides the wonderful feature to create a macro, saving some time and your start list with just one keyboard combination. If you know how to create use a macro, you can copy paste the following text into your macro editor, and run the macro by tapping ctr-e. Do not forget to match the name of the sheet containing the data with the text in the macro.
But it might be easier to use this example file: CloudTimer start list Template
‘ CSV_export Macro
‘ Exports column K as a .CSV file for CloudTimer upload
‘ quick key: Ctrl+e
‘ General function: exports strings contained in a range (1 column wide, varying height) into a text file
Const ForAppending = 8
Const rStartCell = “macroSwitch_OutputTextBelow”
Const strFileNamePrefix = “Your output file ”
Dim fs, f
Dim saveFile As String
Dim WorkRng As Range
Dim s1 As Excel.Worksheet
Dim iLastRowS1 As Long
Set s1 = Sheets(“Startlist template“) ‘replace name of sheet1.
On Error Resume Next
‘ figure out how many rows to select and export:
‘ start at range “macroSwitch_OutputTextBelow”
‘ look down to last filled in row to count number of rows with actual check data
‘ then use that height as height for range to be exported
iHeightOfDataRange = s1.Cells(s1.Rows.Count, “K”).End(xlUp).Row
MsgBox (“number of participants to export: ” & iHeightOfDataRange – 1)
Set WorkRng = Range(“K1”).Offset(0, 0).Resize(iHeightOfDataRange, 1)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
‘ create file to export to
Set fs = CreateObject(“Scripting.FileSystemObject”)
saveFile = Application.GetSaveAsFilename(InitialFileName:=strFileNamePrefix & Format(Now(), “yyyy-mm-dd”), _
filefilter:=”Comma Separated Text (*.CSV), *.CSV”)
Set f = fs.createTextFile(saveFile, ForAppending, TristateFalse)
‘ and write export range line by line
For Each Row In WorkRng.Rows
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
‘ if needed:
‘ alert user to check content for file manually
MsgBox “Make sure the file looks correct”
‘ open the file for user review
Shell “notepad.exe ” & saveFile, vbNormalFocus