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 ???

Subset

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.

Nero

that is amazingly right !!! but why is that happening if the destination column is set to accept maximum length of data?? so what the length of source column has to do with it??? – Subset

"Note For 64-bit systems, the corresponding key is as follows: HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel " - link But apparently the value can only be up to 16? Doesn't seem to be much of an improvement - but I haven't tested it. – Datcha

For me, sorting the rows with longest text at the top worked. @NourSabouny, I think he's saying that the data flow operator in the middle is erroring, even if the destination column is set to nvarchar(max). – Nemato

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.

Martineau

Alternatively, rather than guessing a large enough limit to a DT_STR, you can choose the DT_NTEXT SSIS type, which is the equivalent of the MSSQL nvarchar(max) or the obsolete ntext types. – Remembrancer

A combination of updating the OutputColumnWidth and using DataType DT_WSTR worked for me. In SQL Server 2014, DT_WSTR can have an OutputColumnWidth up to 4,000 Unicode characters wide. This resulted in something similar to 40-Love's answer below. – Gantlet

Can you change all of these columns at once? I have a high amount of columns and I was wondering if this was possible. – Fulmis

This was my problem. +1 – Itch

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.

Potsherd

This was the shortest path to success for me. – Caddoan

Hmmm, not sure how this is different from the solution. This solution seems more appropriate for stackexchange. ~(: – Swadeshi

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.

Peloquin

Thank you for posting this. I was receiving the same error message during my imports, caused by the issue mentioned above: attempting to import data having foreign characters into fields with data types that did not accept foreign characters. My fix, short-term, was to remove the foreign characters from the data I was trying to import. – Retsina

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

Sloop

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.

Distributary

Answer 7

Try this - Go to Data Flow Task > Right click on Excel Data Source > Click on Show Advance Editor > Select Input and Output Properties > Expand Excel Source Output > Expand External Columns and Output Columns and check the erroneous columns and click on those column headers and update Data Type accordingly (Mostly that should be Unicode text stream [DT_NTEXT], otherwise change to that and give it a try). Hope this help.

Limber