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

cexcel

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

Best Answer

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.

Related Topic