Microsoft Access – Fix File import Truncation error

If you attempt to import a table to Access from a file and get the “ImportErrors” table/report created (see image below), you know your table did not import correctly.  

Let me briefly tell you why I got the error:
I tried to import a .dat file and convert it to an Access table. While importing, Access noticed that it was unable to import some of the fields due to an error, and notified us which fields we need to fix in an “ImportErrors” table/report (as shown below). Open the newly created table to see what the errors are with the import:

 


Import error table

As you can see this table has 3 columns:

Error: The error that File Import encountered
Field: The Field Name (column name) of where the error is
Row: The row number associated with where the error occurred.

So, as you can see in the first row of the table (above) we have a “Field Truncation” error for “Field Name” ( or column name) “CC” and “Row” for the row number. This gives us all the information we need to go examine the contents of that row, if we need to confirm. I will just trust that it is right because I trust Access that much at least.

Truncation

Truncation happens in an import when the variable type is not big enough to hold the data it is being assigned. For people that have programming experience, this should make sense. Different variable types can encompass different data types and lengths (ie string, integer). In our case data type for “CC” is “Text” Variable (or data type). The “Text” data type can only hold up to a maximum of 255 characters. For those of you who don’t understand, it’s like basically trying to put an big object A in a small container B -> it wont fit. What Access does in this scenario is fill the “Text” data type for field “CC” with as much data as it can hold and discards the rest, hence the term “truncation.” In other words it puts as much of object A into Container B as it can hold and discard the rest.

Data Type Description
Text Can hold a maximum of 255 characters (numbers and text)
Memo Can hold up to 65,536 characters (numbers and text)

I am not going to get into data types in this article and will just skip to letting you know how to fix it. So in our case, field “CC” data type of “Text” must be changed to data type “Memo” before we finish the import. Some of you may be thinking: why not just use “Memo” for everything to resolve the truncation error. While this is possible, when working with large sets of data, you want to use the Text variable over the Memo variable wherever possible.

New versions of Microsoft Access have a “Short Text” data type as well as “Text”.

SO, when in the File -> Import screen:
 


Import Text Wizard

Click the “Advanced” button on the bottom left. Now you should be presented with the following screen:


Import Specification

Now, we need to find the “Field Name” of “CC” and change the “Data Type” to “Memo”. Go ahead and change all other fields data type as necessary according to the “import error” report. When you import the file this time, you should not get the truncation error.

Leave a Reply

Your email address will not be published. Required fields are marked *