Populating a Streets & Trips map with searched waypoints

Here's a quick summary of my new task:

Just last week we where approved to sell to a well known nationwide chain of stores. The chain is searchable on Google Maps, Bing, and even has a website with all of their locations listed.

I have the task of plotting all of these stores into my map.

Do you have any idea how to do this without spending an entire day on it?

I am looking at Bing.com. If you populate a map with waypoints, it gives you the option of exporting them to kml file or gpx.

This could work, although might still be fairly time consuming.

Does the store have a location list you could copy to an Excel file?

You should be able to do the same search in Google Earth that you did in Google Maps and save the results to a .kml file. Or use the gpx or kml file save option from Bing if that's easier.

Use your favorite search engine and look for converters that will convert from kml or gpx to Excel for import to S&T. You can get utilities like GPSBabel that run on your PC and you can also find sites that you can use online.

Or as Tomrod suggests, it might be even easier to cut/paste or import the file list from the chain's web site into an Excel file.

Something to think about if you have difficulty cutting and pasting from the web site directly into Excel is that it might be easier as a two-step process. Sometimes Word understands HTML better and will accept a paste from HTML more cleanly. So it might be easier to cut/paste into Word and then move it into Excel (cut/paste or export/import). When you cut/paste from a web page into Word or Excel, don't be disturbed if nothing happens for a couple of minutes. There is some internal conversion and formatting going on. It sounds a lot more complicated than it will probably be.

...ken...

Thanks a ton for the input.

I was able to map out all the southern California map way points @ Bing.com which took about 20 mins. now im going to try the Babel software.

It seems that if I where to try to cut and paste from Word to Excel it would still take hours or even days to get through the hundreds of store locations.

I would love to use the addresses given by the store chains website, since that most likely has the most accurate information but i don't know if i could maintain sanity copy pasting all the values one at a time.

I don't understand why you would copy/paste them one at a time. Could you give us an example, or a link to the appropriate page on the web site? I would select the whole works at once, or at least an entire page at a time for the copy/paste. That's why I mentioned that this might work better as a multi-step process, copy/pasting into Word first and then either copy/paste or export/import (say, to tab delimited) to Excel.

A link to a web page with the address list would be useful if you want some detailed help. I can't imagine why you would be forced to do the copy/paste one store at a time, but some web sites are set up in strange ways that might prevent doing a mass copy.

...ken...

When I try to pull information from a website like the one below in Excel it is not in a format that will be recognizable by Streets & Trips. For S&T to understand I would need to separate the data into columns with headings that match S&T import columns(lots of copy/pasting).
If you know an easier way, please tell me.

http://www.vitaminshoppe.com/content/en/stores/stores.jsp;jsessionid=3J4TJYFYZN532CQUC4WVAFQKCQL1AUNE?lstState=CA

Thank you very much Ken.
Jesse

Okay, I see what you mean ... not obvious how it can be done easily. I'll see if I can make some sense out of it but no promises. It looks like it may be more productive to save the Bing search results as kml or gpx and then use GPSBabel or similar to convert it to something for S&T.

...ken...

I did some messing around and it's possible to get the data into S&T from the list on the web page but it may be more trouble than it's worth. You can copy all the locations from that California page and paste them into Excel in one shot. But it all ends up in a single column which isn't very useful.

You can transpose the locations from rows to columns but you have to do that one location at a time. You select all the rows for a single location, Ctrl-C (or Edit/Copy), click to a different sheet, right-click in the next row in Column 1, click Paste Special, click Transpose.

That takes one location and swings the rows into columns. You would put a column name for each column in the top row. When you're done, you can delete the columns you don't need, delete the sheet you pasted the original data into and save the "coumnized" file.

When the import data wizard runs in S&T it will see the column headings in the top row and use the data accordingly.

If you think this is actually useful and need more details just holler.

...ken...

I was exausted after copy/transposing 10 of the contacts in the excel file.
I went back to the list of stores i had saved as waypoints in bing.com and exported the list. I downloaded gpsbabel and converted all the waypoints.
mission accomplished, or it appears at the moment.

I really appreciate the help Ken

I'm glad you were able to find something you can work with. That chain has a mess of stores across the US so you definitely need an approach that gets you the most stores for the least work.

That approach should also work with Google if you find that you get a more comprehensive list there. I don't think you can save anything with Google Maps, but you can download Google Earth for free and do the same searches that you can do in Google Maps. Then you can save the results in a .kml file and do the same conversion for S&T.

That's not a recommendation; simply another option, depending on which search site gives you the better results.

...ken...

-Update

When i create pushpin sets from bing or google earth, the pushpins are incomplete or not up to date with the newest stores. Also, when i import the kml files from bing or GE, the address and phone does not import.

Does anyone know how to extract contacts from a websites store locator page http://www.nutrishopsuperstores.com/find-nutrishop ?

I would like to create seperate pushpin sets for all the major chains of stores that our company does business. All of these chains have a store locator page or a store listing page.

Jesse

Jesse,

I think you should stop banging your head against that particular wall. Call Nutrishop and ask to talk to the person who maintains the list of all their store locations. Ask that person if they can drop you an Excel file or .CSV file in the format you need to import for complete pushpin use.

When I was looking for all the Superstore gas bar locations in western Canada I emailed customer support and asked for a list. A nice lady was back to me in about 48 hours with a complete list, including marking all the ones that carry diesel (I didn't ask). If they'll do that for a customer, I'm sure that as someone they do business with you should be able to get at least that much from them.

...ken...