使用OleDb写入Excel异常,"字段太小而不能接受所要添加的数据的数量" "the field is too small to accept the amount of data "

解决方法:

excel是根据第一行数据来判断数据类型的,所以你需要在第一行相应字段添加足够长的字符串(随便写长一点就行,导入后可以删除的嘛).

这个问题是Access数据库字段默认长度是250,而你添加的数据大于250个字符,但是你是通过Access驱动引擎写入到Excel的,所以你也无法改变数据类型.

在网上找问题,很多人都说改变Access字段数据类型,问题是我根本就没有在Access上操作,而是通过Access驱动引擎写入到Excel里面.所以解决思路应该放在Excel上. 

这样就搞定了.

还有一个异常:标准表达式中数据类型不匹配。

同理:原因就是第一行记录为空或者为数字,所以要改为字符串.将第一行记录添加数据,

这是我在google找到的.

=============================

参考:https://www.inaplex.com/forum/yaf_postst4_Excel-ODBC-error----The-field-is-too-small.aspx

While importing from an Excel spread sheet you see the following error message:

• "[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data".

This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet.

Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….

1. A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let's say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let's say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
   
2. A column in a spreadsheet has numeric strings for the first 8 records, For example "123", "456" etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example "Hello World". The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing "hello world" is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.

In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.