Remove From My Forums

Microsoft oledb data acces truncates the data length to 255 characters

Question
0

Sign in to vote

User383937017 posted

Hi all

When i use microsoft oledb driver for excel import, it truncates the data to 255 characters.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;MAXSCANROWS=0;IMEX=1'";

Please provide some solutions

Tuesday, June 11, 2013 5:30 AM

Answers

0

Sign in to vote
User-183374066 posted

1. You need to do some registry settings .. See following discussion

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

2. The other thing can be first row in the column UserEntity in the Excel file is null. In this case, SQL Server Integration Services(SSIS) will consider the column as DT-WSTR. To work around the issue, we can sort the UserEntiry column in the Excel as Z to A. After that, the column will be considered as DT-NTEXT in the SSIS. see following

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e16cc2ed-42b0-446c-a16f-b2b732eaf7be

Same solution by Asheej

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.

http://www.dotnetspider.com/forum/320302-Oledb-get-truncate-excel-data-after-256-character-while-importing-data-into-DataTable.aspx
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, June 11, 2013 6:00 AM