MapInfo Pro

During our Ultranyx MapInfo Pro Day in London last week and a workshop the week before, I encountered questions about opening MS Excel data into MapInfo Pro. In this article, I will cover a few ways to work with MS Excel data in MapInfo Pro.

Happy #MapInfoMonday!

The Standard Way

MapInfo Pro has been able to open MS Excel files for decades. MS Excel is a common format for holding data that you want to analyze in MapInfo Pro.

From the Open dialog, you select Microsft Excel (*.xls, *.xlsx) from the Files of type list.

If your data also holds column names in the first line, check the Use Row Above Selected Range for Column Title option. If you have selected a full worksheet, MapInfo Pro will automatically change the data area to switch from line 1 to line 2.

Finally, you will presented with the Set Field Properties dialog. Here you can check the column names and types and adjust these if you want them to be different from what MapInfo Pro has gotten from the MS Excel file.

Click the OK button to load the data.

The MS Excel data has now been loaded and will typically be shown in a browser.

If your data holds spatial data as coordinates or lat/long values, you can now use Create Points to create a point for the individual records. Read more about the Create Point process in this article: MapInfo Monday: Creating Points from XY Coordinates.

This will allow you to show the data from MS Excel on a map.

The New Way via OGR Vector Files

With the OGR Vector File Support,  we added to MapInfo Pro v2021, there is now a new way to open Excel files.

From the Open dialog, select Vector Files (OGR) (*.dxf, *.csv, *.txt, ...) from the Files of type list. The formats listed here have been tested with MapInfo Pro. You can also get a complete list of GDAL OGR formats listed but we haven't tested all these so you may run into issues with some of them. But give them a try. If there is a specific format, you would like to see support, add it as a suggestion on the Precisely Ideas site.

Now select the MS Excel file you want to open.

There are two ways that you open the Excel data: You can open the data directly or you can save a copy of the data into a MapInfo table.

If you open it directly, you can't make it mappable and create points from any potential coordinates in the data. However, It will allow you to edit the data and make changes directly in the MS Excel file.

You can also create a copy of the data in a MapInfo table. This is done by clicking on the Open As MapInfo Table button in the Select One or More Tables to Open dialog after selecting the tables that you want to open.

Or by selecting the tables to open and then clicking on the Options button, and from the Vector File (OGR) Open Options dialog selecting the Save Copy as MapInfo Table option.

Saving the Excel data into MapInfo tables runs as background threads that you can monitor in the Tasks window.

Once the data has been saved into MapInfo tables, the tables will get opened into MapInfo Pro and typically viewed in browser windows.

To create points on the data, use the Create Point control from the Table context menu in the Table List window.

And now, you can view the data in a map in MapInfo Pro.

Comparing the two methods

Using OGR Vector Files allows you to easily open multiple worksheets from the same MS Excel file into MapInfo Pro.

Using OGR Vector Files allows you to edit the Excel data in MapInfo Pro. But you can then not make the table mappable and see the data on a map.

Both methods allow you to create points from coordinates in the data. None of the two methods can automatically create points from the data. This is a manual step.

Opening the Excel file via the standard way allows you to check the column types and names. In both cases, you can always use the Table Structure dialog to change the column types and column names after saving the data into a MapInfo table.

Which of the two methods are you using when opening MS Excel data?

Thanks, this is really interesting.

I was wondering, could you share an example of the MapBasic code to open the above Excel file via OGR Vector Files? I've tried the above process with the MapBasic window open but it didn't echo what the commands would have been.

Many thanks.

During our Ultranyx MapInfo Pro Day in London last week and a workshop the week before, I encountered questions about opening MS Excel data into MapInfo Pro. In this article, I will cover a few ways to work with MS Excel data in MapInfo Pro.

Happy #MapInfoMonday!

The Standard Way

MapInfo Pro has been able to open MS Excel files for decades. MS Excel is a common format for holding data that you want to analyze in MapInfo Pro.

From the Open dialog, you select Microsft Excel (*.xls, *.xlsx) from the Files of type list.

If your data also holds column names in the first line, check the Use Row Above Selected Range for Column Title option. If you have selected a full worksheet, MapInfo Pro will automatically change the data area to switch from line 1 to line 2.

Finally, you will presented with the Set Field Properties dialog. Here you can check the column names and types and adjust these if you want them to be different from what MapInfo Pro has gotten from the MS Excel file.

Click the OK button to load the data.

The MS Excel data has now been loaded and will typically be shown in a browser.

If your data holds spatial data as coordinates or lat/long values, you can now use Create Points to create a point for the individual records. Read more about the Create Point process in this article: MapInfo Monday: Creating Points from XY Coordinates.

This will allow you to show the data from MS Excel on a map.

The New Way via OGR Vector Files

With the OGR Vector File Support,  we added to MapInfo Pro v2021, there is now a new way to open Excel files.

From the Open dialog, select Vector Files (OGR) (*.dxf, *.csv, *.txt, ...) from the Files of type list. The formats listed here have been tested with MapInfo Pro. You can also get a complete list of GDAL OGR formats listed but we haven't tested all these so you may run into issues with some of them. But give them a try. If there is a specific format, you would like to see support, add it as a suggestion on the Precisely Ideas site.

Now select the MS Excel file you want to open.

There are two ways that you open the Excel data: You can open the data directly or you can save a copy of the data into a MapInfo table.

If you open it directly, you can't make it mappable and create points from any potential coordinates in the data. However, It will allow you to edit the data and make changes directly in the MS Excel file.

You can also create a copy of the data in a MapInfo table. This is done by clicking on the Open As MapInfo Table button in the Select One or More Tables to Open dialog after selecting the tables that you want to open.

Or by selecting the tables to open and then clicking on the Options button, and from the Vector File (OGR) Open Options dialog selecting the Save Copy as MapInfo Table option.

Saving the Excel data into MapInfo tables runs as background threads that you can monitor in the Tasks window.

Once the data has been saved into MapInfo tables, the tables will get opened into MapInfo Pro and typically viewed in browser windows.

To create points on the data, use the Create Point control from the Table context menu in the Table List window.

And now, you can view the data in a map in MapInfo Pro.

Comparing the two methods

Using OGR Vector Files allows you to easily open multiple worksheets from the same MS Excel file into MapInfo Pro.

Using OGR Vector Files allows you to edit the Excel data in MapInfo Pro. But you can then not make the table mappable and see the data on a map.

Both methods allow you to create points from coordinates in the data. None of the two methods can automatically create points from the data. This is a manual step.

Opening the Excel file via the standard way allows you to check the column types and names. In both cases, you can always use the Table Structure dialog to change the column types and column names after saving the data into a MapInfo table.

Which of the two methods are you using when opening MS Excel data?