Hi Alban
Thanks for reaching out again. I just checked the ticket and unfortunately, I do not bring good news.
There is a limitation in the Microsoft Driver that we use which limits the number of columns that can be accessed to 255 columns.
This means that it wasn't possible for us to fix this as easily as I had hoped when I suggested that we might be able to get this into a patch for v2019. I'm sorry about raising your hope on this.
For v2021, we are looking into other ways to read Excel files but if that could be an option is still too early to say.
I did also dive a bit more into the options for you here and now.
The limitation seems to be on the access. You can't access more than 255 columns at a time. But you can access the columns beyond the 255th column but you will have to read these in multiple steps.
As
@Lee Crosby suggested above, you could introduce an ID column and have this say as the first column, and as the 251st column (IQ in Excel).
This will allow you to read in the first 250 (up to and including column IP), and then the remaining columns from IQ and up.
I tried it and it gave me these two Register Table statements:
Register Table "D:\1. Support\test_315 columns.xlsx"
TYPE XLS Titles Charset "UTF-8"
Range "Sheet1!A2:IP2"
Interactive Into "C:\Temp\~MAP0005.TAB"
Register Table "D:\1. Support\test_315 columns.xlsx"
TYPE XLS Titles Charset "UTF-8"
Range "Sheet1!IQ2:LC2"
Interactive Into "C:\Temp\~MAP0006.TAB"
As Lee also mentioned the tables would be opened under the same name if you use the interface so you'd have to save the first table under a different name.
I ended up creating a MapBasic script that can help you overcome this limitation. It currently assumes the following:
- The first row in your Excel file holds the column heading
- The ID columns are at column A and IQ in your Excel file
- The last column in your Excel file is the column "LC"
- The resulting tab file will be saved in the same folder as the Excel file using the same file name.
You can modify these values to better match your Excel file. To do so, load the script into the new SQL Window.
When you run the script it will ask the user to select the Excel file to open, and for the number of rows in the Excel file.
As part of the opening process, you can modify the column types and column names if needed. Just like you can do when manually opening data from Excel.
It will open the data from Excel into two tables, join the two tables on their first column and save the join into a new table.
Then the temporary tables will be deleted and the resulting table will be opened.
I have included a zipped version of the script that you can put into your
Saved Scripts folder. Pro will then autodetect it. It's called
Load Excel with many columns. It uses variables to ask for the Excel file to open and the number of rows.
I know this isn't the best solution but hopefully, it can help you get your data from Excel into MapInfo Pro. PS: The script does require one of the MapInfo Pro v2019.3 builds. You can grab the latest from here:
MapInfo Pro v2019.3 Build 45.
Let me know how the script works for you.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------
Original Message:
Sent: 03-11-2021 02:00
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello Peter,
Are there been any progress on a patch for that bug?
Thanks
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
Original Message:
Sent: 02-03-2021 02:32
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello Peter,
Thanks for the support.
Happy to test a patch once available.
Best regards
Alban
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
Original Message:
Sent: 02-03-2021 02:28
From: Peter Horsbøll Møller
Subject: import excel file >= 255 columns
Hi Alban
Yeah, this is very unfortunate.
Our support case# is PRE-00214910.
We have engineering picking up this issue as we speak.
We are planning on another build of MapInfo Pro v2019.3 to be posted on our web site in February.
At the moment, I can't tell if we can get a fix for this into that release.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 02-02-2021 17:32
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello Peter,
Is there a ticket number from support?
How can we track progress?
I'd like to get in touch with our local product manager about this. This has been going on for more than one year. We were told 2019 would fix it, then 2019.1 and now on 2019.3, the issue is still there.
Thanks.
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
Original Message:
Sent: 02-02-2021 06:05
From: Peter Horsbøll Møller
Subject: import excel file >= 255 columns
Hi Alban
You are right. It doesn't work. Sorry that I didn't verify the workaround better before sharing it.
Let's see what support comes back with
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 02-02-2021 03:32
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello Peter,
We tried the workaround today without much luck:
Using your method, we enter the range manually. The output was:
- extra columns (>255) were created but were empty
- extra columns (>255) name were blank
It didn't work.
Have you tried with a mix of character and integer columns for example?
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
Original Message:
Sent: 02-01-2021 04:30
From: Peter Horsbøll Møller
Subject: import excel file >= 255 columns
Hi Alban
Yeah, I can reproduce the issue.
It seems that the ability to detect the area to import is still somewhat limited to the 255 columns.
I can however import the full dataset if I manually overwrite this.
First I specify to use the full extent of a worksheet and check the Use Row above Selected Range for Column Titles.
Now From the Named Range list, I select Other, and in the Other Range dialog, I change the value to the full extent of the data in Excel.
In the example below, I have changed it from "IU5" to "VT5"
When I now move on with the process, MapInfo Pro loads the full dataset from Excel and gives me a table with 592 columns.
Sorry about the bit windy workaround here. I'll pass this onto support so that we can get this fixed fully.
Thanks for highlighting this issue (again), Alban.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 02-01-2021 04:21
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello,
I am on v2019.3 :(
looks like the problem is still there and yes I am using the extended tab format.
how to get a ticket raised?
thanks
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
Original Message:
Sent: 02-01-2021 04:06
From: Peter Horsbøll Møller
Subject: import excel file >= 255 columns
Hi Alban
According to this discussion (Open excel file 250+ columns), this should have been fixed in MapInfo Pro v2019.1.
You raised the issue in the thread a year ago.
Have you been able to try out MapInfo Pro v2019.1?
You still need to ensure that you are using the Extended Tab format.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 02-01-2021 00:42
From: Alban Spella-Barberet
Subject: import excel file >= 255 columns
Hello,
- Is there a way to import excel file having > 255 columns please?
- I tried converting to csv and import. It might work but the issue is that I have large integer which are converted and rounded to scientific notation which is not what I want. Is there a workaround on this?
Thanks.
------------------------------
Alban Spella-Barberet
NBN Co Limited
North Sydney NSW
------------------------------