tracks to Excel (?)

[boomfiziks]

I'm trying to do some spreadsheet calculations of my track logs. I'm using MapSource - City Navigator. When I look at one of my track properties, I can highlight and paste the information onto my Excel.

 

The problem is that the latitude & longitude information are in the same cell. For example, in one cell, I may have "N34 30.324 W77 25.175".

 

What I'd like to be able to do is put each bit of information into it's own cell. For example "N", "34", "30.324", "W", "77", "25.175". Does anyone know how this can be done? Typing in several thousand points along a track seems a bit tedious.

 

Thanks,

Dwight

[Dale_Lynn]

I'm trying to do some spreadsheet calculations of my track logs. I'm using MapSource - City Navigator. When I look at one of my track properties, I can highlight and paste the information onto my Excel.

 

The problem is that the latitude & longitude information are in the same cell. For example, in one cell, I may have "N34 30.324 W77 25.175".

 

What I'd like to be able to do is put each bit of information into it's own cell. For example "N", "34", "30.324", "W", "77", "25.175". Does anyone know how this can be done? Typing in several thousand points along a track seems a bit tedious.

 

Thanks,

Dwight

 

As you already know Excel wants to see some sort of "delimiter"when it imports data... When you do a "import data" and Excel does not recognise the data format it should open a window and ask you how to proceed/process imported data... Some data streams use a comma (,) as a delimiter or some other form of delimiter. You will have to determine what the delimiter is (playing with Excel formats) to get individual data elements in each row/column... IF you want to break it down further as to degrees in one field and minutes in another (and so on) you will probably have to do a lot of copy/paste/delete to format data way you want...

 

And after all that, what are you going to do with all the data "sub-elements"...

 

You probably need to find better way to corollate your data and reevaluate you though process on what you are trying to do to match your "application" to existing data elements. Other option may be to create a macro to break data elements up and populate specific cells with your "sub-elements". Know anything about Visual Basic, Excel supports macros written in Visual Basic . In fact Excel is compiled from Visual Basic source code. That Is why it will support VB sub applications

 

Dale

Edited September 1, 2006 by Dale_Lynn

[fieldkat]

Hello. I have Excel 97, so please keep that in mind if some things don't jive with your version of Excel.

 

Highlight the column with the lat/lon data. Go to Data>Text to Columns. Choose the Fixed Width radio button and click Next. Now move/add column break lines in the Data Preview window to set how you want the column broken into new columns. Click next to adjust the data format and/or click Finish.

 

...

[+PDOP's]

Convert your Garmin file to a CSV (Comma Separated Value) format with which excel will import the data into separate columns. You can use http://www.gpsvisualizer.com/convert or http://www.gpsbabel.org/ to do the conversion.

[Hertzog]

I'm trying to do some spreadsheet calculations of my track logs. I'm using MapSource - City Navigator. When I look at one of my track properties, I can highlight and paste the information onto my Excel.

 

The problem is that the latitude & longitude information are in the same cell. For example, in one cell, I may have "N34 30.324 W77 25.175".

 

What I'd like to be able to do is put each bit of information into it's own cell. For example "N", "34", "30.324", "W", "77", "25.175". Does anyone know how this can be done? Typing in several thousand points along a track seems a bit tedious.

 

Thanks,

Dwight

All the suggestions that have been given are good approaches. Another approach that I often use in Excel is to use the text processing functions in Excel to pull out the data I want; for instance, the equation:

 

=VALUE(MID(B161,12,9))

 

will pull longitude out of cell B161 which contains:

 

N33.89915 W118.36006

 

In your example with degrees and decimal minutes, something like:

 

=VALUE(MID(B161,13,2))+VALUE(MID(B161,16,6))/60

 

would pull out the longitude and convert it to decimal degrees.

[+Red90]

I'm not sure why all the complex suggestions.

 

From Mapsource, save as a "text (tab delimited)" file. Open in excel and it should all be fine.

[Dale_Lynn]

Hello. I have Excel 97, so please keep that in mind if some things don't jive with your version of Excel.

 

Highlight the column with the lat/lon data. Go to Data>Text to Columns. Choose the Fixed Width radio button and click Next. Now move/add column break lines in the Data Preview window to set how you want the column broken into new columns. Click next to adjust the data format and/or click Finish.

 

...

 

Just what I was going to suggest after playing with EXCEL a bit last night....

 

Dale

[Dale_Lynn]

<>

Edited September 1, 2006 by Dale_Lynn

[Hertzog]

I'm not sure why all the complex suggestions.

 

From Mapsource, save as a "text (tab delimited)" file. Open in excel and it should all be fine.

That puts a space rather than a tab between latitude and longitude, so he would still have to tell Excel to use spaces in addition to tabs as separators to avoid ending up with latitude and longitude in the same cell. Also, he needs to do something to separate the "N" and "E" from the numbers to work with the data as numbers.

[robertlipe]

GPSBabel can convert lots of different formats and receiver downloads into just about any something-separated format you want. The style scheme allows a large degree of customization.

 

Working with anything other than decimal degrees in a spreadsheet will make you crazy if you want to do anything "mathish" on the numbers.

[+Red90]

Use UTM and specify both tab and space as delimiters. Or after opening the files, grab the position column and go "data/text to columns" and split by spaces or fixed width.

[egorny]

Another piece of software is Nantional Geographic's TOPO GPS USA. Runs about $25 usually you can find it for $20.00.

 

We use it in classes to download GPS data (you can select waypoints or tracks or both). You can select the format you want and then export it to a txt file that brings it really nice into Excel.

 

Ed