Guide: How to import an Excel / CSV file and plot it on the map
Converting Excel files to CSV
If you have an Excel file (.XLS or .XLSX), you will need to save it as CSV first. This is very easy as you just need to open the Excel file, then Save > Save as ... and choose CSV file from the dropdown.
See more about how to "Export data to a text file by saving it" on Microsoft Office Help
Note: To be correctly processed by Maps Pro, the first row of the file must contain the column headers. If you wish to only export part of your document, click-drag to select the row/columns you need, copy (Ctrl+C), open a new Excel document and paste the content (Ctrl+V), then save as CSV.
Opening your CSV file in Maps Pro
There are two options to open the CSV file in Maps Pro:
- Right click the file > Open with > Maps Pro
- Open Maps Pro > Menu > Overlays > Add KML/GPX/CSV/GeoJSON files > .CSV and pick your file on the disk
If you have addresses, Maps Pro will turn them into geographic coordinates. This process is called "geocoding" and will alter your file. Three columns will be added:
- geocoder_lat: contains the latitude
- geocoder_lng: contains the longitude
- geocoder_address: contains the complete address of the point that was found
If your CSV file contains addresses
If your CSV file contains addresses, there is an additionnal step before you an plot your file on the map. You will need to tell Maps Pro which columns contains the street addresses, zip/post code, city, city state or country.
With these informations, Maps Pro will be able to get an address for each record, send it to Google Maps, and store the coordinates in the file.
Note: This process will need you to be online as Maps Pro needs to retrieve informations from Google Maps. Once Maps Pro processed all the addresses, you will be able to use your CSV file while offline.
Let's take some sample data from the city of Chicago. On their open data website, you can download the list of Chicago libraries as a CSV file:
Libraries - Locations, Hours and Contact Information
To download it as a CSV, click Export > CSV. The CSV file contains the address of each library, here what it looks like in Excel:
Once you open this CSV in Maps Pro, you will get the following wizard:
The first step of the wizard is to select the columns which contains the name and description of each record. These informations will be displayed on the map and in the overlay panel that appears on the right once the file is opened.
By default, Maps Pro chooses the first two fields of the file but you can change it to the fields which make sense in your case.
The second step allows you to choose which columns contain the address components. Maps Pro automatically detect them if the are called "street", "road", "zip", "city" and so on. If Maps Pro didn't detect the right columns automatically, please choose them from the dropdown lists.
Just underneath, Maps Pro shows you an example of the address that will be made from the columns you selected. If the address can't be found for some reason, Maps Pro will display an error message is red.
Once you feel that the configuration is good, press OK and a progress bar will appear:
Depending on the number of addresses in your file, the process can take a while but once it is done, the coordinates will be written in the CSV file and you won't have to do this step again.
Note: Google Maps imposes daily limits, usually 25 000 requests per day. If you have more, you will need to split your file and process your one file a day.
Once the process is finished, the wizard will automatically close and your file will be directly shown on the map:
You can now find your overlay in the overlay list in Menu > Overlays to toggle it on or off.
If your CSV file contains geographic coordinates
If your CSV file contains geographic coordinates, then you are good to go. Maps Pro will automatically detect columns containing latitude and a longitude coordinates, based on the column name (lat/latitude and lon/longitude) and the value of these columns which must be float numbers.
The CSV data wizard will still open but you can just select the name and description fields and press OK to plot the file on the map.
If you have questions about How to import an Excel / CSV file and plot it on the map, please email tickets@mapspro.uservoice.com, thanks!