• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Cell Text Length|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Cell Text Length|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCell Text Length
sp_PrintTopic sp_TopicIcon
Cell Text Length
Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
1
June 15, 2021 - 11:44 pm
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
June 16, 2021 - 6:54 am
sp_Permalink sp_Print

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

Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
3
June 16, 2021 - 10:01 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
June 18, 2021 - 2:22 am
sp_Permalink sp_Print

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

Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
5
June 18, 2021 - 9:56 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
June 19, 2021 - 1:50 am
sp_Permalink sp_Print

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. Laugh

Br,
Anders

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
June 20, 2021 - 6:46 am
sp_Permalink sp_Print

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

Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
8
June 28, 2021 - 10:55 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
June 29, 2021 - 12:20 am
sp_Permalink sp_Print sp_EditHistory

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?

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 612
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
10
June 29, 2021 - 12:35 am
sp_Permalink sp_Print

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.

Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
11
June 29, 2021 - 9:40 pm
sp_Permalink sp_Print

@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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
June 30, 2021 - 2:09 am
sp_Permalink sp_Print sp_EditHistory

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
John Langham-Service
Member
Members
Level 0
Forum Posts: 51
Member Since:
July 17, 2018
sp_UserOfflineSmall Offline
13
July 5, 2021 - 9:33 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ben Hughes, Alison West, Ramon Lagos, Shawn Barwick, Chris Pinto
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.