ACL Error: Cannot Export to Excel

Next time you get the cannot perform export to Excel error in ACL, try one of the 3 solutions described below.  The full text of the error is:

 Cannot perform the export.
You can export fields with maximum of 254 characters to Excel.

What Caused the Error

The error means that the table you tried to export to Excel has at least one field that contains more than 254 characters. Usually, this occurs with computed fields (for some background information on computed fields, see  what is a computed field and how to create one). But a non-computed field can also generate this error.

How to Find the Field Causing the Error

To determine which field(s) caused the error, open the table you tried to export, and select Edit, Table Layout (see below). Then look for a field that has a number larger than 254 in the Length field. Assume you have a table called D67 with a computed field called c_Insured Name that has a length of 663, and you’ll see the following: Usually, this condition occurs when the field gets padded with blanks, but sometimes this condition occurs when you import a file with a free-form text field that has a long description. Since you might have more than 1 field causing the error, make sure you check the field length of all fields.

I’ve also seen this error occur when you create a computed field that has many conditions, even when the actual data displayed in the field is well below 254 characters. It seems like ACL counts every character in each of the conditions and includes the count in the field length. For this problem, only Solution #3 works. This is rare, but I’ve encountered it more than once.

[If you don’t know what conditional computed fields (CFFs) are, don’t worry; this most likely is NOT the cause of the error. For more info on CFFs, see this post:  How to add a Conditional Computed Field.]

Solution #1

To fix this problem, you could trim the length of the field by creating a computed field. In the example below, I called that field c_Insured_Name2, and use an expression that trims the information. You can try either of the expressions below, depending on your situation:

ALLTRIM(c_Insured_Name)     If your data has spaces or blanks padded at the beginning or end of the field (or both), this expression will preserve all your data, but remove all those spaces at both ends of your data.
SUBSTR(c_Insured_Name, 1, 254)     If you just want to save the first 254 characters in the field, this expression will preserve all data, starting with the 1st character and ending with character 254 (all characters after 254 are ignored). All characters 1-254 are preserved, regardless of whether they are letters, numbers, special characters, or spaces. You can end at any character you desire; to end with the 100th character, change 254 to 100.

The problem with using the SUBSTR expression shown above is that if the field contains 663 characters of REAL data that you need (not just blanks), you would lose all characters after the 254th character.

That’s when I turn to Solution #2.

Solution #2

A better solution is to export the table to a delimited text file. Open the table, and select Data, Export to Other Application. This time, under Export As, instead of choosing Excel, choose Delimited as shown below. You can change the Column Separator and Text Qualifier if you like, but usually the defaults shown below work fine (however, if your data contains commas, do not choose a comma as the delimiter).

After exporting the delimited file, you can then, if you desire, import that file into Excel (version 2007 or later). I’m guessing that the 254 character export error is an ACL-Excel interaction problem, not an Excel problem, simply because this Microsoft link says the Total number of characters that a cell can contain is 32,767 characters. That means a lot more than 254 characters fit into an Excel cell; you just can’t do it directly from ACL. At least not yet.

However, there’s a Catch!

There’s one minor catch.* Since you are exporting the data to a text file, you lose all formatting. So when you import the file back into Excel, make sure you check and fix the format of all dates, number fields, etc., to ensure they are appropriate before you give the file to someone else or do further analysis on the file.

* For my international readers, a catch is a drawback or difficulty that is not readily evident.

Solution #3

Solution #3 can be applied at 2 different levels, the table level or the field level.

Regardless of the level, this solution applies only when 1) the error is caused by a conditional computed field, and 2) the original field value of each record was replaced with another value based on the conditional computed field (in other words, the original value in each record, which was 254+ characters, was replaced by one of the conditions with a value that was less than 254 characters; I’ll explain this in more detail in my upcoming CFF post; again, most likely this is NOT what caused your error).

At the table level, just extract the table in question into another table, which replaces the computed field causing the error with physical values. In other words, the field is technically no longer computed; if you look at the field properties, you won’t see an expression any more.

At the field level, just create a new computed field called c_Insured_Name2, and use the original field name as the expression. In other words, I create a new computed field using the data in the original field that caused the error (see example below). That resets the field length to the length of the actual data. Here’s a screenshot of what how to do it:

If this doesn’t make sense, just try it. It might make more sense when you see what it does with your own data.

Related posts:

ACL tip: What is a Computed Field?

ACL: How to Add a Computed Field

ACL: How to Add Computed Fields via Script

Teach Yourself ACL

All my posts about ACL

P.S. from Skyyler: Yes, I do write about topics other than ACL, but not very often.

Advertisement