MapInfo Pro

Back to discussions
Expand all | Collapse all

import excel file >= 255 columns

  • 1.  import excel file >= 255 columns

    Posted 02-01-2021 00:43
    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
    ------------------------------


  • 2.  RE: import excel file >= 255 columns

    Posted 02-01-2021 04:07
    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


  • 3.  RE: import excel file >= 255 columns

    Posted 02-01-2021 04:22
    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


  • 4.  RE: import excel file >= 255 columns

    Posted 02-01-2021 04:31
    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


  • 5.  RE: import excel file >= 255 columns

    Posted 02-02-2021 03:32
    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


  • 6.  RE: import excel file >= 255 columns

    Posted 02-02-2021 06:06
    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


  • 7.  RE: import excel file >= 255 columns

    Posted 02-02-2021 17:33
    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


  • 8.  RE: import excel file >= 255 columns

    Posted 02-02-2021 20:33
    Hi Alban,

    Another workaround is to create an unique ID column in excel, then split the table across multiple excel files (MapInfo will complain the table is already open if you try to import a sheet from the same file). Make sure you keep the id column in both files, this will be how the table is re-joined in MapInfo.

    Once in MapInfo, run a SQL query something like
    Select *
    From large_number_of_columns_table, large_number_of_columns_table2
    where large_number_of_columns.id_column = large_number_of_columns2.id_column
    Into Selection

    Kind regards,

    ------------------------------
    Lee Crosby
    Campaspe Shire Council
    Echuca VIC
    ------------------------------

    Original Message


  • 9.  RE: import excel file >= 255 columns

    Posted 02-02-2021 20:49
    Hello Lee,

    Yes, that's a good workaround.

    Thanks.

    ------------------------------
    Alban Spella-Barberet
    NBN Co Limited
    North Sydney NSW
    ------------------------------

    Original Message


  • 10.  RE: import excel file >= 255 columns

    Posted 02-03-2021 02:29
    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


  • 11.  RE: import excel file >= 255 columns

    Posted 02-03-2021 02:33
    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


  • 12.  RE: import excel file >= 255 columns

    Posted 03-11-2021 02:01

    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


  • 13.  RE: import excel file >= 255 columns

    Posted 03-11-2021 04:20
      |   view attached
    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
    ------------------------------

    Attachment(s)

    zip
    Load Excel file with many columns.zip   880 B 1 version
    Original Message


  • 14.  RE: import excel file >= 255 columns

    Posted 03-12-2021 00:53
    Thanks Peter for the detailed reply and attachment.

    ------------------------------
    Alban Spella-Barberet
    NBN Co Limited
    North Sydney NSW
    ------------------------------

    Original Message