Mass Conversion

[+Frolickin]

I keep a database of my caching history. In it I have coordinates for all the caches I have found. I want to get these into either Mapsend or MS Streets & Trips.

 

Issues

The coordinates are stored in two fields as follows:

39° 44.062

075° 08.571

 

I have too many caches found to go back and hand-edit these to a different coordinate system, which is what I would do if I were beginning this today.

 

So, how do you recommend converting these?

 

I had hopes of exporting a list of north coordinates, west coordinates, and cache names to Excel, saving as a CSV, and then converting with GPSBabel. That is not happening without some more work being done. The conversion is where that plan broke down. Does anyone have a suggestion for mass converting the coordinates that contain the degree sign and a space?

[+NightPilot]

That should be doable in Access. I'm not that familiar with Access, but you should be able to make the changes automatically in there. If not, you can export the Access database to Excel, which certainly allows this. You just use the built-in functions to search and replace the degree symbols, etc in one cell, and then copy that cell into the entire column, and then copy by value what remains. You might have to do each replace separately, but it's not that involved. When you get everything like you want it, just save as a .csv. I regularly do conversions of this type data in Excel, converting 5000+ rows at a time. I have a template set up by this time, so all I have to do is copy the original data into the template sheet, and copy the resulting columns to a new sheet, and I'm done.

[robertlipe]

They're really in the database in that format? That'll teach you. :-) Run them through something (and this should be easy in a database) that divides the part after the degree sign by 60 and then adds the part before the degree sign.

 

If you can lean on the formatting even a little bit, you can probably get GPSBabel to treat it as a gpsutil file since it uses that DD MM.MMM format: if you can fudge them to be even:

 

GC2723 3944.062N 07508.571W 0000000m thirtybytesfollowedbyaapsce a

you could then just gpsbabel them to whatever.

 

Just doing the math in the database or coming out of the database will be easier than contriving the format., It'd take about 20 seconds to do in awk or perl but I get the feeling you don't live in a world with rich tools at your disposal so explaining the details of how to do it with those will do you no good.

 

If this is a one-shot deal and you can provide the data in a completely consistent (ZERO hand-editing required) format, I'll do it for you for the bargain price of, oh, three beers.

 

I drink stouts.

[+njload]

Hello Frolickin, have you tried GSAK? I use it and have loaded push-pins into Streets & trips, and also loaded into Mapsend using the export feature. If you have you Loc/GPX files saved to your Hard Drive its easy to load into GSAK, If not what I have done is loaded the caches in MY Watch Page and then done a pocket query to download them all.

 

I mess around with Access and will try to come up with something, but have a DR. appointment tonight and might not get to it.

 

Best Wishes

NJLOAD

[+Frolickin]

I've gotten it converted. 'Twas actually easier than I expected. The thing is, I did it in Excel. Now, wouldn't it be great to get this back to Access. I am certain I will find a way to do that.

 

I appreciate all the help.

[mloser]

Open your database in Access, select the table, clear out all the rows, and then chose File Get External Data, Import, chose files of type Excel (XLS) and import it. If the columns have the field names in them your data should be imported into the Access table with no problem.

Oh yeah, make a backup copy of the MDB file first, so when you destroy it you have something to go back to.

[+Frolickin]

Oh yeah, make a backup copy of the MDB file first, so when you destroy it you have something to go back to.

 

I've done that too many times previously. It's part of the routine!

 

Thanks.

[+NightPilot]

Access won't import Excel or .csv? I thought you could go back and forth between Access and Excel. I admit I'm not an Access kind of guy, I prefer dBase, but the Office programs are supposed to take each other's data.