The text was truncated or one or more characters did not match on the target code page. When importing from an 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 a 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 continue to get the specified error in the header, 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 copying all the data into another Excel workbook, but still no luck.

any ideas

Answer 1

I assume that you are trying to import this using an Excel source in the SSIS dialog box?

If so, the problem probably is that SSIS displays a number of rows at the beginning of your spreadsheet when creating an Excel source. If in the [ShortDescription] column he does not notice anything too big, by default a column with text of 255 characters will be marked.

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

• You must ensure that the [ShortDescription] column in at least one of the sample rows contains a value that exceeds 255 characters. One way to do this is to use the REPT () function, for example. = REPT ('z', 4000), which will create a string of 4000 letters "z".
• You must increase the number of rows selected by the Jet Excel driver to include such a row. You can increase the number of rows selected by increasing the TypeGuessRows value in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel (if your system is x64, then under HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Jet\4.0\Engines\Excel ) registry key.

You can see additional information on these two links:

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

For further explanation, SSIS creates 3 objects behind the scenes of the wizard, an Excel data source object, an SQL table destination object, and a data flow statement between them. The source Excel object defines the source data and exists independently of two other objects. Therefore, when it was created, the sampling described by us is executed and the size of the source column is set. Thus, by the time the data flow statement is executing and trying to pull data from excel for placement in your table, it is already looking at the data source, which is limited to 255 characters.

Answer 2

I had this problem when importing from a delimited flat file in SQL Server. The solution was to update the "OutputColumnWidth" value for the offending column (from the error message). In the Select Data Source form in the Import Wizard, my source was a flat file. In the left pane, select Advanced. Then you can set the properties of individual columns. In my case, "OutputColumnWidth" for most of my columns was set to "50" by default. I just updated it to a larger value that would not truncate the value from the flat file.

Answer 3

An easy way to make it work is to edit the file you want to import and create a new line in the first place. Thus, he will always be selected. Then for any columns that can have> 255 characters, just add 255 labels to the cell and it will work. After importing, simply delete the extra line you added.

Answer 4

I got this error when I tried to import a large file with several Chinese characters in it, as well as some invalid (large) lines.

The text file was saved in UTF8 format.

My settings:

In the general version (nothing has changed):

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

On the left side is an advanced option

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

Regarding the display of view data types

 - On Error: Ignore - On Truncation: Ignore 

My target column had a width = 50.

I have no errors with these settings.

Answer 5

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

If you can not find it on

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

then take a look at

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

• Copy a line with a cell value> 255 characters to the beginning of excel, make this line the first line in excel
• change the registry value from the link above.