MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Opening Excel Files via Python

  • 1.  MapInfo Monday: Opening Excel Files via Python

    Employee
    Posted 07-07-2025 05:27

    A few weeks ago, @Michiel Zevenbergen from our Spanish partner GeoTáctica sent me a question:

    "When opening the newer Excel files (XLSX) through MapBasic, I need to specify a sheet name. How do I go about this without hardcoding the sheet name in the source code?"

    The simple solution is to prompt the user to specify the sheet name. That can be done with a simple Input() function. 

    Dim sSheetName As String
sSheetName = Input("Sheet Name", "Sheet Name", "Sheet1", "string")

    This will show a small dialog to the user where the sheet name can be entered. The default value has been set as Sheet1.

    This is a fundamental solution that is easy to implement. It will also work well as long as the sheet names are known to the user or are the same in the Excel files.
    A better solution would prompt the user to select a sheet name from a list of existing sheets in the Excel file.
    So, how could you make that work?
    Again, I found myself walking down the Python path.
    The Python module Pandas can be used to work with Excel files. Pandas does, for some work, rely on another Python called openpyxl.
    MapInfo Pro comes with Pandas preinstalled. However, you must install the openpyxl module yourself to use it in MapInfo Pro. To do so, launch MapInfo Pro with elevated rights by right-clicking on the shortcut and selecting Run as Administrator.
    Now open the Python Console in MapInfo Pro or use the Python type in the SQL Window and execute this command:
    install_module('openpyxl')
    This will download and install the module if it isn't already available.
    Having Pandas and openpyxl available allows you to quickly get a list of sheets in an Excel file:
    # Load the Excel file
# using the "with" structure ensures that the file gets closed when done
with pd.ExcelFile(fileExcel) as excel_file:
    # Get the sheet names
    sheet_names = excel_file.sheet_names
    It's just a single line of code, but above I wrapped it into a with structure, which ensures the Excel file gets closed afterward.
    That's it. The variable sheet_names now holds a list of the sheet names in the Excel file.
    All that is left is to prompt the user to initially select the file and then prompt the user to select the sheet to open. And finally, open the Excel file in MapInfo Pro.
    Here is the entire Python script
    #install_module('openpyxl')

import pandas as pd
import os

# Path to your Excel file
fileExcel = eval('FileOpenDlg("", "", "XLSX", "Select Excel File to Open...")')

if fileExcel == '':
    raise SystemExit('No Excel file was selected by user!')

# Load the Excel file
# using the "with" structure ensures that the file gets closed when done
with pd.ExcelFile(fileExcel) as excel_file:
    # Get the sheet names
    sheet_names = excel_file.sheet_names

sheet_names_with_semicolon = ';'.join(sheet_names)
cmd = 'Input("Select Sheet", "Sheet", "{0}", "list", "", "{1}")'.format(sheet_names[0], sheet_names_with_semicolon)
#print(cmd)
sheetName = eval(cmd)

if sheetName == '':
    raise SystemExit('No Sheet Name was selected by user!')

# Get directory path
pathFiles = os.path.dirname(fileExcel)
# Path to the new TAB file
fileTAB = eval('FileSaveAsDlg("{0}", "{1}", "TAB", "Specify name of TAB file...")'.format(pathFiles, sheetName))

#Register Table source_file Type XLS 
#[ Charset char_set ] [ Titles ] #[ Range range_name ] [ into destination_file ] [ Interactive ]
cmd = 'Register Table "{0}" Type XLS Titles Range "{1}" into "{2}"'.format(fileExcel, sheetName, fileTAB)
#print(cmd)
do(cmd)

table = pro.Catalog.OpenTable(fileTAB)
do('Browse * From {0}'.format(table.Alias))

    A couple of improvement ideas:

    1. If the Excel file only has one sheet, just use that sheet and don't prompt the user to select it.
    2. Just save the MapInfo TAB file next to the Excel file. Don't prompt the user to select where to save the TAB file.

    Thanks for reading to the end of the article. I hope you found it useful. If you are looking for additional #MapInfoMonday articles, you can find a categorized list of all articles here: Home of #MapInfoMonday

    Have a great week!



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------