Data truncating after 255 characters when inserting into dataset from Excel but no issue when populating DataTable

Question

I am trying to insert the data from Excel file to dataset using ADO.NET. Below is the procedure adopted

1. First all excel data are loaded into dataset using

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mydb.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

1. when populating dataset it inserts only 255 characters. (I couldn't find where it is truncating). In our source code there is no code for truncating. But when same connection is used to fill a datatable no such problem occurs.

Please help me to over come this problem

Thanks in advance

Answer 1

The problem is that the ACE driver is inferring a TEXT data type for the column when you're populating the data set. Text columns are limited to 255 characters. You need to force it to use the MEMO data type. When you're filling the database, presumably, the database column is of a type that supports more than 255 characters, and the driver picks that up.

In addition to the methods discussed in this thread on the problem, you can force it to use the memo data type by inserting a dummy row of data and inserting 256 or more characters in the cell for that column. (That thread concerns Jet but should also be applicable to ACE.)

Your best alternative, perhaps, is to use some other library to read the file and populate the DataSet without Jet/ACE.

Answer 2

You're a bit vague about the way it gets truncated, but from the below there are a couple of possibles:-

• Worksheet size 65,536 rows by 256 columns
• Column width 255 characters

From here:-

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

Worksheet and workbook specifications

FEATURE MAXIMUM LIMIT
Open workbooks Limited by available memory and system resources
Worksheet size 65,536 rows by 256 columns
Column width 255 characters
Row height 409 points
Page breaks 1000 horizontal and vertical
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 56
Cell styles in a workbook 4,000
Named views in a workbook Limited by available memory
Custom number formats Between 200 and 250, depending on the language version of Excel you have installed.
Names in a workbook Limited by available memory
Windows in a workbook Limited by system resources
Panes in a window 4
Linked sheets Limited by available memory
Scenarios Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 3 in a single sort; unlimited when using sequential sorts
Undo levels 16
Fields in a data form 32
Custom toolbars in a workbook Limited by available memory
Custom toolbar buttons Limited by available memory

Answer 3

This is the cause of the issue - https://support.microsoft.com/en-us/kb/189897

It can be fixed by inserting a dummy row at the top (perhaps a header row - I have not tried this myself) with 255+ characters or more.

This seems to be a promising solution.