Skip to main content

Bulk Task Smasher: What does the warning about the Excel character limit mean?

Published Date

Question

I am trying to download a file from Bulk Task Smasher, and the app is telling me there is some problem with Excel and a character limit. What does this mean?

 

Answer

Background

Bulk Task Smasher can generate two kinds of files that incorporate data from your Uberflip account: Populated Templates and Exports. The generated files are Microsoft Excel spreadsheets in the .XLSX format, and data from your account is inserted into individual cells within the spreadsheets.

However, Excel has a limit on the number of characters that a single cell can contain: 32,767 characters. If you attempt to open a spreadsheet file in Excel where any single cell has more than 32,767 characters, Excel will indicate that it found a problem, and will offer to "recover" the file:

image.png

If you click Yes, Excel will cut off the content of any cells with more than 32,767 characters to fit within the character limit. For example, if the cell originally contained 70,000 characters, Excel would cut this down to just the first 32,767 characters, and discard the rest.

The problem

In some cases, Bulk Task Smasher can generate files where individual cells exceed Excel's character limit (for example, in cells containing the body text of content Items). Opening these files in Excel and then using the "recover" option would cut down the contents of cells that exceed the character limit, but when this happens Excel does not indicate which cells were cut down.

This creates a risk of data loss if you attempt to use a file affected by this issue as a job template in Bulk Task Smasher: if you are unaware of which cells were cut down and did not remove their contents from the file, Bulk Task Smasher would interpret the shortened cell contents as a change, and would update your Uberflip account accordingly. In practical terms, this means Bulk Task Smasher would shorten content Item body text or other data in your Uberflip account to match what was specified in the affected cells in the job template.

The solution

To help mitigate this risk, Bulk Task Smasher checks all of the files it generates for cells that exceed Excel's character limit and automatically removes the contents of any over-limit cells it finds before making the file available for download.

Whenever this happens, Bulk Task Smasher will notify you with a prompt before the file is downloaded to your computer:

Bulk_Task_Smasher_-_Uberflip.png

Removing over-limit cell contents ensures you can safely open and edit the file in Excel without the risk of unintentionally making unwanted changes to your Uberflip account due to truncated cell contents. While working with the file in Excel, all affected cells will be blank, and will be highlighted with a comment flag:

image.png

Alternative options for advanced users

The single-cell character limit of 32,767 characters only applies to Microsoft Excel: other spreadsheet programs have their own (often higher) limits  — or even no limit at all. For example, the single-cell character limit in Google Sheets is 50,000 characters, in OpenOffice Calc it is 65,535, and both LibreOffice Calc and Apple Numbers do not place any limit on the number of characters in a cell.

If you want to use a spreadsheet program other than Excel to work with the files generated by Bulk Task Smasher, you can choose to download the files with the contents of over-limit cells left intact (not removed).

To do this, click on Advanced options in the character limit warning, then check the box next to I understand the risks, download this file with the over-limit cells included. You will then be given the option of downloading a Non Excel Compatible version of the file:

image.png

Please note that using this option is at your own risk, and you should be aware of any cell character limits in the spreadsheet program you intend to use.