Excel data import Truncate Data length to 255

Hi, I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to  255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue. Please suggest me the way out.   

INSERT into tmp_Test SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Book1.xlsx', [Sheet1$])

July 29th, 2015 2:13am

When you use OLEDB providers then the datatype is determined automatically by the provider based on the first 8 rows. If you have lengthy cells in the first 8 rows then data type will be set as text and otherwise it will be memo type which can hold 255 characters. To overcome this issue Either change the registry setting as mentioned in below KB article: http://support.microsoft.com/kb/281517 or use Microsoft.Jet.OLEDB provider to read the data.

Also http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/40f62ac7-7a14-44a7-9e38-115fde4f0e66

 
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 3:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics