Excel as a TomTom POI - Points Of Interest (POI) file editor / converter
As i use a TomTom GPS, i thought it was time to "scratch an itch" and use Excel as an editor/convertor program for the different types of POI files that i work with.
There are of course enough online solutions available and also series of installable Windows programs, but as a lot of Office workers are blocked by a corporate internet firewall and on top of that do not have the permission to install a third party program locally on their PC's.
Therefor a 'simple' local Excel workbook with 'macro's'/VBA could be a solution to do some quick editing and exporting to manage these specific POI files directly in Excel.
As the TomTom POI files (ov2) are encoded as a binary format, you cannot simply use Notepad for this task. Even file formats like KML and KMZ are not easily processed using Excel.
This VBA - Workbook is tested in Excel 2007, 2010, 2013, 2016, 365 32Bit/64Bit English version.
For Mac systems i do not have a working solution as i use some 'Windows only' activeX libraries internally.
Main features
Uses plain Excel (Windows) -> VBA in an xlsm file
Manage Multiple locations lists in 1 file as separate excel pages
Duplicate removal / sort and filter possibilities in the list
Description spaces cleanup
Automatic [address] font coloring
Shows location in Google maps: from coördinates OR from the description lookup
Importing and exporting KML files (Importing KMZ files)
Importing and exporting ov2 binary TomTom files
Importing only your 'Mydrive' export file (as TomTom does not supports importing this file again),
Exporting only CSV for Garmin
Importing and exporting GPX files
Coördinate to [Address] Lookups, Address to coördinates lookup
Reverse [address] lookup based upon Longtitude and Lattitude (API key(s) are needed)
GPS coördinates lookup based upon a given [address] in the description (API key(s) needed for this functionality)
3 separate / parallel providers possible: Google, TomTom, LocationIQ
Configuration is internally saved/kept per Excel file
Changelog:
Bugs solved in version 5.3.0: offered for free and free download (see download link below)
Locationiq: postcode & town recognition in address part
Import of LineString coordinates inside a KML file
Better GPX import handling
ov2 import: description text is not truncated anymore. Thanks Livio for the bug report!
Testing of different gpx files: stress test on big 37k lines files -> OK
Clear action of big file: move cursor back to top
Better cursor handling after importing of POIs
Bugs solved / New features in version 5.2.6: offered for free and free download (see download link below)
3 location lookup providers possible
location lookup with multiple rows selected
multiple location lookup delay config
Multiple rows cleanup description
Automatic [address] font coloring
Reworked config form for the extra providers
Code optimisation & bug hunt
Bugs solved in version 5.1.4:
Import from other Excel POI Files / pages
Doubleclick on Longtitude or Lattitude cell will use the coordinates for google maps lookup
TomTom MyDrive v2 export import only favorites
Autofit columns sizes after import
Changed google API key remarks: new 1 day = 1 call limit, API key also needs a billing number for free use
Optimized number formatting + zonesize
Better List type handling / importing
Better KML/KMZ import handling (no icons or styles are imported)
Better handling of long descriptions/more POI's on 1 page (overflow error)
Error solved when creating a new Track Table (Object not set error)
Solved wrong setting of freeze panes location (10+ lines down)
New features in version 5.0:
Importing KMZ files (zipped exports from google maps)
The tool will only load the location points data and will ignore all graphics and multimedia contained in the kmz file.
Importing of GPX files
Support for location files with more information than the normal Longitude / Lattitude and description columns
Multile internal types of location files
(a lot of code rewriting was needed for this)
Support for Layers (with layernames) and Tracks (With elevation and time columns)
Support for Tracks & trackpoints
Bugs solved, new features in version 4.43:
Importing KML files containing Layers (internally named Folders) can now be processed.
The tool imports all layers into 1 list (as the TomTom GPS does not support Layers)
KML export files from Google Maps fall under this category.
Non layered KML files are still working
as before.
Geocoding lookup:lookup the Lattitude and Longtitude based upon an [address] in the description column
Reverse geocoding: address lookup of the given longtitude and lattitude location (using Web api (XML) and Json
Input of your personal google geocoding API key input to let it work
Bugs solved in version 4.40:
Added extra code for compatibility for Excel 64 bit (still in testing)
Bugs solved & new functionalities in version 4.30:
Viewing in Google Street View or Google maps is possible now
Older choice between Street, Satellite & Terrain in the ribbon is removed due to Google maps changes
Importing Favorites Data from a TomTom MyDrive export txt file (a json file structure)
The on line service MyDrive that communicates with your latest TomTom gps stores the Favorites in this exportable text file.
The import is not directly foreseen in the TomTom MyDrive service ...
Added Table editing buttons for: New Table, Clear Table and Delete Table
Reviewed ribbon layout (Table - Location)
Imports are now always ADDED to the current selected page at the bottom of the list.
Solved Bugs and other issues in Office Excel 2016
Bugs solved & new functionalities in version 4.20:
TomTom POI import: importing can work with skip records in ov2 files(optimized ov2 files.
Better reporting after import if ov2 record types are not OK or if the record length is not correct.
Visualizing deleted records in ov2 TomTom files.
NEW: Import Google Earth kml files (with an internal KML/Document/Waypoint structure)
Re-enabled the Excel statusbar + reporting the amount of records imported.
Sheet name corresponds to the selected imported filename.
Sheet name is used also as name when exporting POI points to files.
Undelete POI lines button.
Removing Telephone suffix from multiple cells using the selection.
Bugs solved in version 4.11:
TomTom POI import: Better coordinates conversion: negative values are converted now.
If a TomTom ov2 file is another type then Type 2: give a warning.
Added capabilities in version 4.00:
Export GPX format (for Garmin GPS systems)
Export KML file (for Google Earth / My Maps)
Google Maps view type for opening (Map, Satellite, Terrain)
Multiple sheets = separate export files into one Excel file are possible now.
You can now import several ov2 files into one Excel Spreadsheet.
Just add extra Excel sheets to the file, like you would do it normally in Excel.
Capabilities of version 3.00:
Load the POI contents of a binary TomTom ov2 file into an Excel list.
Edit the points of interest GPS locations (Longitude and Latitude) directly in Excel cells.
Edit the POI point's description
Add a telephone to the Point of interest's description
Double click on the POI: referral to Google Maps to check upon the location of your POI
Save / Export the Excel contents to a usable (ov2) TomTom POI file
Save / Export the "Points of interest" into a standard CSV file (Garmin Compatible file format)
Standard Excel editing manipulations: Copy/Paste etc ... are still possible
Filtering and Sorting POI's list in Excel
Possible developments in the future, if time permits ...
CSV import
GPX file import
Multiple POI page merge/export
The download of this Ms Excel 2007, 2010, 2013, 2016, 2019, 365 xlsm file is available here: Download (hotlinking to this file or to the download location is not possible)
This solution is stored in a compressed ZIP archive. Unzip and doubleclick to use/store your location lists.
If your organisation does not support downloading / using external xlsm files - containing macro's - you could use it on you private computer ...
Screen shots / short explanation:
Opening
If you open this Excel Workbook tool for the first time, you'll see this screen.
It's displaying the security message that pop's up every time you open up a new or unknown Excel Workbook containing 'Macro's'
If you click on 'Enable Content', you'll have the possibility to add this Workbook to the list of Trusted documents.
After this step the POI editor will be working normally, and will open normally, without displaying the warning as in the next screen.
This is needed to use all the buttons and functionality of this file and to access the POI Data in the tabs.
Main menus
The empty Excel POI editor Workbook is opened normally (if Macro's/ VBA are activated)
There is one empty "Locations" sheet already available where the imported locations are inserted or where you can manually insert or copy/paste your location data.
On top you have two tabs: the Location Tab and the Import - Export Tab
The location's tab is where you do the editing inside a location's tab.
The Import/Export tab is where you import or export data in and out the current selected tab.
Import / Export POI's
If you import an ov2 file from your TomTom GPS, the results are always appended in the current Excel tab/page. The loaded location records are ADDED below any existing locations.
Importing is supported for:
TomTom ov2 files
TomTom MyDrive Export files
Google KML files
Google KMZ files
Google GPX files
Other POI Editor files (reorganizing)
The standard page column layout are Longitude, Latitude, Description. But this can be changed.
In the description you can further specify a telephone number by using the '>' separator inside the description.
You can also use the 'Add Telephone Suffix' button on the selected row.
Exporting is supported for the following formats:
OV2 for TomTom GPS
CSV Text file for Garmin GPS systems and other compatible systems.
KML Text file for interaction with Google Maps
GPX format for Garmin GPS systems and others.
Location list types
Before import you can specify a table layout, or you can convert any existing layout to a different one.
Location: with trackname, longtitude, lattitude and description columns
Layers: with layername, longtitude, lattitude and description columns
Tracks: with trackname, longtitude, lattitude, elevation and time columns
Default Location PIO format:
Location format for track with additional elevation and time:
Location format with additional layers in the POI data:
You can add a table type OR change a tabletype afterwards.
After import, changing to another table type is possible but will NOT display the data that was not imported due to other columns.
Each page type will have it's own colour
Location: RED
Layers: BLUE
Tracks: GREEN
Import / Export TomTom MyDrive
TomTom MyDrive data is available after you log in om the TomTom site
Go to the settings (gear/cog icon on the bottom)
Select MyDrive tab on top
Click on "Download your MyDrive data"
This will start the download of your MyDrive data, containing your Favorite locations you have created.
Google maps POI display
Displaying a stored location in Google maps can be done in 3 ways
Doubleclick on the coördinates
Doubleclick on the description
Using the Show in Google Maps button
If you use the lookup button in the ribbon: Show Selected Row in Google Maps.
The type of map that is opening is defined by the choice in the Excel Ribbon:
To set up Address Lookup and reverse address lookup, the settings menu and at least 1 API key has te be filled in.
You can fill in up to 3 API keys to be able to perform address lookups. Only 1 provider can be active
You can use the buttons after selecting a POI in the list. The icons will swith depenant of the chosen provider. If you did not fill in an API key the buttons turn grey.
For all geocoding functionalities you have to get a google geocoding API key and insert this in the settings.
If an address or a Coordinate exists you will get a confirmation warning.