Convert your Contacts(or any address list) to Dialable POI's

Folks,
I originally posted this on the Zumoforums, but it may be more appropriate here. If anyone has questions, please feel free to post. I will answer them the best I can.
-Joe

Seven “easy” steps to convert your Outlook Contacts into Dialable GPS POI's

Prerequisites:
Clean Data!
MS Access (or other Database program that you know)
GPS Visualizer’s Easy Batch Geocoder located At http://www.gpsvisualizer.com/geocoder/
A Text editor (such as Crimson Editor (www.Crimsoneditor.com )
Microsoft XML Notepad 2007 (free tool for checking your XML output file if needed)

1. Export Data from Outlook
a. Verify that all address fields are filled out
b. Do NOT use commas (,) in any Company, address or name field
c. Do NOT use ampersands (&) as they sometimes cause issues
d. Separate contacts to another contact folder if desired
i. Highlight desired contacts and choose “Copy to folder” from the “File” menu
ii. Save contacts and name new folder
e. Select Folder and export to CSV file
i. “File”, “Import and Export”, Export to a file, “Next”,
Comma Separated Values (DOS), “Next”, Highlight folder to be exported, “Next”, Name file, “Next”, ”Map Custom Fields”, “Default Map”, “OK”, ”Finnish”.
2. Import Contacts to Access
a. Open Access, “File”, ”Get External Data”, “Import”
b. Choose Text Files from the “Files of Type” dropdown and go get file you created in prior step.
c. Choose Delimited, “Next”
d. Select “First Row Contains Field Names, Text Qualifier of “ and , as the delimiter, “Next”.
e. Select New Table, “Next”.
f. Allow Access to add a primary Key, “Next”.
g. Rename the Table name to Contacts and overwrite if asked (if you are refreshing you contact base), “Next”.
3. Export Contacts to be geolocated with the GPS Visualizer’s Easy Batch Geocoder
a. Highlight the “Contacts for Location Export” Query.
b. “File”, ”Export”, Choose Text files from the “Save as Type” dropdown, choose a location and click “Export”. (Overwrite if refreshing contacts)
c. Choose delimited, “Next”
d. Select “First Row Contains field Names”, Comma (,) delimited, but NO text Qualifier (the GPS Visualizer’s Easy Batch Geocoder does not use them), “Next”
e. Name the file, Finnish. Overwrite if refreshing contacts.
4. Use the GPS Visualizer’s Easy Batch Geocoder to decode you exported list of contacts
a. Open the Text file called “Contacts for Location Export.txt” you created in the prior step
b. Highlight all text by opening the file and using the “edit”, “select all” command.
c. Copy all text using the “edit”, “Copy”
d. Navigate to the GPS Visualizer’s Easy Batch Geocoder located At http://www.gpsvisualizer.com/geocoder/
e. Paste the text into the Input field in the geocoder selecting the following options
i. Select Tabular from the “type of Data” drop down
ii. Choose SemiColon ( from the “Field Separator in Output” Dropdown
iii. Click the “Start Geolocating” button and let er rip!
f. Select all the text from the output field and rightclick copy.
g. Create or open a new text document called “Geolocated Contacts.txt”
h. Paste in the text from the results window (overwrite if refreshing contacts)
i. Save the file.
5. Import Geolocated Contacts into Access
a. If refreshing data, delete the table named Geolocated Contacts from the database.
b. Open Access, “File”, ”Get External Data”, “Import”
c. Choose Text Files from the “Files of Type” dropdown and go get file you created in prior step. (Geolocated Contacts.txt) Hilight the file and click “Import”
d. Choose Delimited, “Next”
e. Select “First Row Contains Field Names, No Text Qualifier and Semicolon ( as the delimiter, “Next”.
f. Choose “In a new table” radio button , “Next”.
g. Click “Next” again.
h. Select the “Choose my own primary key” radio button and select “ID” from the dropdown list. Click “Next”.
i. Confirm Table name of Geolocated Contacts and choose “Finnish”
6. Export Contact data in XML Code
a. Run the report called “Generate Text for XML document”.
b. With the report displayed, click “File”, “Export”.
c. Choose Text Files from the “Files of Type” dropdown and name the file “Generate Text for XML document.txt” (Overwrite if refreshing Contacts), “Export”
d. Choose Windows radio button. “OK”
e. Go get the file you created and open it. (Generate Text for XML document.txt)
f. Click in front of the first character in the document and close up all the spaces at the top of the document. (This is important as the first line of test needs to be on the first line for the GPX format to work.)
g. “File”, “Save”, “Close”
h. Rename the Document from “Generate Text for XML document.txt” to ” Generate Text for XML document.GPX”.
i. Copy this document and place it in a subfolder in your Garmin Custom POI’s folder.
7. Connect your Garmin and run the POI loader. It should see the file and upload your new Dialable Waypoints. Enjoy!

NOTES:
1. For my examples, I wanted the contact’s Home location even if I had a business address too. (Unless all I had was the company address) I also selected other phone numbers depending upon if certain ones were not available. You can tweak the Query to meet your needs as you see fit.
2. I also need to get the XML code so that I can display multiple phone numbers. I know this is possible, I just didn’t have the time to include it in this initial process. If anyone has this info, please post it.
3. Make a custom BMP file (22x22 pixels) and throw it is the same directory as the final GPX file and you will have a custom look at all your contacts. Without a custom BMP, they show up as grey dots.
4. Thanks to Motorcycle Mama and v900rider for pointing me in the right direction, thanks to Brian for the great post on Dialable POI's and the folks at GPS Visualizer for putting out a great, useful free tool!

Access Database Info: (2 Queries, 1 report)

Query 1: Contacts for Location Export

SELECT IIf([Home Street] Is Null,IIf([First Name] Is Null,[Company],[First Name] & ' ' & [Last Name] & ' at ' & [Company]),IIf([First Name] Is Null,[Company],[First Name] & ' ' & [Last Name])) AS Name, IIf([Home Street] Is Null,[Business Street],[Home Street]) AS Street, IIf([Home Street] Is Null,[Business City],[Home City]) AS City, IIf([Home Street] Is Null,[Business State],[Home State]) AS State, IIf([Home Street] Is Null,[Business Postal Code],[Home Postal Code]) AS Zipcode, Contacts.ID
FROM Contacts
WHERE (((Contacts.[Business Street]) Is Not Null)) OR (((Contacts.[Home Street]) Is Not Null))
GROUP BY IIf([Home Street] Is Null,IIf([First Name] Is Null,[Company],[First Name] & ' ' & [Last Name] & ' at ' & [Company]),IIf([First Name] Is Null,[Company],[First Name] & ' ' & [Last Name])), IIf([Home Street] Is Null,[Business Street],[Home Street]), IIf([Home Street] Is Null,[Business City],[Home City]), IIf([Home Street] Is Null,[Business State],[Home State]), IIf([Home Street] Is Null,[Business Postal Code],[Home Postal Code]), Contacts.ID;

Query 2: Contacts With GPS Locations

SELECT [Geolocated Contacts].latitude, [Geolocated Contacts].longitude, [Geolocated Contacts].Name AS WP_Name, [Geolocated Contacts].Street, [Geolocated Contacts].City, [Geolocated Contacts].State, [Geolocated Contacts].Zipcode, [Geolocated Contacts].desc, IIf([Home Street] Is Null,[Business Phone],IIf([Home Phone] Is Null,IIf([Mobile Phone] Is Null,[Business Phone],[Mobile Phone]),[Home Phone])) AS Phone
FROM [Geolocated Contacts] INNER JOIN Contacts ON [Geolocated Contacts].ID = Contacts.ID
WHERE ((([Geolocated Contacts].latitude)<>0))
GROUP BY [Geolocated Contacts].latitude, [Geolocated Contacts].longitude, [Geolocated Contacts].Name, [Geolocated Contacts].Street, [Geolocated Contacts].City, [Geolocated Contacts].State, [Geolocated Contacts].Zipcode, [Geolocated Contacts].desc, IIf([Home Street] Is Null,[Business Phone],IIf([Home Phone] Is Null,IIf([Mobile Phone] Is Null,[Business Phone],[Mobile Phone]),[Home Phone]));

Access Report: Generate Text for XML document
Report Header label text:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>

Report Detail Text Box:

='
' & [WP_Name] & '
' & [desc] & '
Waypoint

SymbolAndName

' & [Street] & '
' & [City] & '
' & [State] & '
' & [Zipcode] & '

' & [Phone] & '

'

Report Footer Label Text: