32767 Characters in one cell error message

[NabilEnn]

Hello everyone does anyone know the fix to the 32767 characters in one cell error you get when trying to export out to excel? extracting data from pdf or docx, using tika, cell splitter column filter row filter etc.

[takbb]

Hi @NabilEnn , thanks for opening this as a new question, which should mean it gets a wider audience.

You will be getting that message when exporting to Excel because 32767 is the maximum cell size that Excel can handle.

So the only option I see is not to attempt to write more than 32767 characters to a cell, but how you prevent that will depend on what you want to happen with such data.
eg

• you could simply truncate the data using String Manipulation’s substr() function
• you might decide you wish to split the data across multiple rows
• you might decide you wish to split the data across multiple columns
• you may have some other thoughts on reducing such cells, such as removing specific phrases.

I wouldn’t necessarily recommend splitting into additional columns if it will likely generate an unknown number of additional columns as this make the resultant spreadsheet more difficult to manage in any automated way in future.

Do you know if it is only one offending column causing the problem, or multiple columns?

How do you propose to work with spreadsheets containing such large amounts of text? - I’d imagine that 32k of text in a cell in Excel is unviewable and unprintable.

[NabilEnn]

It is only one column, could you by chance give me an example to remove any type or random words from a column or using string manip. could you create me a regex doing so and my column would just be “Content1” if i wanted to remove the characters “83239rej” for example. Thanks for all your help @takbb

[takbb]

Hi @NabilEnn , if you are simply wanting to remove all occurrences of the string “83239rej” from column “Content1”, you can just use the replace() command in String Manipulation (no regex required)

replace($Content1$,"83239rej","")

or you could use the String Replacer node:

But if you have a whole set of specific words that you are looking to remove, there are probably better ways of approaching this, such as utilising “String Replacer (Dictionary)”

I’m slightly confused by the question though in the context of this thread… does it mean that your string will then fit within 32767 characters?