Text was truncated or one or more characters had no match in the target code page When importing from Excel file

Question

I have an excel file with four text columns: one of them is called ShortDescription which has the longest value. I created a table in SQL Server 2008 database, with four columns and the ShortDescription column type is set to NvarChar(Max).

but when using the SSIS import and export dialog, I keep getting the mentioned error in the title, even when I set the OnTruncation option to Ignore.

I tried to clear the column data, and it succeeded (so I made sure that the problem is in the ShortDescription column). I tried to copy the whole data to another excel work book, and still no luck.

any ideas ???

Answer 1

I assume you're trying to import this using an Excel Source in the SSIS dialog?

If so, the problem is probably that SSIS samples some number of rows at the beginning of your spreadsheet when it creates the Excel source. If on the [ShortDescription] column it doesn't notice anything too large, it will default to a 255 character text column.

So to import data from a column that contains rows with large amounts of data without truncation, there are two options:

1. You must make sure that the [ShortDescription] column in at least one of the sampled rows contains a value longer than 255 characters. One way of doing this is using the REPT() function, e.g. =REPT('z', 4000), which will create a string of 4000 of the letter 'z'.
2. You must increase the number of rows sampled by the Jet Excel driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel (of if your system is x64 then under the HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Jet\4.0\Engines\Excel) registry key.

You can see more information at these two links:

• http://waxtadpole.wordpress.com/2008/04/28/hello-world/
• http://technet.microsoft.com/en-us/library/ms141683.aspx

To further explain, SSIS creates 3 objects behind the scenes of the wizard, an Excel data source object, a SQL table destination object, and a data flow operator between them. The Excel source object defines the source data and exists independent of the other two objects. So when it's created, the sampling I described is done and the source column size is set. So by the time the data flow operator executes and tries to pull the data from excel to put in your table, it's already looking at a data source that's been limited to 255 characters.

Answer 2

I had this issue when importing from a flat, delimited file into SQL Server. The solution was to update the 'OutputColumnWidth' value for the offending column (from the error message). On the 'Choose a Data Source' form in the import wizard, my source was the flat file. On the leftmost pane, choose 'Advanced'. You can then set the properties of individual columns. In my case, the 'OutputColumnWidth' for most of my columns was defaulted to '50'. I simply updated it to a larger value that would not truncate the value from the flat file.

Answer 3

A simple way to get it to work is edit the file you want to import and create a new row in the first spot. That way it will always be sampled. Then for any columns that may have >255 characters, just add 255 characters to the cell and it will work. After you import, just delete out the junk row you added.

Answer 4

I got this error when I was trying to import a large file that had some chinese characters in it, and also some invalid (large) strings.

The text file was saved in UTF8 format.

My settings:

On the General Option (didn't change anything):

- Locale: English (United States) 
- Unicode: Unchecked
- Code Page: 65001 (UTF-8)

There is an Advanced Option on the left

- DataType (for column): Unicode String [DT_WSTR] (changed)
- OutputColumnWidth: 4000 (that's the maximum) (changed)

On the Review Data Type Mapping

- On Error: Ignore
- On Truncation: Ignore

My target column had width = 50.

I got no errors with these settings.

Answer 5

There is an alternative location of the registry component that needs to be changed to resolve this problem.

If you cannot find it at

Start–>RUN–>RegEdit–>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

then look in

Start–>RUN–>RegEdit–>HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel

Answer 6

For me this link helped me : https://support.microsoft.com/en-us/kb/189897

1. Copy the row which has cell value > 255 characters to the beginning of the excel, make that row the first row in the excel
2. change the registry value from the above link.