using Excell to build the poi

 

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

--
marnoldi Nuvi 680 & Nuvi 2797LMT

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

marnoldi wrote:

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. Android Here WeGo - Offline Maps & GPS.

FYI, Additional tips

marnoldi wrote:

=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 nüvi 250W | Garmin eTrex "yellow" | TomTom GO 910

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.

  • It removes blank lines
  • Extracts specific data I don't need
  • Separates the Title,Address,city,state,zip
  • Removes duplicates
  • and Spits out formated text

    Let me know if need anything specific. The only caveat is that I have a day job and have to do this off hours. rolleyes

  • 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

    Axxra wrote:

    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.

  • It removes blank lines
  • Extracts specific data I don't need
  • Separates the Title,Address,city,state,zip
  • Removes duplicates
  • and Spits out formated text

    Let me know if need anything specific. The only caveat is that I have a day job and have to do this off hours. rolleyes

  • 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. Android Here WeGo - Offline Maps & GPS.

    Suggestions needed

    charlesd45 wrote:

    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. Android Here WeGo - Offline Maps & GPS.