DataPoint users report that sometimes information of an Excel file is not showing at full length or that text data is truncated or cut off. When we have a closer look at this problem, we always see that the first 255 characters of the Excel cell are read into the presentation. All characters after this position, are truncated or lost.

So in Excel, our datasheet might look like this:

You will see that the text of cell E10 is long, exceptionally long compared to other cells of column E. When you set up a link in PowerPoint to this Excel file, and use this information in real-time on your slide, then this data will certainly be cut off. Only the first 255 characters will be visible.

Why?

Because the Excel driver used here, is always trying to guess and evaluate the content of an Excel file, before the actual import. When an Excel spreadsheet is imported, it is converted from the spreadsheet format to a database format, with similar rows and columns as in the Excel. To analyze the cells, the Excel software driver looks at the first 8 rows. Based on that information, it tries to figure out if a cell is always holding a number, or a date, or a text string. Numbers and dates are no problem. But typical for databases, you have 2 types of text fields.

• A short text field, limited to 255 characters maximum, to enter e.g. the address of a customer
• An unlimited text field to store e.g. medical information of a patient with long medical reports

And here it goes wrong. The first 8 rows of the raw Excel data, holds short texts only. All texts are smaller than 255 characters in length. So the Excel driver decides to use a short text field for the column E in the dataset. After that conclusion, all further rows of the Excel file are read and placed in the short text column, and longer text is truncated because of this wrong conclusion.

The fix!

You can influence the number of rows that Excel is scanning to determine the type of data. By default, this number is 8. You can higher this number (up to 16) or set it to 0 for a full scan. Microsoft warns that putting this value to 0, could result in performance issues. But we have have never seen big performance issues with this change, so go ahead, but keep in this mind. To change the number of rows to scan, use the registry editor.

• On the Start menu, click Run.
• Type in regedit and click OK.
• In the registry editor that opens after that, expand the nodes of the registry and navigate to a few possible locations. The locations vary on the operating system, 32 or 64 bits, Microsoft Office version installed etc. Possible locations are:

• In that folder, look for the property name TypeGuessRows at the right and select it.
• Right click the property and choose Modify… from the popup menu.
• Set the Value data to 0 and hit OK.
• Exit the registry editor now.

After this change, restart PowerPoint and try to read the information again. DataPoint will now scan all the values of your Excel file and choose the correct type of data, the longer text field in this matter. All Excel texts, short and long, will be visible correctly in PowerPoint as of now.

This tweak for Microsoft Excel works perfectly for DataPoint as well as for other products using the Excel link driver.