Formatting a POI file for easy sorting in Excel (May work in other spreadsheets)
(PDF version of this page for easy printing)
This method will help keep you from creating duplicate entries and make the creation and/or updating your POI file much easier and quicker.
Also, POI Factory users can easily delete out unwanted POIs if they only want a certain state or area.
Preparing your POI file
The Garmin recommended format is:
• Column A = Longitude
• Column B = Latitude
• Column C = Name
• Column D = Comment (optional)
IMPORTANT: Save your Excel POI file with a different name before starting.
I use column “C” for the name of the location. Example: For the Bank of America POI file I enter “BoA ATM” then the branch or remote name and the phone number if it’s a branch. However you format your file –BE CONSISTANT. Garmin uses the “C” column as the “Header” for the individual POI in the displayed list. If you want to list the availability of services – do it in this column.
IMPORTANT: For sorting purposes column D must contain only the address of the POI. Pretty standard here - street address, followed by town/city/county. Next is the State and last is the 5 digit Zip Code (Postal Codes in other countries may have more or less characters).
NOTE: 2 lines for the address is not necessary. But, in Excel I delete the “Space” at the end of the street address and then use an “Alt + Enter” between the street address and the city. This creates a 2 line cell and in turn makes Garmin do a “line wrap” (drop to next line). This will make Garmin display only the street address in the first line.
TIP: This works for the column “C” as well.
Example:
3 1st Ave
New York NY 10003
Instead of:
3 1st Ave New York
NY 10003
NOTE: Excel defaults to single line cells which hides the second line. You can adjust the cell height of all the cells by selecting the grey arrow in the upper left corner of the work area to highlight all the cells. Then move the cursor to hover over a horizontal line in the line header column. When a bar with an up and down arrow appears, left click and drag the line down until the 2nd line in column D is visible. Don’t make the cells too tall – you have to scroll more. Single left click in any cell to clear the selection.
NOTE: I do not use commas anywhere in the file. I have seen POI files have a problem because POI Loader thinks the comma is a column delimiter because something went wrong when the .csv file was created. Notice that there are no commas in the examples above.
IMPORTANT: "D" column cells must be formatted as described above for the sort to work properly (except the second line is not necessary).
So, after all the address cells are formatted:
1. Go to the last line of data in the file and single left click in the “E” column.
NOTE: Left click on any cell that contains data, then hold down the “Ctrl” key and press the “Down Arrow”. Excel will scroll down to the last cell that contains data in that column.
2. In the Formula Bar/Data Input box type ”=RIGHT(Dx,5)” (without the quotes and “x” is the line number).
Example: In my Bank of America file, currently, the last line is 7207, so I would enter “=RIGHT(D7207,5)”.
NOTE: This tells Excel to start from the right of the D7207 cell and display the last 5 characters.
IMPORTANT: If you are in another country using a different number of numbers and/or characters – adjust the “5” to reflect the number of numbers/characters used (including spaces).
3. Press the “Enter key.
RESULT: The Zip Code of the address in the last line, column “D” should now be displayed in column “E”, with the formula hidden.
4. Right click on the Column E cell, at the bottom, and select “Copy” in the dropdown menu.
RESULT: Cell should now have a flashing border.
5. Single left click in the cell above it in the E column.
RESULT: Cell border is now highlighted
6. Hold down the “Ctrl” key and the “Shift” key at the same time and then press the “Up Arrow” key.
RESULT: The E column is now light blue and the work area is now at the top.
7. Press the “Enter” key once.
RESULT: All cells in column E now display the Zip Code of the cell to the left of it. Spot check to verify all went well. If it did not work, delete or clear column E and go back to step 2.
NOTE: You could also do this from line 1, BUT it will bloat your file by placing the formula in all 1 million+ lines – don’t do it!
8. Select the gray arrow in the upper left of the workspace.
RESULT: All cells are now light blue.
9. Select the “Data” tab at the top of Excel and then select the “Sort” icon.
RESULT: Sort dialog box appears.
10. In the dialog box, select the dropdown arrow for “Column” and select “D”.
RESULT: “Column D“ appears in the selection box.
11. Ensure that the “Sort on” is set to “Values” and the “Order” is set to “A to Z”. If not, change them to the correct values.
NOTE: These are the default settings.
12. Select the “OK” button in the dialog box.
RESULT: Column D is now sorted alphanumerically and the “Sort” dialog box closes.
13. Select the grey arrow in the upper left again.
RESULT: Entire work area is light blue.
14. Select the “Data” tab at the top of Excel and then select the “Sort” icon.
RESULT: Sort dialog box appears.
15. In the dialog box, select the dropdown arrow for “Column” and change to “E”.
RESULT: “Column E“ appears in the selection box.
Note: “Sort on” and “Order should not have changed.
16. Select “OK”.
RESULT: “Sort Warning” dialog box appears.
17. Ensure that it is set to “Sort anything that looks like a number, as a number”
NOTE: This is due to the fact that the cells contain a formula and displays a number.
18. Select “OK”.
RESULT: File is now sorted by address number and Zip code.
NOTE: Excel will automatically recalculate all the formulas.
HINT: Any cells that have less than 5 numbers will be at the top of column “E”. Edit the “D” cell to delete the trailing space(s). Then resort the file on the “D” column and then sort on the “E” column.
19. Save your file under the new name.
Saving as a “.csv” file
20. Open the new file and delete column E.
IMPORTANT: A .csv file containing an extra column (E) may cause an upload to fail or be corrupted during the upload.
21. Select the “File” tab and select “Save as”
RESULT: “Save as” dialog box appears.
22. Navigate to desired directory, if needed.
23. Enter a new file name, if desired, in the “File name” box.
NOTE: You are saving it as a .csv file, so it will NOT overwrite the .xlxs file.
24. In the “Save as type” dropdown, select “CSV (MS-DOS) (*.csv)”.
NOTE: Best results using this format .csv file. It is a true text file.
25. Select “Save”.
RESULT: Dialog box closes and file has been saved and a warning box appears.
26. Click “YES”.
RESULT: Another dialog box appears.
27. In the dialog box select “Don’t Save”.
IMPORTANT: Selecting “Save” will overwrite the current .xlxs file that contains the “E” column with a file that does not have the “E” column and you will have to recreate the column.
28. Close your file.
29. Open the new .csv file using Excel and check to see all is well.
30. Upload your new .csv file to your Garmin and ensure that POI Loader returns the same number of entries as the .csv file and returns no error messages.
NOTE: If any errors are noted, then fix them in the .xlxs file, then overwrite the .csv file and upload again to the Garmin.
31. If the file uploads to a Garmin with no errors, you can now upload the file to POI Factory.
ADDING NEW ENTRIES
NOTE: This method will help reduce duplicates.
1. Open the .xlxs POI file
2. Hold down the “Ctrl” key and press the “F” key.
RESULT: “Find and Replace” dialog box opens.
3. Select the “Header” for the “D” column.
RESULT: The “D” column is highlighted light blue.
NOTE: This will not work for the “E” column, since it searches on the formula level, not the text display level.
4. Enter all 5 numbers of the Zip Code in the dialog box for the new entry and select “Find Next”.
RESULT: Excel will either scroll to a line with the Zip Code and highlight the cell or it will open a dialog box stating that the data cannot be found. If you get a dialog box, Click “OK” and scroll through the file to find the numerically closest Zip Code.
NOTE: Sometimes Excel will find the number in the street address. Just select the “Find Next” button to continue the search.
5. After locating the Zip Code (or the closest that is higher), “Right Click” on the “Row Header” cell (on the left) and select “INSERT” in the dropdown menu.
RESULT: A new blank line is inserted ABOVE the selected line.
6. Populate the line with the new data.
NOTE: When the new address is entered, the Zip Code will appear in column “E” after you press enter or select a new cell. If only part of the Zip Code is there, then there is a “Space” or “Spaces” at the end of the address. Edit the address cell to delete the “Space(s)”.
IMPORTANT: If the Zip Code does not appear, click once on the cell and check the Formula/Input line above the work area. If no formula is there, then simply right click on the cell above and select “Copy” in the dropdown. Then right click on the empty cell and in the dropdown select the left icon under “Paste Options”. Excel will automatically adjust the formula and the Zip Code should appear. Excel sometimes hiccups and doesn’t create the formula when inserting a new line.
7. Enter more entries, if needed.
8. Save your file.
DELETING LINES
1. Open the .xlxs POI file
2. Hold down the “Ctrl” key and press the “F” key.
RESULT: “Find and Replace” dialog box opens.
3. Select the “Header” for the “D” column.
RESULT: The “D” column is highlighted light blue.
NOTE: This will not work for the “E” column, since it searches on the formula level, not the text display level.
4. Enter all 5 numbers of the Zip Code in the dialog box for the new entry and select “Find Next”.
RESULT: Excel will either scroll to a line with the Zip Code and highlight the cell or it will open a dialog box stating that the data cannot be found. If you get a dialog box, Click “OK” and scroll through the file to find the numerically larger closest Zip Code.
NOTE: Sometimes Excel will find the number in the street address. Just select the “Find Next” button to continue the search.
5. After locating the correct line to delete, “Right Click” on the “Row Header” cell (on the left) and select “Delete” in the dropdown menu.
RESULT: Line is deleted.
6. Save the file.
TIP: This will help you find and delete closed or moved locations when updating your POI file. At the BoA website there is no "List" for you to go by and many other sites are structured this way (usually by state and/or city). As you verify the location in Excel, color the line to show that it has been verified. Then when you have gone through all the sites listed at the website, scroll through the list and look for any lines that are have no color. If you find one, then verify that it is not one you missed. If you missed it, add the color and move on. If the location is no longer valid, then delete the line. Once all the locations have been verified/deleted then select the grey arrow in the upper left to highlight (select) all the cells. Then select the down arrow in the Paint Bucket icon on the Home tab and select "No Fill" in the dropdown and all the cells will lose any color. Nice bonus to this is that you can stop working on the file anywhere in it, save and close the file, open it later and you will know what has been verified.
CAVEAT:
Excel sorts “Text” cells differently from “Number” cells. Any cell that contains “Text” is sorted as an alphanumeric cell using a different rule.
Number cells example:
1200
12
114
When sorted will be:
12
114
1200
Alphanumeric cells example:
1200 East xxx
12 East xxx
114 East xxx
When sorted will be:
114 East xxx
12 East xxx
1200 East xxx
Be aware of the two different sorting methods, especially in large cities with many locations.
Added Tip May 5, 2020