6,128
社区成员
发帖
与我相关
我的任务
分享【急】Excel2003/2007导入SQL2005/2008问题
在使用IS把Excel中的数据导入SQL数据库时出现：There was an error with output column “ColumnName” on output "Excel Source Output" . The column status returned was: "Text was truncated or one or more characters had no match in the target code page的错误，数据源中有4万多条数据，出错的相关字段属性为ntext，当导入2万多条数据时提示上述错误，修改注册表相关键值没有效果！求高手指点！谢谢！
...全文
请发表友善的回复…
发表回复
hacici 2012-07-13
- 打赏
- 举报
SQL2008是通过扫描EXCEL的前8行的各个数据的内容来估计该表各个列的数据的长度的，在前8行中“投诉描述”中有内容超过255字符的条目的时候自然会被判定为LongText类型从而成功导入
mutecamel 2009-12-08
- 打赏
- 举报
我刚刚也遇到类似问题，发现原因是这样的：
sql server处理excel文件时，只允许文本字段最大长度为255，如果excel文件中某个单元格字符数超过了255，sql server就会报这样的错。
解决方法：我是新手，比较笨的方法是，excel文件里用left(xx, 255)截取，保存，然后再导入。这样的做法缺点很明显，就是丢掉很多字符，但小弟没有更好的解决办法，如果有更好办法的话，望有牛人告知，必万分感谢！
sql server处理excel文件时，只允许文本字段最大长度为255，如果excel文件中某个单元格字符数超过了255，sql server就会报这样的错。
解决方法：我是新手，比较笨的方法是，excel文件里用left(xx, 255)截取，保存，然后再导入。这样的做法缺点很明显，就是丢掉很多字符，但小弟没有更好的解决办法，如果有更好办法的话，望有牛人告知，必万分感谢！
Johe_08 2008-12-01
- 打赏
- 举报
此贴已经发出5天时间了，还是没有人能解决，下面是执行过程中的错误列表！希望能再有高手帮忙！小弟不才，跪谢了！
Information: 0x4004800C at Data Flow Task, SSIS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "ColumnName" (422) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Excel Source [1]: The "output column "ColumnName" (422)" failed because truncation occurred, and the truncation row disposition on "output column "ColumnName" (422)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [14]: The final commit for the data insertion in "component "OLE DB Destination" (14)" has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [14]: The final commit for the data insertion in "component "OLE DB Destination" (14)" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (14)" wrote 22052 rows.
Information: 0x4004800C at Data Flow Task, SSIS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "ColumnName" (422) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Excel Source [1]: The "output column "ColumnName" (422)" failed because truncation occurred, and the truncation row disposition on "output column "ColumnName" (422)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [14]: The final commit for the data insertion in "component "OLE DB Destination" (14)" has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [14]: The final commit for the data insertion in "component "OLE DB Destination" (14)" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (14)" wrote 22052 rows.
Johe_08 2008-11-27
- 打赏
- 举报
谢谢大家的回复，我查看了一下出错的字段在excel里是nvarchar型，这个字段中的数据可以为空，有的内容多于255；出错应该是因为这个问题！但是为什么分成两张表时可以成功导入？如果是把excel表中的字段属性改为ntext型该如何修改？谢谢！
xiaoliaoyun 2008-11-27
- 打赏
- 举报
我遇到过导入出错的情况,最后发现是EXCEL里面有些空格太长的缘故....
因为数据太多,也不知道那行数据有问题,后来只好手动在EXCEL添加列,例如第一列添加 insert into table values (' ,....
然后copy到查询分析器执行,这样出错可以定位到具体的行
因为数据太多,也不知道那行数据有问题,后来只好手动在EXCEL添加列,例如第一列添加 insert into table values (' ,....
然后copy到查询分析器执行,这样出错可以定位到具体的行
Johe_08 2008-11-27
- 打赏
- 举报
有没有高手帮我解决啊？听说这里高手挺多的，各位大哥大姐帮下忙啊！
qap22 2008-11-26
- 打赏
- 举报
up
Johe_08 2008-11-26
- 打赏
- 举报
应该是缓冲区不够的问题，注册表键值没有修改之前只能导入几千条数据，虽然修改过之后能提高不少！试过分成两个数据源导入，但是每次导入都要分开导太麻烦了！有没有更好的解决方法？
Johe_08 2008-11-26
- 打赏
- 举报
谢谢回复，原来用的是nvarchar只能转换为文本文件分开导入，后来改的ntext可以使用原文件导入，但是当导入两万多条数据时报上述错误！微软官方说修改excel缓冲区的键值，试过了也不行！还有其他方法吗？
等不到来世 2008-11-26
- 打赏
- 举报
ntext改成nvarchar(max)试试
Johe_08 2008-11-26
- 打赏
- 举报
有没有人知道啊？这个问题困扰我好长时间了，微软的官方网站也没有找到类似的解决方案！谢谢高手指点下！
Johe_08 2008-11-26
- 打赏
- 举报
数据库中的字段属性都是按照Excel中的相关字段设置的！
-晴天 2008-11-26
- 打赏
- 举报
检查一下 EXCEL 每一列中各行的数据类型(格式)是否一致.
Johe_08 2008-11-26
- 打赏
- 举报
数据库对应字段也是ntext型
jinjazz 2008-11-26
- 打赏
- 举报
数据库对应的字段长度,不是你excel字段长度
Johe_08 2008-11-26
- 打赏
- 举报
谢谢回复，相关字段可以为空，有数据的可能是几百字的长度！
jinjazz 2008-11-26
- 打赏
- 举报
是不是数据字段长度太小
