Knowledgebase
Home » Item #994
Data tool error - import generates 'truncation' error even though field is big enough for the data
Question
I am trying to import data from an Excel spreadsheet using the data tool. But I keep getting a truncation error on one field (e.g. P_Desc1). I have checked, and this is definitely big enough to contain the data. What is going on? How do I fix it?
Answer
The problem is related to the Excel OLE DB drivers, which is the software that allows the data tool to connect and pull data from Excel spreadsheets.
Unlike database fields, columns in an Excel spreadsheet do not have sizes specified. So the Excel drivers determine what field types and sizes to use based on the first few lines on the spreadsheet. In most cases, this allows the correct choice of field sizes. But if none of your first few records have a product description of more than 255 chars, then Excel will use that as a field size. The error occurs when a line in the spreadsheet further down then comes in with a value with more than 255 characters - it is impossible to add this data and an error is thrown.
The simplest solution is to manipulate your data slightly so that the first record has a long product description (or any other field that is causing problems in the same way). This should allow the Excel drivers to select the correct field type, and hence successfully import all subsequent records regardless of the amount of data in each field.