excel导入SQL时提示"数据对于所指定的缓冲区来讲太大"

时间:2022-09-09 11:00:20
excel导入SQL时提示"数据对于所指定的缓冲区来讲太大"

以下内容转自[CSDN]

最近处理excel数据导入到Sql Server中,失败,报错如下 :

作为源列3(“产品说明”)的数据对于所指定的缓冲区来讲太大 。
我的excel文件中有一列叫“产品说明”,就是一些文字。Sql server处理导excel数据流程是这样的,它会先创建目标表,再把excel数据写入缓冲区,然后再把缓冲区数据写入目标表。问题就在创建目标表和 写入缓冲区,sql server会根据现在的excel中的数据格式来写入缓冲区和创建目标表。比如excel中有一列为编号,里面是数字,sqlserver就会在目标表 里建一列叫“编号”,为float类型.

而对于我的excel文件中的"产品说明"这一列,是文字,那sql server如何建立目标列或缓冲区呢?它怎么知道该把目标建立成varchar还是text呢?经过测试,发现sql server是这样做的,他会取excel文件中的前8行(2的三次方),然后算这8行里“产品说明”的最大长度,如果这8行里最长的 > 255,则目标列和缓冲区就会建立成ntext类型,否则就建立成nvarchar类型。这样,问题就来了!假设我的excel文件前8行“产品说明”中 都 < 255,则目标和缓冲区就被建立为varchar(255)类型了(这里说明一下,你可以修改目标表的字段类型,但缓冲区长度或类型是无法修改的)。而如 果我的第10行里“产品说明”的长度超过了255,则sql server往缓冲区里写的时候,自然就发生了如上的错误:数据对于指定的缓冲区来讲太大。

这样,我们导数据当然就失败了!这个也许不能算bug,因为只是实现得不好(取某列前8行的最大长度并不能真正代表所有行,我想 microsoft的工程师为了简单或者提高性能才这样做的);也许算是bug,因为不知情的用户导数据失败了。而且不知道为何而失败的!
公理自在人心,让大家或那些工程师们去评判吧!

解决办法 :

    修改注册表: HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel 下双击右边的"TypeGuessRows"选项,将"数值数据"改成0。 修改之后关机重新启动。

原因是:将 excel表的数据导入数据库的时候,Jet引擎根据"TypeGuessRows"选项的值所代表的行数判断内容的数据类型,默认是根据前8行的内容判 断数据类型,修改成0后,它会对每行的内容进行判断,不过这样做会影响性能。

其他方法:

    1 把比较具有代表性的行移到Excel的第一或二行。

    2 对于特殊列,该列含有:(True, False, 以及其它Text),把True和False改为其他值(OLDTrue, OLDFalse),导入之后再改回原值(True,False)。

    3 先导入到Access中再导入到SQL Server。