Garmin Xero C1 - Postprocessing Data Issue in Excel

[RickKr]

Just received my unit, took it out for some simple trials, 22LR in a revolver and a slingshot. Unit worked as advertised, so I'm delighted there. As others, I'm amazed at how simple, easy and reliable it is to use.

However, I am baffled by an issue when trying to work with the data in Excel, having exported them from the ShotView app to my PC via email. Opened the CSV file in Excel and attempted to do some cell referencing and formula work only to find out it doesn't work because the data fields are "text", not number values. Using the "Cell Format" function to change the format from text to number does NOT change the format, they remain as text. Double-clicking on a number and then pressing enter DOES change the format to number. Dates and times are also text but double-clicking and pressing enter does not change them into data values. While this works, it would be a bit onerous when there is a lot of data.

Copying the entire block of text, pasting into "Notepad", a text editor, and then copying that back to the same block space in Excel DOES change the numbers into number values. This is a much more reasonable.

I'm wondering if I'm the only one 1) attempting this sort of postprocessing and 2) having this issue. At this point, this only affects me playing with the data in Excel and the programs I use for working with shot velocity data, such as Gordon's Reloading Tool require manual entry of data (GRT does not yet have an import tool for Garmin as it does for Caldwell , Labradar and MagnetoSpeed chronys.)

I've emailed Garmin about this just a bit ago, so it will be days before I hear back, if at all.

Rick

 

[prone.life]

fwiw I regularly import my Garmin csv into Google Sheets by emailing from chrono with no such issues. I add three columns with calculations.

In Numbers (Mac) you can choose which charset/text encoding to use in importing data in Advance Settings in the import dialogue. I don't remember Excel giving you that option but it's been a minute since I've used Excel.

 

[rijndael]

I don't have this issue, but have you tried saving the file as an xls to see if it behaves differently than when a csv? There are features not available in csv format that are available in a native spreadsheet.

Have you tried hitting CTRL-A and trying to change the cells all at once?

 

[RickKr]

Thanks for those hints. I realized after posting that the file type that the iPhone app (ShotView) is exporting is not CVS, rather it is formatted as and XLS workbook with separate sheets for each shot group and session. It is in these workbook that the numbers are formatted as text not values. ShotView is pretty simple, no options for designating export formats.

Update: Just off the phone with a Garmin Xero support dude. New to him. While on the phone we determined the difference is exporting individual shot groups, it goes as CVS format and values come across into Excel as numbers. But, selecting multiple shot groups (see image below) causes the ShotView app to export as XLSX format with multiple sheets (which cannot exist in a CSV file) and that is where the numbers are formatted as text. He is passing the issue on to the tech group, but for now the only way is to export individual shot groups. That is more work than the only other reasonable solution I found is to do a block copy of all the info in a shot group sheet to NotePad and then back into Excel. Exporting individual shot groups is way more work as each are sent via email and each file has to be saved from email to the PC and then individually opened in Excel, yada, yada, yada...

Guess I'll see how much I really need to work with this data in Excel versus manually entering it into GRT or whatever shot/group analysis app I'm trying to use.

The guy was great, much better interactive support than I've experienced when calling a support line.

Rick

 

[rijndael]

ETA: Disregard, I see your update above.

Use the Home tab, in the app, and export 1 session. It's a csv. Below is one of mine.

You're selecting multiple, from the "Sessions" tab?

"Rifle Cartridge"
#,SPEED (FPS),Δ AVG (FPS),KE (FT-LB),POWER FACTOR (KGR⋅FT/S),TIME,CLEAN BORE,COLD BORE,SHOT NOTES
1, 2761.1, -9.0, , , 9:26:52 AM, , , ""
2, 2759.6, -10.5, , , 9:27:32 AM, , , ""
3, 2773.4, 3.3, , , 9:27:56 AM, , , ""
4, 2754.1, -16.0, , , 9:28:41 AM, , , ""
5, 2772.1, 2.0, , , 9:28:58 AM, , , ""
6, 2768.9, -1.2, , , 9:29:43 AM, , , ""
7, 2768.7, -1.4, , , 9:30:35 AM, , , ""
8, 2774.8, 4.7, , , 9:31:03 AM, , , ""
9, 2770.9, 0.8, , , 9:31:29 AM, , , ""
10, 2780.4, 10.3, , , 9:32:14 AM, , , ""
11, 2769.7, -0.4, , , 9:32:37 AM, , , ""
12, 2755.8, -14.4, , , 9:32:57 AM, , , ""
13, 2768.5, -1.6, , , 9:34:52 AM, , , ""
14, 2774.0, 3.8, , , 9:35:18 AM, , , ""
15, 2777.9, 7.8, , , 9:35:53 AM, , , ""
16, 2782.9, 12.8, , , 9:36:24 AM, , , ""
17, 2770.1, -0.0, , , 9:36:47 AM, , , ""
18, 2779.0, 8.9, , , 9:37:08 AM, , , ""
-,,,,,,
AVERAGE SPEED,2770.1,,,,,,,
STD DEV,7.9,,,,,,,
SPREAD,28.8,,,,,,,
SESSION NOTE,"6.5CM. H4350. 50F.
Seating depth testing",,,,,,,
-,,,,,,
DATE, "February 27, 2024 at 9:26 AM",,,,,
All shots included in the calculations,,,,,,,

 

[NZ_Fclass]

I had an issue with dates in Excel - albeit an older version - as dates are held aa a sequential number starting from some ambiguous point last century - but displayed in the familiar format, dd/mm/year. Excel could not re-interprate them from text I confess that I have not attempted this task recently, having manually entered all the dates in a database - correcting some that were in US format, mm/dd/yy.

 

[newbieshooter]

Thanks for those hints. I realized after posting that the file type that the iPhone app (ShotView) is exporting is not CVS, rather it is formatted as and XLS workbook with separate sheets for each shot group and session. It is in these workbook that the numbers are formatted as text not values. ShotView is pretty simple, no options for designating export formats.

Update: Just off the phone with a Garmin Xero support dude. New to him. While on the phone we determined the difference is exporting individual shot groups, it goes as CVS format and values come across into Excel as numbers. But, selecting multiple shot groups (see image below) causes the ShotView app to export as XLSX format with multiple sheets (which cannot exist in a CSV file) and that is where the numbers are formatted as text. He is passing the issue on to the tech group, but for now the only way is to export individual shot groups. That is more work than the only other reasonable solution I found is to do a block copy of all the info in a shot group sheet to NotePad and then back into Excel. Exporting individual shot groups is way more work as each are sent via email and each file has to be saved from email to the PC and then individually opened in Excel, yada, yada, yada...

Guess I'll see how much I really need to work with this data in Excel versus manually entering it into GRT or whatever shot/group analysis app I'm trying to use.

The guy was great, much better interactive support than I've experienced when calling a support line.

Rick

1. I just exported multiple sessions to xlsx file from my iPhone
2. Open the file in excel
3. notice all the data fields are stored as text. There is a green triangle error for each of the data cells which states that the number is stored as text:



4. You can tell that the number is stored as text since the number is "left aligned" in the cell with a green triangle warning.



5. Highlight all the data
6. select the yellow triangle ! dropdown




7. select "Convert" to Number



8. Notice all the data is now stored as "number". Numbers are "right-aligned", and the green triangle warning is gone.

 

[RickKr]

Ha, been using spreadsheets since SuperCalc and Excel since 1990. Knew about the formatting indicators, etc. that you you mention. But, had never noticed that yellow triangle. That worked, coverted to number as you said.



Worked for contiguous blocks, but not for non-contiguous blocks.


Thanks a bunch.