As a follow-up to last week's article on opening MS Excel files via the OGR Vector Files support that we added to MapInfo Pro v2021, I will show you how to automate this process using MapBasic.
@Gavin Thomas raised this question in a comment last week.
Happy #MapInfoMonday and #MapBasicMonday.
Grabbing MapBasic Code via the MapBasic Window
I always recommend using the MapBasic window to get an initial idea of how to structure your MapBasic code when automating some new features of MapInfo Pro.
Open the MapBasic window in MapInfo Pro via the Tool Windows dropdown on the Home tab.
Now perform the action that you want to automate. MapInfo Pro will write the statements you need to use to perform the actions into the MapBasic window. You only have to adjust these to your purpose.
Below you can see the statements recorded by the MapBasic window when opening an Excel file through the OGR Vector Files support.
Writing a MapBasic Application
Using the statement from the MapBasic window above, I can create a function allowing me to open a sheet from an MS Excel file into MapInfo Pro using OGR Vector File support.
I build my functions with the necessary parameters allowing me to control the name of the MS Excel, the sheet, and the name of the MapInfo table. I will also add a parameter that lets me control if the table should be hidden when opened.
Function OpenXLSXFileOGR( ByVal sExcelFile As String
, ByVal sExcelSheet As String
, ByVal sMapInfoTabFile As String
, ByVal bHideTable As Logical
) As Logical
OnError GoTo ErrorOccured
OpenXLSXFileOGR = FALSE
Print Time(24) & " Register OGR Table ..."
Register Table sExcelFile
TYPE "OGR"
TABLE sExcelSheet
DRIVER "XLSX"
Charset "UTF-8"
Cache On
Into sMapInfoTabFile
Print Time(24) & " Open OGR Table ..."
If bHideTable Then
Open Table sMapInfoTabFile Hide
Else
Open Table sMapInfoTabFile
End If
OpenXLSXFileOGR = TRUE
Exit Function
'-------------------
ErrorOccured:
Note "Error happended while trying to open sheet " & sExcelSheet & " from Excel file " & sExcelFile & ": " & Err() & ": " & Error$()
End Function
As you can see above, I use the Register Table statement to create a MapInfo table referencing a sheet in an MS Excel file. After this, I open the table with or without the Hide keyword depending on the bHideTable parameter.
This is only the first step which allows me to open a table pointing to the data in the MS Excel file.
I added the bHideTable parameter so that I can hide the table in case I'm opening the table to make a copy of it.
I have hard-coded the Charset to "UTF-8". You could also pass this as a parameter to make the function more flexible.
With this function in place, I can build the main procedure where I open the Excel file and make a copy of it. This part will look like this:
Sub Main
Dim sExcelFile, sExcelSheet, sMapInfoTabFile, sTempFile As String
sExcelFile = "C:\Data\2023\2023-Q1-metropolitan-street.xlsx"
sExcelSheet = "2023-03-metropolitan-street"
sTempFile = PathToDirectory$(TempFileName$("")) & "_tempfile.tab"
sMapInfoTabFile = "C:\Data\2023\Metropolitan street 2023-03.tab"
If OpenXLSXFileOGR(sExcelFile, sExcelSheet, sTempFile, TRUE) Then
Print Time(24) & " Commit Table As ..."
Commit Table _tempfile
As sMapInfoTabFile
Type NATIVEX Charset "UTF-8"
Close Table _tempfile
Print Time(24) & " Open NativeX Table ..."
Open Table sMapInfoTabFile
Print Time(24) & " Browse from NativeX Table ..."
Browse * From PathToTableName$(sMapInfoTabFile)
End If
End Sub
I start by defining some variables holding the name of the files and sheet.
Notice that I use a temporary file name for the initial table created to open the MS Excel file into MapInfo Pro. That is because I don't want to keep this file. It's only there to be able to open the MS Excel file and make a copy of it.
If call my custom function OpenXLSXFileOGR to open the MS Excel file. If this returns TRUE, I can now create a copy of the table, close the temporary table, and finally open the copy that I created.
I use the Print statements in the code to check that things work and the Time(24) part of these statements allows me to monitor the performance of each step.
I hope this was helpful.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------