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
------------------------------
Original Message:
Sent: 02-08-2019 12:43
From: John Hollingsworth
Subject: Import Excel with all fields as Char using Register Table
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
Original Message:
Sent: 02-08-2019 12:23
From: Eric Blasenheim
Subject: Import Excel with all fields as Char using Register Table
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
Original Message:
Sent: 02-08-2019 10:31
From: John Hollingsworth
Subject: Import Excel with all fields as Char using Register Table
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
Original Message:
Sent: 02-07-2019 15:43
From: John Hollingsworth
Subject: Import Excel with all fields as Char using Register Table
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
------------------------------