using Excell to build the poi
Tue, 03/25/2008 - 6:12pm
![]() |
![]() 17 years
|
i used my excell sheet with addresses & paste it into this program:
http://www.batchgeocode.com/
this will generate several columns when changed text to columns
i then came accross this formula that will allow you to consolidate information from col C with col d & col e & col f
=c1&" "&" "&d1&" "&e1&" "&f1
when consolidated, then copy & special paste "value" only and the formula will not show
i just thought to share my fidings
I'll give it a try
Sounds like an idea ... I usually take my raw data and dump it into Wordpad and manually edit the quotation marks and commas. Maybe this will shorten the time and effort. Thanks!
"Life is a journey - enjoy the ride!" Garmin nuvi 255
batchgeocode
I've been using http://www.batchgeocode.com/ for creating a few POI files. They've beem mostly accurate, with only one major bust so far.
nüvi 750 & 760
marnoldi wrote: i used my
i used my excell sheet with addresses & paste it into this program:
http://www.batchgeocode.com/
this will generate several columns when changed text to columns
i then came accross this formula that will allow you to consolidate information from col C with col d & col e & col f
=c1&" "&" "&d1&" "&e1&" "&f1
when consolidated, then copy & special paste "value" only and the formula will not show
i just thought to share my fidings
You can also use a fx command in Excel (concatenate) to merge the information into one cell.
Excel to merge rows
marnoldi.what formula would I use to merge to rows together?For example only. I want to merge row c and d together.End results c and d will now contain all data as one row.
Charlie. Nuvi 265 WT and Nuvi 2597 LMT. MapFactor Navigator - Offline Maps & GPS.
FYI, Additional tips
=c1&" "&" "&d1&" "&e1&" "&f1
In addition, Add &char(10)& for a line break
=c1&char(10)&d1&char(10)&e1&char(10)&&f1
(make sure word wrap is set on the resulting field)
Here are a few more tricks:
http://www.poi-factory.com/node/10065
Garmin Nüvi 650, 255WT
Another Excel tip
Here is a little tip I use for Excel...
If C3=ADDRESS: 123 Main Street.
The fomulla below will removes the first 9 characters (ADDRESS: )from C3 so it will now read:
123 Main Street
=RIGHT(C3, LEN(C3)-9)
And another excel tip
That's a good tip (and not what one might expect: ie, use the =RIGHT function to remove the left-most characters).
Here's another way to accomplish the same thing, in both Excel or Word: use the Replace command to remove unwanted text. Eg, I had a file containing strings such as:
Tel: 555-555-5555
Tel: 555-555-5556
Tel: 555-555-5557
I was able to remove all instances of "Tel: " by Replacing "Tel: " with nothing. Result was this:
555-555-5555
555-555-5556
555-555-5557
Works in Word and in Excel.
Garmin DriveSmart 55 & Traffic | Garmin eTrex "yellow"
Keep'em coming
These are great tip and I know I appreciate them!!
It's those changes in latitudes, changes in attitudes nothing remains quite the same. With all of our running and all of our cunning, If we couldn't laugh, we would all go insane.
Free software...
Hey guys, I'm a software developer and I often write small programs to make my life easier. Is there any kind of repetitive task you do when creating your POI files that I can help with?
For example I'm using one right now to work with addresses I pulled from a particular web site.
Let me know if need anything specific. The only caveat is that I have a day job and have to do this off hours.
Pick your function.....Excel
Here is a list of available text functions:
(Make cleaning CSV file much easier)
CHAR - Returns the character specified by the code number
CLEAN - Removes all nonprintable char. from text
CODE - Returns a numeric code for the first char in a text string
CONCATENATE - Joins several text items into one text item
DOLLAR - Converts a number to text, using currency format
EXACT - Checks to see if two text values are identical
FIND - Finds one text value within another (case-sensitive)
FIXED - Formats a number as text with a fixed nbr of decimals
LEFT - Returns the leftmost characters from a text value
LEN - Returns the number of characters in a text string
LOWER - Converts text to lowercase
MID - Rtrns a nbr of chars from text strt @ the pos. you specify
PROPER - Capitalizes the first letter in each word of a text value
REPLACE - Replaces characters within text
REPT - Repeats text a given number of times
RIGHT - Returns the rightmost characters from a text value
SEARCH - Finds one text value within another (not case-sensitiv)
SUBSTITUTE - Substitutes new text for old text in a text string
T - Converts its arguments to text
TEXT - Formats a number and converts it to text
TRIM - Removes spaces from text
UPPER - Converts text to uppercase
VALUE - Converts a text argument to a number
Garmin Nüvi 650, 255WT
Free software
Hey guys, I'm a software developer and I often write small programs to make my life easier. Is there any kind of repetitive task you do when creating your POI files that I can help with?
For example I'm using one right now to work with addresses I pulled from a particular web site.
Let me know if need anything specific. The only caveat is that I have a day job and have to do this off hours.
I hope you make that program available.It sounds like it would reduce a lot of key pushing.
Charlie. Nuvi 265 WT and Nuvi 2597 LMT. MapFactor Navigator - Offline Maps & GPS.
Suggestions needed
I hope you make that program available.It sounds like it would reduce a lot of key pushing.
I threw it together in a few hours to address the raw data I get from a specific web site, but I'd be willing to make a more generic one for anyone to use. Feel free to let me know what you would like it to do (e.g. use specific delimiters, toggle quotation marks, format it for a specific batch geocoder, etc.)
I'm green with respect to the whole navigation thing so I need the input. I've had my gps about a week and I've been on this site for less than that. I just like this whole POI concept and want to help the community if I can.
Axxra. I believe you will
Axxra. I believe you will get lots of suggestions.A lot of time is spent with members making their own custom poi files.It seems by posts most use excel or notepad to organized the data taken from web sites. I use site http://www.gpsvisualizer.com/geocoding.html for single or batch address.It sounds like you have a pretty good handle already on the POI concept and have covered a lot of the needs as stated in your orginial post.
Welcome to the site.Lots of good people here.
Charlie. Nuvi 265 WT and Nuvi 2597 LMT. MapFactor Navigator - Offline Maps & GPS.