数据插入到Excel中的数据集，但没有问题填充的DataTable时，当255个字符后截断 [英] Data truncating after 255 characters when inserting into dataset from Excel but no issue when populating DataTable

问题描述

我试图插入使用ADO.NET从Excel文件中的数据，以数据集。下面是该过程采用

1. 首先Excel数据使用加载到数据集

CONNSTRING =供应商= Microsoft.ACE.OLEDB.12.0;数据源= mydb.xlsx;扩展属性= \的Excel 12.0; HDR =是; IMEX = 2\;

1. 填充数据集时，它插入只255个字符。 （我找不到在那里截断）。在我们的源代码有用于截断任何代码。但是，当相同的连接用于填充一个DataTable不会发生这样的问题。

请帮我在来到这个问题。

在此先感谢

解决方案

的问题是，ACE驾驶员推断TEXT数据类型的列，当你填充数据集。 TEXT列被限制为255个字符。你需要迫使它使用MEMO数据类型。当您填写的数据库，据推测，该数据库列是支持超过255个字符的类型，和司机拿起这件事。

在除在此线程上讨论的方法问题，则可以迫使它通过插入数据的一个空行，并在该列中的单元中插入256个或更多的字符使用备忘录数据类型。 （该线程涉及Jet，但也应适用于ACE）。

您最好的选择，或许是使用一些其他的库来读取该文件，并填充DataSet无喷气/ ACE。

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

解决方案

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.

这篇关于数据插入到Excel中的数据集，但没有问题填充的DataTable时，当255个字符后截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！