Excel 255 characters in length Import SQL SERVER2005 method

Excel 255 characters in length Import SQL Server2005 method
[Excel Source [1]] Error: Error in output column ' Description 3 ' (546) on output ' Excel source Output ' (9). The column state returned is: "The text is truncated, or one or more characters do not have a match in the destination code page." ”。

The column is up to 308 characters in Excel, but the external columns and output columns in the Excel source can only be set to nvarchar (255), and if the set length exceeds 255, a warning appears, the source column properties do not match, and execution does not pass. The use of the ntext type has also been attempted and does not match.

I used ado.net to pour it into the database tutorial, but found that all over 255 were truncated.

Reason Analysis:

ADO and SSIS Excel sources are problematic in determining the length of a play column.

Solution One:

Convert Excel to CSV format, and then import the database using a flat file source.

Two points to note:

1, the output column length needs to be manually modified.

2, pay attention to different languages caused by Unicode character conversion problem, if necessary, first switch the system language, and then convert.

Solution Two:

In the registry

Jet engine.
Hkey_local_machinesoftwaremicrosoftjet4.0enginesexcel

If yes: Ace engine.
Hkey_local_machinesoftwaremicrosoftoffice12.0access Connectivity Engineenginesexcel

The value of the TypeGuessRows item below is set to 0

In the Windows Server 2003 system registry, the jet item is located under Hkey_local_machinesoftwaremicrosoftwow64node

The reason is that the driver by default according to the first 8 rows of columns to determine the length of the column, modified to 0 after the need to check the entire column of data to determine the length of the column, more than 255 characters Fummer think ntext format, less than 255 words Fuzeme think nvarchar (255).

Add a data stream. In the data stream. Add an OLE DB source.

Creates a new OLE DB source for Excel. I won't say it here.

You only need to set provider to Microsoft ACE OLE DB. Then select the Excel file.

Then set the extended Properties property to Excel 12.0;hdr=yes

Solution Three:

Use a function to cut a column longer than 255 characters to more than 255 columns, and merge after importing the database.

Note the optional paste feature of Excel.

Available functions: Left, mid

Pass my test, pass!

Downloading registry files

Select len (varchar (1000), Division of Staff), * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:book1.xls ') ; user id=admin;password=;extended Properties=excel 5.0 ') ... [Sheet1$]order by Len (Staff Division) Desc--select * FROM table1
Select Len (Division of Staff), * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ', ' Excel 8.0;hdr=yes;imex=1;database=c:book1.xls ', ' Select the division of labor, * from [sheet1$] ") Order by Len (Staff Division) Descselect * FROM OPENROWSET (' microsoft.ace.oledb.12.0 ', ' Excel 12.0;HDR =yes;imex=1;database=c:book1.xls ', [sheet1$]) Order by Len (Staff Division) DESC
Select Len (Division of Staff), * from OpenDataSource (' microsoft.ace.oledb.12.0 ', ' Data source=c:book1.xls;extended properties= ') Excel 12.0;hdr=yes;imex=0 "') ... [Sheet1$]order by Len (Staff Division) DESC