I am a newbie to this forum and I have been searching for help on Garmin Connect (activity) CSV downloads which do not appear to be in a user friendly format for excel analysis. There appear to have been a few requests on the forum for better native formatting and also the facility to download more than 20 files. These seem to have not been addressed by Garmin, as far as I can see. I made the mistake of trying to fix the issues I saw rather than doing the sensible thing of just finding different software.
What I wanted was an Excel spreadsheet that could be sorted and resorted without errors. This did not seem to be possible in the Garmin native CSV format. My solution was this (apologies for the clumsy excel programming, hopefully someone else has a better system).
Download the CSV file and change the file extension to ".txt".
Opening the file in Excel results in a file import wizard.
- in Step One Select "Delimited" and press "Next>>" (optional select "Start Import at Row" - select "3" to remove redundant information from the file)
- In Step Two uncheck "tab" and check "comma" then "Next>>"
- In Step 3 scroll over to the column labelled "Start" and select "Text" format from the radar buttons at the top of the page. Do the same for "Time" (also change to "text")
- press "Finish"
The spreadsheet should now be in Excel.
Its a good idea to save at this stage as a excel file (Excel/File/Save as/Save as type)
Insert three new columns immediately after column E ("Start")
Label the new columns (something like...):
-Date
-Time (extracted) - to distinguish from the existing column labelled "time"
-Av. Speed (this is computed to compare with the speed reported by Garmin as a check that the new cells are working OK)
You'll probably need to widen "Column E" otherwise the contents over-flow on to Column F.
In Column F (Date) insert this formula (copy Ctl-c and paste Ctl-v):
=DATE(IF(MID(E2,11,1)=",",MID(E2,13,4),MID(E2,14,4)),MONTH(DATEVALUE("01/" & MID(E2,5,4))),IF(MID(E2,11,1)=",",MID(E2,10,1),MID(E2,9,3)))
If the result is a number 40xxx.00, then change the format of the column to "date"
In Column G (Time (extracted)) insert this formula (copy Ctl-c and paste Ctl-v):
=IF(MID(I2,4,1)="",CONCATENATE("00:00",I2),IF(MID(I2,5,1)="",CONCATENATE("00:0",I2),IF(MID(I2,2,1)=":",CONCATENATE("0",I2),IF(MID(I2,6,1)="",CONCATENATE("00:",I2),I2))))
In Column H (Av. Speed) insert this formula (copy Ctl-c and paste Ctl-v):
=J2/(G2*24)
I have found for some bizarre reason that my Garmin sometimes formats its native "Time" column with a ":" separator and not a ".". The formula in column H gives the correct result (in the tests I've done)
If everything has worked OK, the H column should be the same as Column L (Garmin speed), although possibly with an additional significant digit.
If there is an error it is possible that the copy and paste has resulted in an unwanted space in the formula. Apologies but that is up to how the post is parsed by the forum posting software. Just check though and remove any spaces in the formula. Hopefully that fixes it.
Suggest you save the file at this stage.
Congratulations to anyone who got this far. Apologies for the leg-work. I have no idea now why I bothered, but hope this helps if you want to use it. There must be better software to analyse data on Garmin products (mine is a Forerunner 305). Now I'll go find some (any suggestions welcomed).
Garmin admin..... please provide a facility for downloading more than 20 lines in a CSV file (others have requested this going back to 2009!). Thanks
