We have a powerful system for importing location data rapidly from spreadsheets. Use the Import window if you have data attached to addresses, such as labels, descriptions and categories. You can even set data columns for filters and auto-colouring on import.
If you have a column of UK postcodes, you can import up to 10,000 spreadsheet rows at a time. If you have general addresses without postcodes (which can be worldwide addresses), you will need to geocode the addresses before importing. We explain how to do this below.
On this page:
Demo of map created from spreadsheet import
Spreadsheet requirements
Import process
Export process
What to do if your postcodes are not in a separate column
Geocoding procedure if you do not have postcodes or you want more accuracy than postcodes
Updating existing locations from a spreadsheet
Demo of map created from spreadsheet import
The first 6 lines of the spreadsheet
| Label | Address 1 | Address 2 | Postcode | Community | Type | Status | Account code | Website | Contact name | |
|---|---|---|---|---|---|---|---|---|---|---|
| Listing 1 | Abercarn Industrial Estate | Abercarn | NP11 4SF | Abercarn | Tools & Hardware | Archived | X7720 | https://create.maproom.net/demo-link/ | Contact Name | [email protected] |
| Listing 2 | 32 Bernard Street | Newport | NP11 7ED | Abercarn | Electronics & Gadgets | Allocated | X7540 | https://create.maproom.net/demo-link/ | Contact Name | [email protected] |
| Listing 3 | 52 George Street | Newport | NP11 7ET | Abercarn | Sports & Outdoors | Pending | X7530 | https://create.maproom.net/demo-link/ | Contact Name | [email protected] |
| Listing 4 | Troed y Rhiw | West End | NP11 4SS | Abercarn | Medical | Complete | X7530 | https://create.maproom.net/demo-link/ | Contact Name | [email protected] |
| Listing 5 | Upper Gertrude Street | Abercynon | CF45 4RN | Abercynon | Home & Living | Live | X14550 | https://create.maproom.net/demo-link/ | Contact Name | [email protected] |
Spreadsheet requirements
- Supported formats: Excel (.xlsx, .xls) or CSV
- Your spreadsheet must include columns for either Postcode or geocoded coordinates for Latitude and Longitude. (More info about this requirement below.)
- First row must contain column headers
- Each subsequent row must represent one location
- Excel sheets with multiple tabs will allow tab selection for individual imports
- For consistent results, ensure your spreadsheet has a simple structure without merged cells or Excel formulae, filtering or custom formatting
You can include multiple extra columns alongside the required columns. Our system will attempt to import all the data in your spreadsheet. (So remove any unnecessary data that you don’t want feeding to the map.)
There is no definitive upper limit for spreadsheet size. The import speed and map loading speed will vary according to how much data you are importing for each location. However, if your spreadsheet has more than 5,000 rows, consider breaking up the import into batches. If you have more than, say, 40k data rows, your map is likely to take many seconds to load. If slow loading time becomes a problem then we recommend you enquire about our bespoke mapping solutions that will process your data into zoom tiles for fast loading.
Import process
Download a demo spreadsheet to import
Upload – Click the Import icon on the top bar to open the import window, then drag and drop your spreadsheet onto the window.
Backup prompt – If your map already contains locations you will be prompted to export a backup at this point.
Georeferencing columns – the system will confirm if you have either a Postcodes column or Latitude and Longitude coordinates columns, which are essential for import.
Listing name – an appropriate column for your location names. These will be the labels in the sidebar listing and the popup headings.
Address fields, e.g. street, town, country, postcode.
Contact details, e.g. a name, email, phone, website link if relevant.
Category filtering, if you want to set dropdown text filters for specific columns. E.g. a column for ‘Status’, with terms such as ‘pending’, ‘active’, ‘archived’ that can be filtered. You can have up to 5 filter columns at once. (More info in set colour keys and text category filters.)
NOTE: multiple terms for a single location can be imported if you separate each term in your spreadsheet with vertical pipes |. E.g. in the demo spreadsheet Westminster Abbey is listed as Abbey | UNESCO World Heritage Site | Visitor Attraction in the ‘Attraction type’ column, whilst Edinburgh Castle is listed as Castle | Visitor Attraction. Each term will be treated as a separate filter for that location in the category dropdown filters list.
Marker colouring – default blue, but you can choose another colour, or you can set a category column for auto-colouring, or use a ‘Colour’ / ‘Marker colour’ column if your spreadsheet contains a column of colours already.
Start Import – you will be able to see progress. A batch of 1,000 spreadsheet rows should take only about a minute to import.
Report – you can download a summary at the end of the import process. Invalid coordinates (non-numeric or missing) will be skipped with an error note in the report.
Auto-plotting – All successfully imported locations will appear on your map with filters, colouring and data feeding automatically to popups. You can change the filters and colouring globally.
Demo import – if you have used the spreadsheet above as an import test, you should now have a map looking something like this.
Export process
You can export your locations to spreadsheet format in this window. The exported spreadsheet will include a database ID and the geocoded coordinates (latitude and longitude) of each location. (About Geocoding.)
The database ID is essential to retain if you want to update existing locations on import.
(The geojson format option is if you want to export your location data to other mapping applications.)
What to do if your postcodes are not in a separate column
If your addresses including postcodes are run together in a single column, you should separate the address parts first if possible, so that the postcodes are isolated into their own column. This is an easy exercise if the address parts are separated by commas, as in this example, and if you are using Excel.
1 – In Excel, select the column, then go to the Data tab and find the Text to Columns tool.
2 – Choose Delimited then click Next.
3 – Make sure Comma is selected then click Next. Then click Finish.
Your address parts should now be in separate columns. Be sure to add the label ‘Postcode’ to the postcodes column, and other appropriate labels for the other address parts ready for import.
If your address parts are not defined consistently by commas, try a delimiting exercise with spaces, then concatenate the postcode parts into a single column. However, if your addresses are an inconsistent mess (😅 a common problem!), run a geocoding exercise, as we explain below.
Geocoding procedure if you do not have postcodes or you want more accuracy than postcodes
If your addresses do not include consistent UK postcodes, or if you are finding it difficult to extract the postcodes into a separate column, or if you want more accuracy than plotting postcode centroids, or indeed if you have locations outside the UK, you will need to run a geocoding exercise to find Latitude and Longitude coordinates for your locations. (About Geocoding.)
If you have fewer than 500 locations, use the + Add list tool to do the initial geocoding and plotting of your locations. If you have more than 500 locations, use our Premium Geocoder first.
Once you have a map with markers, export your data which will include the geocoded coordinates plus a database ID for each location.
Example spreadsheet structure with geocoded coordinates and ID
| ID | Name | Latitude | Longitude | Address | Description | Category |
|---|---|---|---|---|---|---|
| 207769 | Office HQ | 51.5074 | -0.1278 | High St, London | Main office | Commercial |
| 207770 | Warehouse | 53.4808 | -2.2426 | Manchester M1 1AA | Storage facility | Industrial |
You can then copy your full data into the geocoded spreadsheet and import to update the existing locations as explained below.
Updating existing locations from a spreadsheet
To update existing locations from a spreadsheet, first export the existing data to spreadsheet which will include a column with database ID for each location. (Try exporting the demo spreadsheet locations that you may have used to test the import process above, and see how the new spreadsheet includes an ID column.) You can then add / edit data offline to the spreadsheet (e.g. change the phone numbers and add new attraction types for each location in Excel). Then reimport the updated spreadsheet. Our system will automatically match the location IDs and update the elements that have changed.
Important to understand: Our system relies entirely on the database ID to update an existing location on import. Without an ID match, it will simply plot a new location. Also be aware that if you duplicate a map, the locations in the duplicated map will have different database IDs to those in the original map.
Need help with spreadsheet operations?
Working with spreadsheets is rapid and rewarding but may also be complex and confusing, depending on how your data is structured. Please reach out to [email protected] if you’d like personalised advice on spreadsheet formatting, geocoding, column mapping, colouring and filtering. We want you to succeed with mapping!