@mappingtoolsThe Data Mapping Wizard can be used with your own data and not just the built-in demographic data. The Wizard then imports your data into your current MapPoint map file.
Most MapPoint users use the Data Mapping Wizard to plot Microsoft Excel spreadsheets. The wizard can also be used with text files, Microsoft Access databases, and UDL-specified databases. Excel is popular because it is very flexible, easy to manipulate and can be used for sophisticated calculations. Despite this, databases are generally recommended because their formal nature makes it less likely that you will make a mistake.
After saying all that, this example of the Data Mapping Wizard will use an Excel spreadsheet. Our sample data is an Excel export of the Customers table from the Microsoft’s sample Northwind database. This is what the exported Excel worksheet looks like:
The sample Northwind customer table in an Excel spreadsheet
It is recommended that you use column headers on the first row. These headers will make it easier read what each column is. Also, MapPoint can use these as a guide to correctly assign the columns. Finally, MapPoint will display these headers when you verify the columns have been assigned correctly.
Start the Data Mapping Wizard by selecting Import data wizard… on the Data menu. This will display a standard file selection dialog box. Select your input Excel workbook. The wizard will then let you choose the worksheet to read from the workbook:
Selecting the input worksheet
Here, we select the Customers worksheet (in fact, the only worksheet present). Press Next to assign the data fields:
Assigning the data fields (columns)
Set the First row contains column headers check box if you have used headers (recommended). Also remember to set the Country/Region drop-down box, so that MapPoint uses the correct location database when geo-locating the data.
For the column headers, set most of these to “Other Data” or “Skip Column“. Use Skip Column for data fields that you do not intend to use, and use Other Data for data fields which you might use but are not necessary to geo-locate the data point (eg. population, sales). It is recommended that you use Skip Column for as many data fields as possible – especially if your dataset has a lot of data points and/or data fields.
Make sure one of the fields is set to Name. Here we use the Company field for the name, and set the company representative fields (Last Name and First Name) to Other Data. The data points are located using the Address, City, State/Province, ZIP/Postcode, and Country fields. MapPoint has automatically identified these data fields, but it is always a good idea to check they have been matched properly. Note that this sample dataset has “null” zip code data with ’99999′ for all zip codes. This may confuse MapPoint, so set the Zip/Postcode field to Skip Column. We will have to manually allocate these in a later step.
We have also set the telephone number fields to Other Data because we want to display these in the pushpin information boxes (“balloons”).
Press Finish to start the import process. MapPoint will read all the data and attempt to geo-locate it. Often MapPoint cannot find unique locations for all of the data points because of spelling mistakes, address changes, or missing data. Here we are missing zip codes, so many of the locations result in ambiguous results (e.g. a long street might cross two or three zip codes). For these ambiguous locations, MapPoint will display the following dialog box:
Matching ambiguous locations
This dialog box lets you select the correct location, or to skip it. If you are importing large amounts of data, this could become very tedious – especially if the problem was due to a trivial error. Use the Skip all records (to only use records that have been matched) or Cancel buttons to get out of this situation.
MapPoint will then take you to the map display panels of the wizard, which we have seen with the demographic data. The exact sequence of panels will depend on your data type and selected map type. Here we have point data, so we select the Pushpin map type:
Selecting a pushpin dataset
This only gives us one final panel, where we set the pushpin set properties:
Assigning the data fields to display
Change the pushpin set name and pushpin symbol if you wish. Here we have kept the default name (the worksheet’s name) but changed the pushpin symbol to the large red disk. Finally, you can set the fields to display in the pushpin information windows (“balloons”). Here we have selected all of the imported data fields, including the address, contact name, and telephone numbers.
Press Finish to create the map. Here are the results with a Miami pushpin opened up:
The resulting map with the Northwind Customer Dataset
Although the Data Import Wizard is usually used with Microsoft Excel data, it can also be used with tabular text files (eg. comma separated or tab separated files); Microsoft Access databases; and UDL-specified databases.
I work for the City of Milwaukee and am wondering how MapPoint can be used to plot routes, such as garbage pickup.
Can custom maps showing the City’s political boundaries be shown, and made into usable letter size paper maps?
I would like to be able to pinpoint trouble areas and map them.
Does MapPoint still show census tracts?
Yes MapPoint can plot data at the census tract and zipcode levels. Your city political boundaries would only be possible if they follow one of these.
For printing, MapPoint can print directly to paper, but for higher resolution plots (eg. larger paper sizes), you may want to use the services outlined here.
How would I map data to show 2 different companies employees’?
Thank you.
You could import each company’s employees as separate pushpin sets using the Data Import Wizard. Importing them as separate pushpin sets makes it easier to set different symbols for the two companies, and to manipulate/export them. Also in MapPoint 2010/11 you can hide a pushpin set to increase readability if the screen is too cluttered.
So I import data from Excel. Currently with my 2004 version, I can only view all of my data on the map. Say I wanted to display only companies whose customer name field has the word “Shell” in it and is a part of the west coast, is there a way I can do this? If so I will be upgrading to 2010 by the end of today.
It sounds like you should be using a relational database (eg. Access or SQL Server) for queries like that. MapPoint cannot do queries like that unless you split your data into separate pushpin sets first (can be cumbersome). I would recommend you upgrade to MapPoint 2011 (the latest version) which has some new features and much better road/area map data; and try Microsoft’s own SQL Server add-in. The add-in is free, and there’s a free trial for MapPoint 2011. Note also, that many add-ins and extensions no longer support MapPoint 2004.
I bought MapPoint 2011 because someone told me I could use it to determine where the bad areas are in my city for crime (I’m purchasing a home). But I can’t figure out how to use this program. Any guidance? I’m so lost and very frustrated.
MapPoint is capable of plotting external data using the Data Import Wizard on the Tools menu. This could be used to produce maps using recent crime data (eg. supplied by your local city). You could produce shaded area maps on a zipcode or census tract basis.
MapPoint 2011 also comes with a range of demographic datasets. Most are intended for marketing (eg. population, spending, etc) but there are three crime datasets: Total (2007), Personal (2007), and Property (2007). Plot one of these using the Data Mapping Wizard (again, on the Tools menu). The datasets are listed in alphabetical order, so scroll down to ‘C’ for crime (there are a lot of datasets!). Also select the area you want to map by. I would recommend zipcode or census tract as these are the smallest – plotting by State will be less useful. When it comes to the map type, select “Shaded Area”.
In MapPoint 2011 it looks like the Data Import Wizard is on the Data menu, not the Tools menu (took me a while to find that). You had mentioned getting crime data from my local city so that I could import it into MapPoint — is there a particular website that cities normally upload this data to in an importable format? The only thing I could find for my city (Tucson, AZ) was located at http://cms3.tucsonaz.gov/police/stats and it doesn’t have any documents that I can import into MapPoint.
We are considering moving locations. What is the easiest ways to import current employee addresses to determine the driving impact if we were to move?
Individual locations (ie. employees, but also stores, customers, etc) are always best imported as pushpins. use the Data Import Wizard to do this. This works best with MS-Access databases or spreadsheets (Excel, csv, etc). As well as pushpins, there is a multiple pushpin option which is worth investigating if you wish to use different colored pins for different categories (eg. sales, admin, etc).
I have an issue with Mappoint not importing all records held on my spreadsheet. Any idea what could be causing this?
Of course it is difficult to say without any more information. MapPoint will give you a list of addresses that it could not locate, and will let you choose from a number of ambiguous possibilities. Reasons for these ambiguous results include typos, or new streets/post codes/etc.
The other reason is that there are formatting errors in your data file.
Hi Richard, thanks for getting back to me. It’s not that it doesn’t recognise an address, it’s that from a spreadsheet containing 294 records, it will import 62. I’ve removed all formatting from the spreadsheet, but Mappoint does not seem to pick up there are actually 294 records in there.
Formatting errors are more likely to be related to the actual text/contents – eg. funny characters, blank cells, etc rather than “cosmetic” Excel formats (color, font, alignment). MapPoint should ignore the cosmetic stuff.
Hi Richard,
Hoping you can help: is there a way in MapPoint 2011 to define territories by State and then export the results to Excel that includes State and all zipcodes w/in that State? Because we need zipcodes for our territory managers, we have been tediously grabbing the zips w/in a state manually but many zips are missed if we don’t enlarge the map enough. Thank you.
I think you are doing it the best way possible within MapPoint. What I would do, is to try and get a zipcode list for each State from either the USPS or State Government. As well as being more convenient, it will be more upto date.
How can I plot London zipcodes? “Zipcodes” was not an option as one of the data types in the import data wizard for the UK. Thanks.
Don’t you mean postcodes? Remember to set the country to UK (and you need the European edition of MapPoint, of course).
Hi Richard -
Any thoughts on cross streets? I want to upload a large spreadsheet of locations, but most of them are cross streets, rather than actual # addresses.
Try using an ampersand (&) between the two street names and put these in the street field. A good way of experimenting with this kind of formatting is to try to use the “Find Address” dialog box within MapPoint.
I am able to import data without zip codes and Mappoint then matches those addresses and locates the correct zip code. When I export the data to Excel it is not showing the zip codes. Is there any way to get Mappoint to export the zip codes to?
Again I did not have them but it was able to find them. Now I just want to get them into Excel in a list so I can send out a mailer.
Thanks!
No. You can only extract street data that you provided to MapPoint. It is possible that this might change in the future: Old versions would only report coordinates if you used them to create pushpins. Coordinate information is now available through (for example) the programming interface.
Richard, I need to make maps with both Canadian and US geographies represented. How do I do this? My data is in an EXCEL spreadsheet and I have country, state/province, county/census division columns as well as a geocode using FIPS in the US and the counterpart in Canada. any help will be greatly appreciated.
The US and Canada use different geography hierarchies and names. You are probably best off splitting your data into two spreadsheets: US, and Canada. Then, import each one individually using the same data plotting parameters, except plotting one for the US (using US geography levels) and one for Canada (using Canadian geography levels).
Good morning,
How do I map only zipcodes. I am having a very difficult time figuring this out.
Thank you,
Damla
Set the data field / column that stores the Zipcodes to “Zipcode”. Set the other fields to “Other data” or ignore. The data will then be located by zipcode.
Rich,
I’m trying to map bank branch offices with all personnel and equipment. I haven’t seen an example of a pushpin with say 10 or more people listed. Is this possible? Would it be better to attach the pushpin to a excel xls? Is it possible to attach an xls to a map pushpin?
Any help or idea would be appreciated.
Lisa
PS. I forgot to add that I’m using Mappoint 2011.
So you are listing the names in the data fields for each pushpin? There could be varying numbers, so the best way of doing that would be to put them in one big text field and import it as “Other Data”. Pushpin ‘balloons’ (the popup windows) are not designed to hold large amounts of information – such windows would quickly cover the map making it unusable. This applies for large screens, but becomes more of a problem more quickly, on smaller notebook type computers.
The Notes field can also hold a URL – this could point to a local file (eg. an Excel file) and not just a web page.
If you are interested in their locations, you could plot each bank’s employees using a pushpin symbol, and use different pushpin symbols for different banks – so distinguishing which bank they worked at.
How do I import zip codes from excel to mappoint in the same order they are in the excel spreadsheet?
Points on a map do not have an order as such. If you mean waypoints in a route, then you can import the data as pushpins using a data field as a “sequence number” (ie. intended order). Then use the MPRouteReader half of our MPRouteWriter product to create a route from them in the intended order.
Hi,
Thx for your help with my question. I notice that when I have a number of pushpins together and open them up, the dialogue boxes cover each other. Is there any way to position these boxes? I tried to just move them but they won’t budge.
Lisa
Right click on a pushpin and select “Orientation” from the popup menu. Each ‘balloon’ box can be positioned in one of four orientations. This might help for marginal cases. You are going to have to zoom in if you have a lot of pushpins close together.
Rich,
In referring to your 10.11.11 response to my question concerning large amounts of data, you said “There could be varying numbers, so the best way of doing that would be to put them in one big text field and import it as “Other Data”. ”
where do I find ‘Other Data’? I looked under import data wizard and link to data wizard and don’t see it.
Lisa
“Other Data” is one of the field options in the Import Wizard where you assign what every field means. Other options are things like “City”, “Name”, “Street”, etc.
Is there a quick way that to retrieve addresses which are not imported into Map due to poor address info so that they can be corrected? Thanks
You cannot get “something for nothing”. If there was poor address info, then MapPoint cannot do any better other than give you some candidates. You have to choose the best addresses or fix the original poor data.
Rich. I think I may not have been clear. For instance if you have a list of 1500 addresses and say 75 of them are bad. Is there a way for you to retrieve the 75 bad ones in list form so that you may clean them up in your original data base instead of going through the individual process in Map Point, which does not help you clean up the original database. I hope that clears it up. Thanks.
Richard- great site. I’m trying to layer datasets. One set has sales information by zip code and the other has salesperson by city/town.
First attempt involved combining the two datasets which was possible with excel vlookups but loading to MapPoint did not accept any of the “Other Data” columns which is what I had to use for my salesperson and salesperson city columns. Any help/direction is much appreciated!
If you are going to combine two datasets, you are going to need to physically combine the two worksheets. The vlookup and cross-referencing is probably confusing MapPoint.
I did physically combine the two datasets and copied/pasted special values so the formula is not in the way.
The problem is that I’m trying to get 4 dimensions of information:
SHADING
1 – Sales
by
2 – Zip of company
and
PUSHPINS
3 – Sales associate
by
4 – Zip of office
I’ve got the shading but am running into a roadblock with adding the pushpins on the same map.
Sorry it wasn’t clear what you were trying to do.
If you wish to plot two different datasets, then you need two worksheets, and you need to import them independently.
It does not make sense to merge two datasets of different types. For example, it does not make sense to merge pushpins and shaded areas. These are different types. Shaded areas only mean something with areas. Pushpins refer to points (even if those points are centers of areas). Shaded areas require one numeric value. Pushpins can have any number of data fields of any type.
Richard- thanks again for your responses. You’ve helped me maintain my hopes (and sanity) as I work through this case. The push-pins would reflect people and where they live while the revenue by zip code reflects the most opportunity. Think of it from a resource allocation perspective. I’ve got the data loaded and figured out what the problem was- I was too zoomed out for the revenue by zip to be noticeable. That was fixed when I zoomed in. I’ve also learned how to change the by-zip data to by-county so I can look at larger territories. I hate to say it but this is kinda fun
I have an Excel spreadsheet with over 25,000 customer records consisting of zip codes in a column. Many of these are multiple customers living in the same zip codes. I am trying to show concentrations of customers in certain geographic areas, so that if there are 100 customers in the same zip code, it will show up as a bigger sales opportunity.
But when I go to map these zip codes, MapPoint only shows one pushpin per zip code, even if there are 100 customers living in the same zip code. And it won’t allow me to do anything other than a pushpin map for “location” data like zip codes. Any help you can offer would be appreciated.
Right, because you are plotting as a pushpin point. Instead, collate your data into Excel so that you have a data column that gives you the number of customers per zipcode. Then import this into MapPoint using the Data Import Wizard but choose “Shaded Area” (or one of the other area options – eg. sized circle). “Shaded Area” will then shade each zipcode according to the number of customers present in the zipcode.
Thanks, Richard. I do have a follow-up question. In my original Excel spreadsheet, I had to adjust the format of the cells containing zip codes so that those zips beginning with zero had a 0 in front of them. (These are mostly in New England. They were showing up as four digits instead of five in my original set of data.) Problem is, when they are then imported into MapPoint, MapPoint doesn’t recognize them as five digits and kicks them out as being “unmatched.” I have 219 unmatched zip codes, all beginning with zero (but not showing as such in MapPoint) and all in New England. Do I have to go through them one-by-one and match them, which will be quite “tedious,” to use your word?
Format them as a character field and not a numeric field – this is a classic example of why Excel can be bad for you (it automatically thinks zipcodes are integer numbers when they are not).
Thanks, Richard!
I have an excel spreadsheet with 600 businesses in one borough of London. I have managed to show the location of most of the businesses using the pushpin with mappoint Europe 2011. there is clearly still some issue with the address data for some as they are not being mapped. I have a couple of questions:
1. at the early stage using import data wizard I don’t have an option to ‘skip column’ so have to have the numerous columns in the spreadsheet as other data which I think may be causing a problem when I try to map ‘clusters’ of businesses. how do I get the skip column option?
2. As there are multiple businesses at single addresses I want to show this ‘clustering’ using shaded circles for street addresses or postcode area/districts (I’m not clear what the difference is). Is there an option to go down 6 or 7 digit postcodes in the UK? they are configured for example SE6 3DU. Should the data field I’m mapping be the unique identifier? If I select this and show the data by postcode I only get one business plotted. I can select postcode sector, district or area but then I get options to add values or count number of items and not sure what it is adding the value of or counting.
This may be a rather complicated reqeust but any help would be appreciated.
The skip column option should be in the drop down list where the columns are allocated. “Other data” should be fine – it will use more memory though.
If you wish to collate data so that you can plot totals (eg. as sized circles in areas), then you should collate it in Excel (or Access, or wherever) first.
Hi Richard, I’m wondering if there is any way to automatically connect pushpins on Mappoint. I see you can manually draw lines, but I’m wanting to automate (quickly and accurately) straight lines from Pushpins 2-30 to Pushpin 1 for example. Perhaps VB? Any suggestions?
Yes this would require custom programming.
And yes we could be hired to do it (use the contact form). One approach is to define the lines in an Excel sheet or text file – perhaps with the pushpins as well, and then program goes through, adding these to MapPoint.
I imported by data from an Excel file. I mapped my sales reps by zipcode and differentiated them by district pushpins. How can I get a pushpin for each rep? Some pushpins may have multiple reps attached as they share a zip. I need it by zip but want a pushpin for each rep.
You will need to give them different locations. In addition to a zipcode, you will need something like a street address. Alternative you could try to use geographic coordinates.
Multiple pushpins mapped at the same exact spot (i.e. the same zipcode with no other information) will always appear at exactly the same location.
I have an excel spreadsheet of individuals. Each one has an address, city, state and zipcode along with demographic data. I want to work with census tracts. How can I add census tracts to these addresses. Using the data mapping wizard I can get aggregate data which tells me how many counts are within each census tract but I want each address to list the census tract separately. Is there a way to do this?
No there isn’t. What you are requesting is a form of reverse geocoding. Typically reverse geocoding finds a street address from a coordinate, but finding a census tract from a pushpin is the same concept. It might be possible with custom programming but is non-trivial (it isn’t directly supported in the MapPoint API).