Code for defining columns in an excel sheet on open

22 views
Skip to first unread message

Matthew Hirsch

unread,
Feb 19, 2022, 12:25:20 PMFeb 19
to Digest recipients

I have an excel file that I am trying to write a MB script so I can open it and not have to define the three columns each time.     This script is pasted into the MB code window and not run in an MBX.

 

The file is called zips.xlsx.  

Column 1 is Zipcode which needs to be set to character 5.

Column 2 is Date which needs to be set to date, not date/time

Column 3 is Sales which needs to be set to float.

 

I can get the file to open in MapInfo, but it always prompts for column types.

 

I’ve looked around and cannot find anything clean on how to do this.

Thank you.

Matthew

 

Marijan

unread,
Feb 24, 2022, 7:53:07 AMFeb 24
to MapInfo-L
Matthew, 

I don' think you can do this programmatically. As MapInfo Reference states, only parameters you can use are charset, titles and range. One thing you can do is to create MapBasic (or any other supported language code) program  to generate TAB file for your XLS with desired field types.

Let's say you create template TAB file:

!table
!version 1000
!charset WindowsLatin2


Definition Table
  File "MyExcelFile.xlsx"
  Type XLS Titles Range "Sheet1!A2:C2"
  Fields 3
    ZIP_code Char(5);
    Date Date ;
    Sales Float ;
begin_metadata
"\IsReadOnly" = "FALSE"
"\MapInfo" = ""
"\MapInfo\TableID" = "5e3900ea-baf2-4677-95a4-51d4746c6183"
end_metadata


As you can see, you could probably create program that would do search and replace of word "MyExcelFile.xlsx" with your xls file name, and place newly created TAB at same location as your xls file. One thing you should take care of is  MapInfo\TableID parameter, it is unique for every table. Try removing it completely - it should work fine. Off course, sheet name and range should be same in every xls. It would be best to take one TAB that you have already created and use it to create template.
Reply all
Reply to author
Forward
0 new messages