Need help finding some converson software

[syphen6]

Im using a Leica RTK for work and when I output the file it comes in this format

 

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

 

I have to manually make it look like this 0001,41 34 11.802267,-87 27 58.845330,487.0977114121,

 

The first row is point ID. Usually I have over 300 points per jobsite and after I do that I import it into a excel file and use earthpoint.us to convert it into KML. Its very tedious and I have tried to convert it with Corpscon but I cant get it to work. Just wondering if anyone knows of any easier way to do this. thanks

[+Lil Devil]

...Leica RTK...

 

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

 

I have to manually make it look like this 0001,41 34 11.802267,-87 27 58.845330,487.0977114121,

 

Wow. Using professional grade survey equipment and you actually think that is a valid conversion.

 

GPS Babel can probably do a correct conversion for you, but I haven't used it on that specific file type.

[+Team CowboyPapa]

You should be able to construct formulas in Excel to perform the conversions automagically. I normally construct them in adjacent columns for the first row of data. Then selecting the cells with the formulas, I hold while dragging down for all rows.

 

Actually, my preferred choice is to make macros, which are chunks of code, to accomplish that; however, that requires the Professional edition of Excel.

[+dakboy]

You should be able to construct formulas in Excel to perform the conversions automagically.

Assuming that Excel is accurate enough

[syphen6]

what would a valid conversion be ? The guy that set it up for us made it output this way and when you put it directly into Google earth it won't work. I have tried to use excel to make a macro but I can't seem to get it to work. Also none of this data is sent to the client this is basically just for a database of jobs in Google earth

 

Thanks

[team tisri]

what would a valid conversion be ? The guy that set it up for us made it output this way and when you put it directly into Google earth it won't work. I have tried to use excel to make a macro but I can't seem to get it to work. Also none of this data is sent to the client this is basically just for a database of jobs in Google earth

 

Thanks

 

I'd expect your source file to contain decimal degrees (i.e. dd.dddddddddd format) and it looks like you're trying to convert into degrees minutes and seconds format (i.e. dd mm ss.sss)

 

It's not difficult to do the conversion, but if I'm right about what you're trying to do then 40.50000000 would convert to 40 30 00.000 rather than 40 50 00.000

 

If this is a professional job you can probably find someone to write you a macro for a fairly modest fee. It doesn't look like a difficult job, but I'd be surprised if very many people would spend too much time writing something that's apparently for commercial use for free, so someone else can make money out of it.

[+Team CowboyPapa]

You should be able to construct formulas in Excel to perform the conversions automagically.

Assuming that Excel is accurate enough

Reread the issue with all the precision that Excel may lack. It has nothing to do with calculations. It is posed as strictly transposing numbers, rearranging their left to right order, and has nothing to do with adding, subtracting, multiplying or dividing. Consequently, none of that Wikipedia stuff applies.

 

Outside of that, I look forward to the next lecture.

[+Team CowboyPapa]

what would a valid conversion be ? The guy that set it up for us made it output this way and when you put it directly into Google earth it won't work. I have tried to use excel to make a macro but I can't seem to get it to work. Also none of this data is sent to the client this is basically just for a database of jobs in Google earth

 

Thanks

10-4, in Excel, Insert, Functions, Text category. Those functions can be used to do the character string manipulations required to generate the desired result described in the opening post.

 

Mathematical calculations play no role in the given example; consequently, the precison limitations discussed in the Wikipedia link are irrelevant and inapplicable to this example.

[+Team CowboyPapa]

....

 

If this is a professional job you can probably find someone to write you a macro for a fairly modest fee. It doesn't look like a difficult job, but I'd be surprised if very many people would spend too much time writing something that's apparently for commercial use for free, so someone else can make money out of it.

I agree with the issue of this being a professional job. For this task, I will make an extremely generous offer of a consultancy fee not to exceed $5,000 (on the basis IRS Form 1099).

[John E Cache]

Im using a Leica RTK for work and when I output the file it comes in this format

 

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

 

I have to manually make it look like this 0001,41 34 11.802267,-87 27 58.845330,487.0977114121,

 

The first row is point ID. Usually I have over 300 points per jobsite and after I do that I import it into a excel file and use earthpoint.us to convert it into KML. Its very tedious and I have tried to convert it with Corpscon but I cant get it to work. Just wondering if anyone knows of any easier way to do this. thanks

This csv file works on earthpoint.us and puts a point named 0001 on a farm south of Lake Michigan,

 

RTK.csv

Name,Latitude,Longitude,Altitude,a,b,c
0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

[MikeToCache]

To really validate this it would be good to have some known data point - that is a the data from the GPS along with the actual location on the map it relates to.

I can see more than one way to interpret that value - the first post suggested one way - was he certain that was the way to process it?

Doing it the way suggested could be correct, or perhaps it is a bit off from the right location.

While the farm suggested by someone might be the correct interpretation, I did the calculation another way and came up with a point near a railroad and visitors center.

Having worked with several GPS models, you need to do a calibration to ensure you are making the co-ordinate conversion the same way the manufacturer had in mind.

I recently corrected a similar flaw in some other software where the data looked similar to what it should be but was all shifted in position.

[syphen6]

Im using a Leica RTK for work and when I output the file it comes in this format

 

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

 

I have to manually make it look like this 0001,41 34 11.802267,-87 27 58.845330,487.0977114121,

 

The first row is point ID. Usually I have over 300 points per jobsite and after I do that I import it into a excel file and use earthpoint.us to convert it into KML. Its very tedious and I have tried to convert it with Corpscon but I cant get it to work. Just wondering if anyone knows of any easier way to do this. thanks

 

This csv file works on earthpoint.us and puts a point named 0001 on a farm south of Lake Michigan,

 

RTK.csv

Name,Latitude,Longitude,Altitude,a,b,c
0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

 

When entering the GPS data like this 34 11.802267,-87 27 58.845330 into Google earth it should put you right near a levee by the Little Calumet River which is about 18 miles away from where the original unaltered RTK data is.

[xyzee]

41.3411802267,-87.2758845330

 

Convers to:

41 20 28.24882

87 16 33.18432

 

Corpscon>Help>Deg-Min-Sec Converter

[MikeToCache]

I believe once properly converted this would put the location in dd.dddddddddd format as follows:

 

Lat: 41.569°

Lon: -87.466°

 

This is behind the vistor's center, at a rail bridge, hydro lines, a zig zaggy barrier of some sort.

 

"near a levee by the Little Calumet River which is about 18 miles away..."

 

I wrote a formula for this a while back - needs a few multiply/divide if i recall.

[syphen6]

41.3411802267,-87.2758845330

 

Convers to:

41 20 28.24882

87 16 33.18432

 

Corpscon>Help>Deg-Min-Sec Converter

 

Yes I can get it to work manually with Corpscon but when trying to make a txt file to do the batch conversion it doesn't work right. It puts my points over in Africa lol.

[John E Cache]

41.3411802267,-87.2758845330

 

Convers to:

41 20 28.24882

87 16 33.18432

 

Corpscon>Help>Deg-Min-Sec Converter

 

Yes I can get it to work manually with Corpscon but when trying to make a txt file to do the batch conversion it doesn't work right. It puts my points over in Africa lol.

You don't have to convert numbering systems. Decimal works fine on earthpoint.us. When I misspelled latitude, earthpoint.us put me in Africa and added an error message to the kml. You have to open the kml file to get the error message. Surveyors use a different coordinate system(NAD27) than Google Earth(WGS84). Have you tried using Corpscon to convert nad to wgs. What does Google Earth say when you mouse over where you took the measurement?

[+Hynr]

syphen6,

 

you should be heeding Lil Devil's suggestion.

 

Take a look at this page related to GPSbabel:

http://www.gpsbabel.org/htmldoc-development/fmt_unicsv.html

 

Note also that Google Earth uses GPSbabel in the background to read in data. So you can be pretty sure that its conversion from your particular csv to kml will result in joy. It just not as simple as you might like (but neither is coding the conversion yourself with Excel).

 

If you want more help here, (i.e. to give you the complete command line and supporting style files (if needed)), then post a complete test file and also tell us what all the fields mean.

 

Just off the top of my head, if you have a file like this (test.csv)

Name,Latitude,Longitude,alt,avar,bvar,cvar

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

0002,41.9411802267,-87.2411802267,487.0977114121, 12, 10, 14

0003,42.0411802267,-87.4411802267,488.0977114121, 12, 10, 14

0004,42.5411802267,-87.6411802267,489.0977114121, 12, 10, 14

0005,43.0411802267,-87.8411802267,487.0977114121, 12, 10, 14

 

Then you command line might look like this (edit this in to a file that has extension .bat and run it):

"C:\Program Files (x86)\GPSBabel\gpsbabel.exe" -i unicsv -f "C:\Users\xxxxx\Desktop\test.csv" -o kml -F "C:\Users\xxxxx\Desktop\test.kml"

pause

(xxxxx would be your windows user name)

Enjoy!

[syphen6]

syphen6,

 

you should be heeding Lil Devil's suggestion.

 

Take a look at this page related to GPSbabel:

http://www.gpsbabel.org/htmldoc-development/fmt_unicsv.html

 

Note also that Google Earth uses GPSbabel in the background to read in data. So you can be pretty sure that its conversion from your particular csv to kml will result in joy. It just not as simple as you might like (but neither is coding the conversion yourself with Excel).

 

If you want more help here, (i.e. to give you the complete command line and supporting style files (if needed)), then post a complete test file and also tell us what all the fields mean.

 

Just off the top of my head, if you have a file like this (test.csv)

Name,Latitude,Longitude,alt,avar,bvar,cvar

0001,41.3411802267,-87.2758845330,487.0977114121, 12, 10, 14

0002,41.9411802267,-87.2411802267,487.0977114121, 12, 10, 14

0003,42.0411802267,-87.4411802267,488.0977114121, 12, 10, 14

0004,42.5411802267,-87.6411802267,489.0977114121, 12, 10, 14

0005,43.0411802267,-87.8411802267,487.0977114121, 12, 10, 14

 

Then you command line might look like this (edit this in to a file that has extension .bat and run it):

"C:\Program Files (x86)\GPSBabel\gpsbabel.exe" -i unicsv -f "C:\Users\xxxxx\Desktop\test.csv" -o kml -F "C:\Users\xxxxx\Desktop\test.kml"

pause

(xxxxx would be your windows user name)

Enjoy!

 

The RTK puts out 2 txt files one is in this format

 

0001,39.3231368977,-87.2453287598,364.8099032526, 10, 10, 12

 

0002,39.3231159504,-87.2453061453,366.7054997960, 10, 10, 13

 

0003,39.3231180181,-87.2453007038,367.1624548425, 10, 10, 13

 

0004,39.3231173336,-87.2452870612,379.4260684910, 10, 10, 13

 

0005,39.3231168274,-87.2453155922,371.9590889002, 10, 10, 13

 

0006,39.3231165771,-87.2453289599,364.9813283241, 10, 10, 13

 

0007,39.3231165122,-87.2453428051,364.6246366317, 10, 10, 13

 

0008,39.3231126073,-87.2453450409,364.7770301125, 10, 10, 13

 

0009,39.3231088126,-87.2453486490,365.0416833084, 10, 10, 13

 

The other is a text file with the labels

 

0001,5013.159,4881.161,3.302,WS,

0002,4991.963,4898.877,5.198,WS,

0003,4994.055,4903.140,5.655,PIPE, 21

0004,4993.363,4913.828,17.918,PIPE, 21-7

0005,4992.851,4891.476,10.451,PIPE, 19-11

0006,4992.598,4881.004,3.473,PIPE, 20-2

0007,4992.532,4870.157,3.117,HB, 21-5

0008,4988.581,4868.406,3.269,HB PF,

0009,4984.741,4865.579,3.534,HB PF,

 

I usually manually convert the first file with a txt editor then I import both files into excel and copy and paste the labels over to the GPS points. Then I label it for earth point in this format :

Description , Latitude , Longitude , Elevation , Icon , Name, Depths , AppendDataColumnsToDescription

[+Team CowboyPapa]

syphen6,

 

Referring to your opening post with before and after example, only one of two possibilites:

 

1. Character String Manipulations: I see only a rearrangement of the numerical characters. I infer that you performed such character string operations such as Selecting, Deleting, Cutting, Pasting, etc., on the before character string to produce the after. (No mathematical calculations.)

 

2. Mathematical Calculations: I do not see that the after has been a result of perfoming arithmetic operatons,i.e, addition, subtraction, multiplication or division, on the before, which would be expected if you were converting from the NAD27 datum to WGS84 or elevation values from feet to meters.

 

My inference from the first post and the one immediately above is that you are performing operations such as those described above as 1. Character String Manipulations.

 

If so, that can be accomplished by means of Excel functions, macros and Visual Basic for Applications (VBA) code. As a reference source, I suggest this:

http://www.amazon.com/Visual-Basic-Applications-Unleashed-McFedries/dp/0672310465/ref=sr_1_1?s=books&ie=UTF8&qid=1351898227&sr=1-1&keywords=visual+basic+for+applications+unleashed

 

Allow me to restate, there is no fuzz on this, it can be done. When fully implented, all one has to do is perform a combination keystroke such as Ctrl-Shft-J and all three hundred rows will be rearranged into any combination of modified character strings spanning whatever columns that one desires. Also note that the VBA application cannot be purchase over the counter at a store such as Staples or Fry's. It is only available bundled with Microsoft Office Professional Edition:

http://www.amazon.com/Microsoft-Office-Professional-2010-Version/dp/B0036Z0NW6/ref=sr_1_7?s=software&ie=UTF8&qid=1351900146&sr=1-7&keywords=microsoft+office+2010

 

Furthermore, if the process includes both types 1 and 2 described above, VBA functionality will provide for both.

 

There are other applications, such as GPSBabel suggested in a post above, which may also yield the desired result; however, I have no hands-on experience with those whatsoever and therefore I cannot make a judgement as to their applicability.

 

Good luck.

Edited November 3, 2012 by Team CowboyPapa

[+Hynr]

OK. I think I am getting the picture, but I am still a bit unclear on what the labels are that you want displaying in Google Earth.

 

It was mentioned rather subtly above, but your manual conversion is wrong (you cannot simply insert spaces to get other formats). So don't let anyone in your firm see this thread. The basic point is that you need not do latitude and longitude conversions at all, since they are already in a format that is preferred by Google Earth. If you do decide to code the kml directly, then note that GE wants to see longitude before latitude in coordinates and also wants a third number (elevation).

 

If I were doing this project and wanted to avoid reinventing the process each time, then I would do it as follows: Open Excel and load the file with coordinates into Sheet1 and load the file with the labels into Sheet2. (Since you are not showing a header row, I am left to assume there are none). On Sheet3 I would code things as follows:

Header row would be in A1 to E1 (to please GPSbabel): ID,Latitude,Longitude,alt,Name

(alt stands for "altitude")

Cell A2 holds the formula +Sheet1!A1

that is copied to B2, C2, and D2 with relative addressing

Cell E2 holds this formula:

=Sheet2!E1&"-Icon"&TEXT(Sheet1!E1,"##")&"-"&TEXT(VLOOKUP(A2,Sheet2!A:F,2,0),"####.#")

generating this string of characters:

WS-Icon10-5013.2

You will have to modify this formula to assemble your label as you want it (I have given you some functions that you can study the help on if you are not familiar with them).

If it is too cryptic for you, then you should probably hire a consultant.

Row 2 is copied down as far as needed

Save this as an xls or xlsx file.

 

Now with Sheet3 showing, save to csv format. This process subjects you some questions. During that inquisition you take stock in the fact that you have already saved the file as native Excel (for future use) so you will not loose anything by doing the CSV save of just Sheet3. Close Excel after this save.

 

This file can be converted by GSPbabel as I showed in my earlier post.

 

Every time you do this in the future, all you do is the importing of the two files into Sheet1 and Sheet2 and copy row 2 on Sheet3 down as far as needed; save to csv; convert with GPSbabel, drag and drop into GE.

 

Since we are beyond the domain of anything relevant to geocaching, please contact me by email with further questions.

[syphen6]

OK I'll try this out thanks a lot Hynr. Also I am told by my boss to put in the spaces in the coordinates. That's the way he wants it.

[John E Cache]

OK I'll try this out thanks a lot Hynr. Also I am told by my boss to put in the spaces in the coordinates. That's the way he wants it.

Thanks for the post. I find learning a different terminology interesting. I figured out the second file is an X-Y format in feet referenced to some unknown point. I wonder if you change the reference so it spits out UTM X-Y coordinates? The web page does UTM "Position".

[+Hynr]

OK I'll try this out thanks a lot Hynr. Also I am told by my boss to put in the spaces in the coordinates. That's the way he wants it.

The concern nearly everyone posting has is that you showed us in your first post that you are converting latitude "41.3411802267" to "41 34 11.802267" (i.e by literally inserting spaces and consequently moving the waypoint - doing literally what your boss is asking you to do) instead of "N41 20 28.24882" (which converts to the format by making calculations, leaving the waypoint in the same spot - which is probably what you boss actually wants). You can see the needed formulas here: http://en.wikipedia.org/wiki/Decimal_degrees

[John E Cache]

OK I'll try this out thanks a lot Hynr. Also I am told by my boss to put in the spaces in the coordinates. That's the way he wants it.

The concern nearly everyone posting has is that you showed us in your first post that you are converting latitude "41.3411802267" to "41 34 11.802267" (i.e by literally inserting spaces and consequently moving the waypoint - doing literally what your boss is asking you to do) instead of "N41 20 28.24882" (which converts to the format by making calculations, leaving the waypoint in the same spot - which is probably what you boss actually wants). You can see the needed formulas here: http://en.wikipedia....Decimal_degrees

I saw the light when I put the raw numbers in the web page(see above). The web page does the exact conversion you suggest.and goes to wrong spot on Google Earth. The raw numbers are not decimal and it is not a decimal point. It is a sexagesimal point. The web page needs spaces between the sexagesimal digits(0-59 for each digit). The OP was exactly right about the conversion and I am a little embarrassed about arguing. Sorry 'bout that.

 

I wrote a Perl script along time ago to generate a Garmin POI file with mountain peaks in OR and WA from GNIS data that had no spaces, either. New to OR, I was always asking, "what mountain is that?". I just wrote a very small Perl script that does the RTK conversion. I can post it if anyone wants it.