Route4Me Address Merging Macro Tool
The Route4Me Address Merging Macro Tool adds relevant Address Book information to spreadsheet files containing address lists, by appending latitude and longitude coordinates (for addresses that are not geocoded) and visitation counters and timestamps.
The Address Book supports uploads of spreadsheet files with an unlimited number of custom columns. If the information in the custom columns is dynamic and must be updated on a regular basis, the Address Merging Macro Tool simplifies the information update process.
You visit the same customers each week but the order amount and value is different.
You need to add updated invoice numbers to customers each day.
You have a fixed customer base, but only certain customers get routed on certain days.
Instead of editing each contact manually, you can update your contacts in bulk in 6 easy steps:
import your initial address list to the address book;
export your existing address book (“Previous” file);
prepare the file with the updated information (“Current” file);
merge the previous and current files;
delete the existing address book;
upload the merged file to address book.
Import the Initial Address List to the Address Book
Prepare a spreadsheet file with the addresses you want to include in your initial upload, then follow the steps outlined in our dedicated article on uploading files to the Address Book.
Your initial import needs to include an “ID” column containing a unique identification code assigned to each address. This identification code will be used in the merging process.
Export the Existing Address Book
To download your existing address book (“Previous” file), select all your contacts either by checking them in the address book list or by drawing a shape around them on the address book map.
Next, click “File” → “Export Selected.”
Prepare the File with the Updated Information
Prepare the “Current” file with the updated contact information (the Merging Macro Tool only supports .CSV files). The “Current” file must include the address “ID” column with the unique identification code for each contact. Overlapping contacts in the “Previous” and “Current” must have corresponding unique identification codes.
Merge the Previous and Current Files
Click “Choose File” in the “Previous Day” section and select the “Previous” file (address book export) from your computer.
Next, click “Choose File” in the “Current Day” section and select the “Current” file.
Click the “Merge Now” button to merge the two selected files.
The merged file will be downloaded to your computer. It will include all of the information from your “Current” file, plus the following additional columns:
Lat – address latitude;
Lng – address longitude;
Curbside_lat – address curbside latitude;
Curbside_lng – address curbside longitude;
Last_visited_timestamp – the date and time when the address was last marked as visited in a route (MM/DD/YYYY HH:MM);
Last_routed_timestamp – the date and time when the latest route to include the address was planned (MM/DD/YYYY HH:MM);
In_route_count – the total number of routes the address has been included in.
– If an address is not present the address book export / “Previous” file, then all above-mentioned fields will be blank;
– If an address is present in the address book but it has never been routed, the “last_visited_timestamp” and “last_routed_timestamp” columns will be blank.
Delete the Existing Address Book
If you don’t want to have any duplicate entries, you must delete your entire existing address book by selecting all contacts either in the Address Book List or the Address Book Map and then removing them.
Upload the Merged File to Address Book
Open the “Address Book List” or the “Address Book Map”, then select “File” → “Upload Addresses” and finalize the upload process.