Log In
Register
Home
July 17, 2018
Offline
Hi,
I am still importing data from files in a folder one of the columns is a text field [Issue Notes] in the excel files it could be anything from blank to a full report so far the longest has 589 chars the majority are between 1-300.
My issue is that power query truncates that field to 255 chars on import, I cant see any reason why this should happen.
regards
John
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Looked around on this topic and found an explanation on this site:
This behavior is determined by the the predictive nature of the Excel driver/provider. Since it doesn't know what the data types are it has to make a guess based upon the data in the first several rows. If the contents of a field exceeds 255 characters, and it's in the first several rows, then the data type will be Memo, otherwise it will probably be Text (which will result in the truncation).
It also seems you can change the TypeGuessRows value to zero in the registry to force Excel to read the first 16 384 rows before determing the data type.
Br,
Anders
July 17, 2018
Offline
Thanks Anders for now I can't change the registry due to corporate policies.
I tried adding a row to the sample file that Power Query uses with 1000 chars to force it to be seen as a memo field but even the Transform sample file has that line truncated to 255 chars
thanks John
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Started to check my files where I know I have long text where many are 400+ characters and have luckily found no errors of this kind, in fact, I can't replicate the error you are describing. Also checking in more detail what data types are there in PQ and for text there is only one, Text. So don't know what the Memo data type is in this regard, perhaps a data type from Power Query's early days.
Also checked the documentation of the data types and noticed at the end, where they show the data type conversion matrix that there are some circumstances where the data type can be changed but it will truncate the source data. However, changing to text is not one of these circumstances.
So, for me this is luckily not an issue, but I do find it strange that it is an issue for you. Would love to get hold of any of your files having this problem to check it out.
Br,
Anders
July 17, 2018
Offline
I have cleaned the files as much as possible but there are some extra queries I couldnt delete.
daily file is the data file I added the bits in yellow to try and trick it into thinking its a memo field
regards
John
VIP
Trusted Members
December 7, 2016
Offline
Hello,
When importing the daily file to a new file it works just fine, I get over 1,000 characters without any issue. The same when I create several copies of your daily file and then add files from folder in a new file, no issues.
I do see you have the text truncated in your .xlsm file, but here I am wondering if this error can be because of a file corruption of some kind. Haven't put to much investigation in that file. But there is a weekend coming. 
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Just dropping by to write that I can not find any clue in your .xlsm file to why the description text gets truncated. I can not replicate the issue when importing your .xls file to a new separate query.
Br,
Anders
July 17, 2018
Offline
Update
I still can't get it to work unless the first row has more than 255 chars in every file I was hoping that it would work with the sample files alone, but unfortunately not.
the 20210112-daily.xls file I included, as part of my testing had a first row cell with 1100+ chars so that one was always picked up the other 200+ files in the folder it skipped.
So at the moment I either have to live with truncated data or go through each file and add 1000 chars to that first cell.
Unless anyone else has an idea
regards
John
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
the 20210112-daily.xls file I included, as part of my testing had a first row cell with 1100+ chars so that one was always picked up the other 200+ files in the folder it skipped.
The file indicated does not have the first data row with more than 1100 chars.
Cell AE16 has 1187 chars, and that's the last data row in that file, why are you talking about first row?
The file loads completely, data is not truncated, so we cannot replicate what you are saying. Can you provide a sample file that gets truncated?
Trusted Members
Moderators
November 1, 2018
Online
What version of Excel / Power Query is it that you are using?
The Memo type only applies to the Jet Database structure (Access) as far as I am aware.
July 17, 2018
Offline
@Catalin
I just downloaded it from here to check and the file attached above 20210112-Daily.xls has the same data in AE1 and AE16 there is a LEN formula in AG1 and AG16 I added to show the number of chars at 1187
To duplicate what I see delete the data in cell AE1 highlighted in Yellow I added that to try the Memo field trick but it only works if in every file (its not in the original downloads AE1 is empty)
I am using 365 Apps for enterprise
Microsoft® Excel® for Microsoft 365 MSO (16.0.14026.20294) 64-bit
regards
John
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Ok, without that AE1 text the text is trimmed to 255.
A good way to fix this is to write a simple macro that converts .xls to .xlsx file type, .xls generates more weird issues, the one you've hit just adds to the list of .xls issues.
Those files look like exports from another system. If so, the IT guys should really come closer to 2021 and save as .xlsx, they just have to use a newer library to export, the one they are using is just too old...
Once the file is saved as .xlsx, full text is displayed, you no longer need to add AE1 text in all files.
Answers Post
July 17, 2018
Offline
Thanks Catalin,
I can't get my customers IT team to change for now so I do as you suggested wrote a macro to update all to xlsx and going forward will open and save as rather than just save the email attchment.
John
1 Guest(s)
