GPX files from CSV using Extra_POI_Editor
 15 years
|
Kudos to turboccc for the Extra_POI_Editor, I really love the features.
One of the nicest features I have found is the ability to add additional fields to a CSV file and have Extra_POI_Editor pick up these fields and populate additional GPX fields. (I know "GPX field" is probably technically incorrect, but most would get what I'm trying to ssay).
So this weekend I took some time, downloaded the crackerbarrel POI from the website, and added new columns E thru G, J and K which relate to Address, City, State, Telephone and extra information. These columns I populated by using XL functions that extract the data from column D from the base CSV file. Columns H, L, M and N are populated with constants for Country, Link, Display, and Proximity. Lastly col I is for the ZIP code, which I started updating.
Under "Options -> csv fields" in Extra_POI_editor I set column 4 to not used, and Columns 5 thru 14 to the new columns above. Then I loaded the new CSV file and Viola! All the fields display nicely in the correct place. From this I could create a GPX file for my garmin, or an OV2 file for my TOMTOM
Heres a sample of the functions for row 1 columns E F and G.. (Address, City and State)
Address - "=RIGHT(LEFT(D1,FIND(">",D1)-1),(FIND(">",D1)-5))"
City - "=RIGHT(LEFT(D1,(FIND("-",D1)-1)),FIND("-",D1)-5)"
State - "=RIGHT(LEFT(D1,3),2)"
Easier Formulas
For State, try this:
=MID(D1,2,2)
Anytime you can make the formulas less complex, the recalculation will be faster. (Granted, with a small data set, you probably won't see a difference, but Excel won't have to work as hard.)
Since I don't know what your raw data looks like, I can't easily extrapolate updated functions for Address or City, but if you're using RIGHT and LEFT in the same function, you can replace them with MID.
Easier yet
Easier yet is to use Excel's built-in Text to Columns "function" under the Data menu. Normally you can select an entire column at once and use the wizard that comes up to specify delimiters as well as which cell you want to put the left-most of the resulting parsed columns.
I say normally because you'll find that the data in the delimited files is all over the map (pardon the pun), depending on the authors preference for sequence as well as delimiter character. The Cracker Barrel file is a good example of that for sure!
-92.395,34.72804,Cracker Barrel,"""AR: Little Rock-2618 S Shackleford Road>(501) 225-7100 I-430 Exit #5"
That's the unedited first line!
It's about the Line- If a line can be drawn between the powers granted and the rights retained, it would seem to be the same thing, whether the latter be secured by declaring that they shall not be abridged, or that the former shall not be extended.
Sometimes useful
The Text to Columns feature is useful if you have consistently delimited data, but you're right that it would be useless with the Cracker Barrel data.
Like I tell trainees: Know how to do it yourself before you rely on automation to do it for you - and possibly take your job at the same time!
Somewhat simplified.
I must have looked at Mid about a dozen times, and for some reason ended up discounting it everytime! Thanks for pointing that out.
Of course something to remember is that this really only needs to be run once on the file, and also, once the file is saved back into .csv format for extra_poi_loader all the functions are replaced with the actual resultant data!
The idea was to show that the somewhat "raw" column D CSV data can be transformed for use in GPX named fields by specifying additional csv columns in Extra_POI_loader. Each POI CSV file to be converted would have to be evaluated, and modified based on the raw col D data.
heres the simplified functions for the crackerbarrel row 1
address - "=MID(D1,6,(FIND(">",D1)-6))"
City - "=MID(D1,6,(FIND("-",D1)-6))"
State - "=MID(D1,2,2)"
Tel # - "=MID(D1,(FIND(">",D1)+1),14)"
Extra info - "=RIGHT(D1,(LEN(D1)-(FIND(">",D1)+15)))"
The Extra info still needs some work, because it includes the final quote (").
Strip the quotes first
I'd strip them out first since they're of no use in Excel itself. You can use SUBSTITUTE to do it.
We don't eat at Cracker Barrel based on some of their past policies, so I'd never looked at the file, but seeing that first row made it a challenge. If you'd like, you get the Excel file I created to parse it here:
http://www.jcomtraining.com/personal/poi/cracker%20barrel%20...
Good job!
Hi jefferbob,
You did a nice job on this file. Using Extra_POI_Editor, it becomes very easy to do a GPX file using the different columns. It is a very good file to start with.
Hi zierk0310,
It seems you did something similar. Please continue to post in the other Extra_POI_Editor thread so I can get the feedback and resolve your issues.
Turbo