View Full Version : Using ImportData and DisplayDataMap - Part 1 of 3 - The ImportData Method

Richard Marsden
12-12-2011, 10:55 AM
by Richard Marsden

Some of the most complex and difficult to use methods in MapPoint’s programming interface (API) are those that import data and plot it.

Data importing is generally not too difficult, but ideally you should know what the input data looks like beforehand. This is in contrast to the user interface (“Data Import Wizard”) which lets you select data columns and assign them interactively.

The real problem is when you try to plot the data. This uses the DisplayDataMap method, which is probably the most complex method in MapPoint’s entire API. Just to make it harder to use, any incorrect parameters result in the same un-informative “The parameter is incorrect” error. This error does not tell you anything useful, such as which parameter is incorrect or what is actually wrong with it.

This article looks at the ImportData and DisplayDataMap methods, demonstrating how they should be used. Samples use C# code with MapPoint 2010, and a corrected version of the Sales.txt file that can be found in MapPoint’s Samples directory. This can be downloaded here (URL: http://mappingtools.s3.amazonaws.com/mp/howto/Sales.txt (http://mappingtools.s3.amazonaws.com/mp/howto/Sales.txt) ).


The API equivalent of the Data Import Wizard is the Datasets.ImportData method. Despite the complexities of the import wizard, this method only requires five parameters:

MapPoint.DataSet ds = myDatasets.ImportData( dsMoniker, arrayOfFields, eCountry, eDelimiter, nImportFlags)

dsMoniker is a text string that holds the name of the input data file. A text file simply requires a pathname, but Excel and Access files can include worksheet or table information. The syntax for these extensions is described in the MapPoint documentation.

arrayOfFields is a two column array that specifies the data fields and how they are to be interpreted. Each row in this array represents a data field. The first column lists the field names. The second column is typically a GeoFieldType enum that specifies how the column is to be interpreted/geocoded; a string (the name for the data field in MapPoint); or blank (let MapPoint decide what to do). MapPoint will attempt to import and interpret all data fields if this parameter is not supplied.

eCountry is an optional GeoCountry enum value that specifies the country. Default is geoCountryDefault.

eDelimiter is an optional GeoDelimiter value that specifies the field delimiter for text files. It is ignored for other file types. Acceptable values are geoDelimiterDefault, geoDelimiterComma, geoDelimiterSemicolon, and geoDelimiterTab.

Finally, nImportFlags is an optional long integer value. This is an ‘OR’ combination of GeoImportFlags values that specify details about the dsMoniker specification for Access and Excel data, and whether the first data row contains field headings or not.

Here is a C# console example that loads the Sales.txt sample data:

// Used when calling COM interfaces when using parameter defaults
object missing = System.Reflection.Missing.Value;

// Start MapPoint and make it visible
MapPoint._Application mpApp = new MapPoint.Application();
mpApp.Visible = true;
MapPoint.Map mpMap = mpApp.ActiveMap;

string filename = @"C:\Program Files (x86)\Microsoft MapPoint 2010\Samples\Sales.txt";

MapPoint.DataSet ds = null;
object[] datafields = null;
MapPoint.DataMap datamap = null;

// Define field specifications
// All fields must be defined, and defined correctly
object[,] fieldSpecifications = new object[6, 2];
fieldSpecifications[0, 0] = "ID";
fieldSpecifications[0, 1] = MapPoint.GeoFieldType.geoFieldSkipped;

fieldSpecifications[1, 0] = "State";
fieldSpecifications[1, 1] = MapPoint.GeoFieldType.geoFieldRegion1;

fieldSpecifications[2, 0] = "Country";
fieldSpecifications[2, 1] = MapPoint.GeoFieldType.geoFieldCountry;

fieldSpecifications[3, 0] = "Our Sales ($)";
fieldSpecifications[3, 1] = MapPoint.GeoFieldType.geoFieldData;

fieldSpecifications[4, 0] = "Competitor A Sales ($)";
fieldSpecifications[4, 1] = MapPoint.GeoFieldType.geoFieldData;

fieldSpecifications[5, 0] = "Competitor B Sales ($)";
fieldSpecifications[5, 1] = MapPoint.GeoFieldType.geoFieldData;

ds = mpMap.DataSets.ImportData(filename, fieldSpecifications,
MapPoint.GeoImportFlags.geoImportFirstRowIsHeading s);

// list field information
Console.WriteLine("Fields read: {0}\nFields (1-ref):", ds.Fields.Count);
int i = 1;
foreach (MapPoint.Field fld in ds.Fields)
Console.WriteLine(" {0}: {1}",i++, fld.Name);

// (data plotting code goes here)

// tidy up
ds = null;
datamap = null;
datafields = null;

catch (Exception e)
Console.WriteLine("Exception: {0}\n {1}", e.Message, e.Source);
Console.Write("Press return to exit");

Console.Write("Press return to exit");

// shut mappoint down
mpMap.Saved = true;
mpMap = null;
mpApp = null;

This example starts a MapPoint application instance, and loads the sales data. It also queries and displays the available fields in the new dataset – this is primarily for diagnostic purposes. It will prove useful when we are trying to create a data map from the data. The data is loaded as a simple pushpin set, but the DisplayDataMap method can be used to change this (see below).

The call to ImportData sets the country to the US, and that the text file is tab-separated with headings defined in the first row. The required data fields are also explicitly listed (in the fieldSpecifications array). The main thing you have to be wary of is that the field specifications have to be exact. A mis-spelt or mis-specified field column can result in a field being skipped, or an exception (error) being thrown.

If you are not sure what the fields are, you can let MapPoint decide. Do this by passing System.Reflection.Missing.Value(variable ‘missing’) instead of the field specifications array:

ds = mpMap.DataSets.ImportData(filename,
missing, // fieldSpecifications,
MapPoint.GeoImportFlags.geoImportFirstRowIsHeading s);

This produces an identical result to the original code with the explicit field specifications. In the general case, it will read all available fields, and MapPoint will try to decide which hold useful geographic information. Geographic information will only be correctly recognized if you use standard names (e.g. “Longitude”). In general, it is best to provide the field specifications so that all geographic fields are correctly recognized.

This example is partially based on the “Working with Your Business Data” samples in chapter 3 of Chandu Thota’s “Programming MapPoint .NET” (purchase link (https://www.paypal.com/xclick/business=website%40mp2kmag.com&item_name=Programming+MapPoint+in+.NET&amount=48.00)). Readers interested in the correct use of Excel worksheets in ImportData are referred to this chapter for further information and sample code.

Continue Reading Using ImportData and DisplayDataMap - Part 2 of 3 - Introducing DisplayDataMap (http://www.mapforums.com/using-importdata-displaydatamap-part-2-3-introducing-displaydatamap-18538.html)

12-13-2011, 09:05 AM
I wrote this three part article :-)

A Python version is "under development" but will be much briefer.

Eric Frost
12-13-2011, 11:57 AM
Properly attributed to you now :-)