MapInfo Pro

Folks I've been successful in using V16 to connect to an XLSX as a linked data source. However I ran into a small issue where the data content width exceeded the standard 254 characters for a single column. This didn't stop the table from opening but it did corrupt the display of the data to underscores and other "shifted number" characters. To get around this I used "Expert Mode" to hand edit SQL select query and added the following

Select Left$('LONG DESC',250) from.....

Now this worked however the column name was changed to "Exp0112' or something similarly generic.

In the same Select statement I'd already used

Select 'UID' as 'ID'

So I presumed the following would work

Select Left$('LONG DESC',250) as 'LONG DESC' from.....

However I get the following error

See Attachment

Now it's seems pretty clear what the error is. The "LONG DESC" is used twice but I'm confused as to what to do. I can't change the spreadsheet column name. This is defined by the client and the code that uses the Linked table is looking for the "LONG DESC" column name in the table.

Any thoughts?

Hi Andrew,

Can you check to your Table Structure and make sure you are within the 4096 character limit? See if you can minimize the character limit in the fields and make sure the sum of the width does not exceed 4096.

The byte limit is now increased to 65,534. Due to other limits, it is actually impossible to hit this limit when creating a table in MapInfo Professional. We have a limit of 250 columns and a limit of 254 characters in a column. Therefore, the longest record which can be created at present is 63,500 bytes (250 x 254).

Table Versions

Since older versions of MapInfo would either crash or behave oddly when attempting to open tables with record lengths exceeding the old 4000-byte limit, tables which exceed the old limit will have a version number of 1200. Older versions of MapInfo will not be permitted to open such tables.

Also, please check this journal article reference on linking Excel via ODBC:

The following link is no longer available. To access this information, copy the title found in the url into the search tool on the top nav bar, or try searching for key words in the post. https://li360.pitneybowes.com/s/article/ka180000000DAeoAAG/Something-a-bit-more-Advanced-Linking-to-an-Excel-file?s1oid=00D80000000KkyB&OpenCommentForEdit=1&s1nid=0DB80000000L2J0&emkind=chatterPostNotification&s1uid=005800000040E4n&emtm=1507093953486&fromEmail=1&s1ext=0

Thanks Matt.

I'm super familiar with the limitations you describe. I've been using Mapinfo for 25 years.

I should probably have been a little more specific with my question.

I was looking for a supported syntax that achieve what I was after. I'm quite familiar with TSQL and Mapinfo's subset but this seemed to be an issue of identifying the field vs the alias issue.

However I was able to leverage a Mapinfo peculiarity to get my solution.

I was able to add\remove the underscores from one or the other of the terms used and rely on Mapinfo to insert the underscores in the final column name as spaces are unsupported.

Both of these are supported syntax. Note the udnerscores

Select Left$('LONG_DESC',250) as 'LONG DESC' from.....

Select Left$('LONG DESC',250) as 'LONG_DESC' from.....

This changes the reference enough to have the parser recognise these as different and once opened in Mapinfo the 'LONG_DESC' column heading is shown in either case.

Thanks for chiming in.

Hey Andrew,

Thank you for bringing this to our attention. We are going to look deeper at this "peculiarity" you referenced.