MapInfo Pro Developers User Group

Expand all | Collapse all

Import Excel with all fields as Char using Register Table

  • 1.  Import Excel with all fields as Char using Register Table

    Posted 02-07-2019 15:43

    Is there a way to force MapInfo to register an Excel file with all fields as Char using the Register Table command?

    MapInfo is 'helpfully' assigning the Float data type to a column that contains data that is all numbers but some values have leading zeros. This is an ID field and needs to be imported as Char. Looking at the help, there isn't a way to do this.

    Has anyone found a workaround?

    Thanks!

    John Hollingsworth

    P.S. Acceptable answers do not include, "Just edit the Excel file before you import it to have an alpha character in that field"



    ------------------------------
    John Hollingsworth
    Clear Channel Outdoor
    ------------------------------


  • 2.  RE: Import Excel with all fields as Char using Register Table

    Pitney Bowes
    Posted 02-08-2019 06:47
    In Excel , format the fields as ZipCode so they are treated as character.  MIPro uses the Access driver to read Excel format files and it tells us what the fields are.  If you open these interactively via File>Open user can change fields types before completion, but right now there is no way to force MIPro to make certain columns numeric, character, etc., via Register table statemetn in MapBasic.

    ------------------------------
    Bill Wemple
    Principal QA Engineer
    Pitney Bowes
    Troy, NY
    ------------------------------



  • 3.  RE: Import Excel with all fields as Char using Register Table

    Posted 02-08-2019 07:35
    How about registering the Excel file to generate a TAB file, and then programmatically modifying the TAB file so that all the column definitions are set to Char?

    ------------------------------
    Andrew Harfoot
    GeoData
    University of Southampton
    ------------------------------



  • 4.  RE: Import Excel with all fields as Char using Register Table

    Posted 02-08-2019 09:23

    I've done this, but unfortunately, when there are values with leading zeros either Excel or MapInfo replaces the entire value with a zero. So data is lost and cannot be recovered.

    It seems to me like in the past in this circumstance that MapInfo would convert the field to Float and keep all of the digits except for the leading zero. That was easy because I could just convert the field to Char and then replace the leading zero. But now they are all 0 values.

    Does anyone know if there was an update to the Access 2010 data drivers that Microsoft may have recently pushed that would be causing this new behavior?




    ------------------------------
    John Hollingsworth
    Clear Channel Outdoor
    ------------------------------



  • 5.  RE: Import Excel with all fields as Char using Register Table

    Pitney Bowes
    Posted 02-08-2019 08:20
    ​As Bill has noted, we are told what types these are by the Microsoft software. For this problem, just go into the spreadsheet and define the columns that are ambiguous as Text. Right mouse on the column, Choose  "Format Cells" and choose Text instead of General or whatever else it is.
    That seems to convince the driver and then MapInfo Pro will do the right thing including the width determinations.
    Editing or writing code to mess with the .tab is always an option but then mistakes will be made, yes?


    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 6.  RE: Import Excel with all fields as Char using Register Table

    Posted 02-08-2019 09:26
    Unfortunately, given the volume of spreadsheets involved, manual editing isn't feasible.

    ------------------------------
    John Hollingsworth
    Clear Channel Outdoor
    ------------------------------



  • 7.  RE: Import Excel with all fields as Char using Register Table

    Posted 02-08-2019 10:31
      |   view attached
    I isolated the problem field into its own spreadsheet. Now when I try to import I get the Microsoft Access warning as shown below. It seems like it is telling me that the Float type won't accept the data. Which makes me wonder why Excel/Access chose Float in the first place.

    I'm starting to think the problem lies in how the input spreadsheet is formatted before it comes to me. However, when I check the cell formatting it is set to General. I'm confused.



    For those following this thread, what happens when you try to Open the attached spreadsheet (test2.xlsx) in MapInfo when leaving the given Float option in place?

    For reference, I'm running MIPro 15.0.3 on Windows 8.1 Pro.

    I've coded MapBasic for 20 years so technically complex replies are welcome.

    Thanks for your help.

    John

    ------------------------------
    John Hollingsworth
    Clear Channel Outdoor
    ------------------------------

    Attachment(s)

    xlsx
    test2.xlsx   10K 1 version


  • 8.  RE: Import Excel with all fields as Char using Register Table

    Pitney Bowes
    Posted 02-08-2019 12:23
    ​I am getting the same error as you if I open it as float. I believe it is the empty cells that are causing the problem but I will check.
    I have no problem opening as character.

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 9.  RE: Import Excel with all fields as Char using Register Table

    Posted 02-08-2019 12:44

    Thanks for testing Eric.

    I removed the empty cells and retested and got the same error message.

    Back to the drawing board...



    ------------------------------
    John Hollingsworth
    Clear Channel Outdoor
    ------------------------------



  • 10.  RE: Import Excel with all fields as Char using Register Table

    Pitney Bowes
    Posted 02-08-2019 13:08
    ​Same for me. It seems the values while still general, are actually text. In my excel, each cell below the first bunch has a little triangle that indicates that this field is actually text and therefore fails as a number. When I select the convert to number option it converts them all and removes the leading zeroes.  The table then opens as float but again, all the zeroes are gone.
    My summation is that you can't read text data a float without error and you can't maintain leading zeroes when using numbers.
    You can't format numbers in excel so that they have leading zeroes, but MapInfo does not see that format so the leading zeroes disappear.

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 11.  RE: Import Excel with all fields as Char using Register Table

    Pitney Bowes
    Posted 02-08-2019 13:39
    I meant to say "You can format numbers in excel so that they have leading zeroes" but....Oops​

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------