excel导入sql server2005的问题

[Excel 源 [1]] 错误: 输出“Excel 源输出”(9) 上的 输出列“Description 3”(546) 出错。返回的列状态是:“文本被截断，或者一个或多个字符在目标代码页中没有匹配项。”。 

该列在excel中最长为308个字符，但是excel源中的外部列和输出列最多只能设为Nvarchar(255),如果设定长度超过255，则出现警告，源列属性不匹配，执行也通不过。使用Ntext类型也尝试过，不匹配。

我使用ado.net倒是把它倒入到数据库了，但是发现所有超过255的都被截断了。

我试过把excel中该列设为文本格式也不行。

这种长字符列应该怎么处理？

48 个解决方案

#1

我现在导EXCEL都是直接在EXCEL中拼出INSERT语句，方便又灵活

#2

引用 1 楼 guguda2008 的回复:

我现在导EXCEL都是直接在EXCEL中拼出INSERT语句，方便又灵活

在EXCEL中拼出INSERT语句？如何做，请指导，谢谢。

用OpenDataSource吗？

#3

没研究过

#4

没研究过

#5

--或者拷貝到文本中bulk insert
bulk insert test
from 'E:\Test.txt'
with(
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'

        )
select * from test

#6

我不喜欢用语句 我一般都习惯用导入导出工具 

#7

最近被客户一些乱七八糟的excel搞的焦头烂额，格式无比混乱，语言也跟我们不同。。

#8

引用 5 楼 wufeng4552 的回复:

SQL code--或者拷貝到文本中bulk insertbulkinsert testfrom'E:\Test.txt'with(
        FIELDTERMINATOR=',',
        ROWTERMINATOR='\n'

        )select*from test

水哥的方法用得比较多 

#9

引用 6 楼 fredrickhu 的回复:

我不喜欢用语句 我一般都习惯用导入导出工具

导入导出向导和SSIS数据流我都尝试了，不行。

#10

感觉bcp 很好使

#11

就是在一列里输入
="SELECT '" & A1 & "'," & B1 & " UNION ALL"
然后一拉，就出来一堆UNION ALL了，COPY一下再加个INSERT INTO 就行了，UPDATE也可以这么用

#12

以前也用导入，还得一步一下设置，直接生成INSERT 语句就方便多了，还可以把格式什么的转一下，还可以插入多个表

#13

excel 里用left函数分割一下 导入数据库后再处理
vba
left(a1),mid(a1,256,left(len(a1)-255)分成两个列 导入后数据库操作合并

#14

引用 11 楼 guguda2008 的回复:

就是在一列里输入
="SELECT '" & A1 & "'," & B1 & " UNION ALL"
然后一拉，就出来一堆UNION ALL了，COPY一下再加个INSERT INTO 就行了，UPDATE也可以这么用

65000行的时候你咋办？

#15

引用 5 楼 wufeng4552 的回复:

SQL code--或者拷貝到文本中bulk insertbulkinsert testfrom'E:\Test.txt'with(
        FIELDTERMINATOR=',',
        ROWTERMINATOR='\n'

        )select*from test

有几十列数据呢，看来只能转成CSV试试看了。

#16

引用 6 楼 fredrickhu 的回复:

我不喜欢用语句 我一般都习惯用导入导出工具

支持。
用导入导出向导吧。

#17

可能是EXCEL中栽条记录某字段值太长！
在对应的SQL SERVER 2005表中增加字段的长度！
try

#18

引用 17 楼 soft_wsx 的回复:

可能是EXCEL中栽条记录某字段值太长！
在对应的SQL SERVER 2005表中增加字段的长度！
try

试过了，没用。源列默认最长255，真奇怪。
跟目标列长度不够不是一回事儿。

#19

我个人没怎么用过EXCEL源.
原因是我的SSIS2005不支持EXCEL 2007.
所以我都是用OleDb源然后Microsoft.ACE.OLEDB.12.0的引擎.

一般如果没有指定Extended Properties的IMEX=1强制解释为文本的话,
ACE引擎会抽取Excel前8条记录来确定列的类型的.
当引擎解释该列的类型为文本的时候.如果列最长不超过255.那类型就定为nvarchar(255)
否则就会定为ntext类型.

#20

Extended Properties的IMEX=1指定了也没有用，可能跟你说的ACE引擎会抽取Excel前8条记录来确定列的类型有关，但是前8行纪录很可能不超过255，这样不就出现了问题？

#21

引用 14 楼 pbsh 的回复:

引用 11 楼 guguda2008 的回复:
 就是在一列里输入
 ="SELECT '" & A1 & "'," & B1 & " UNION ALL"
 然后一拉，就出来一堆UNION ALL了，COPY一下再加个INSERT INTO 就行了，UPDATE也可以这么用


 65000行的时候你咋办？

那我就用VBA循环添加了，也就十分钟左右吧，6W4也不是很多
或者方法不变，数据分几批做。

#22

引用 20 楼 pbsh 的回复:

Extended Properties的IMEX=1指定了也没有用，可能跟你说的ACE引擎会抽取Excel前8条记录来确定列的类型有关，但是前8行纪录很可能不超过255，这样不就出现了问题？

我觉得很可能.

#23

引用 13 楼 billpu 的回复:

excel 里用left函数分割一下 导入数据库后再处理
vba
left(a1),mid(a1,256,left(len(a1)-255)分成两个列 导入后数据库操作合并

这种方法我测试了可行，只不过相当的麻烦，首先修改后的sheet不能包含原来有问题的列，所以要删除该列，这样的话函数生成的列就需要重新选择性粘贴到新的列，只保留数值，即切割一列需要前后进行4次操作，问题列多的时候非常痛苦。
本来直接转成CSV应该可以，可是又存在其他语言跟中文系统的兼容性问题，需要切换系统语言，也很麻烦。

总的来说MS在处理excel和sql server的转换问题时候非常差，也可能是我的经验有限吧。

谢谢billpu的方法，还好这次只有一个Sheet有问题，要不然真要昏古去了。。。

#24

引用 20 楼 pbsh 的回复:

Extended Properties的IMEX=1指定了也没有用，可能跟你说的ACE引擎会抽取Excel前8条记录来确定列的类型有关，但是前8行纪录很可能不超过255，这样不就出现了问题？

弱弱地问一句，先按长度排一下序能解决这个问题不

#25

引用 24 楼 guguda2008 的回复:

引用 20 楼 pbsh 的回复:
Extended Properties的IMEX=1指定了也没有用，可能跟你说的ACE引擎会抽取Excel前8条记录来确定列的类型有关，但是前8行纪录很可能不超过255，这样不就出现了问题？

弱弱地问一句，先按长度排一下序能解决这个问题不

手工提前排序会牵涉到一个问题，当多列过长时，你按照哪一列排序呢？一列的数据类型可能改变，其他的问题列前8行可能还是不过255，呵呵。

#26

引用 25 楼 pbsh 的回复:

引用 24 楼 guguda2008 的回复:
 引用 20 楼 pbsh 的回复:
 Extended Properties的IMEX=1指定了也没有用，可能跟你说的ACE引擎会抽取Excel前8条记录来确定列的类型有关，但是前8行纪录很可能不超过255，这样不就出现了问题？

 弱弱地问一句，先按长度排一下序能解决这个问题不


 手工提前排序会牵涉到一个问题，当多列过长时，你按照哪一列排序呢？一列的数据类型可能改变，其他的问题列前8行可能还是不过255，呵呵。

那可不可以人工添加一行所有列都够长的放在第一行呢？导完了再删掉

#27

这样试试吧.
开始->运行->regedit->

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

然后找到 TypeGuessRows这个项.默认为8的.修改为0.
然后重启一下.试试.
这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

#28

引用 27 楼 liangck 的回复:

这样试试吧.
 开始->运行->regedit->

 如果是:Jet引擎.
 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

 如果是:ACE引擎.
 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

 然后找到 TypeGuessRows这个项.默认为8的.修改为0.
 然后重启一下.试试.
 这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024

#29

引用 28 楼 billpu 的回复:

引用 27 楼 liangck 的回复:
这样试试吧.
开始->运行->regedit->

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

然后找到 TypeGuessRows这个项.默认为8的.修改为0.
然后重启一下.试试.
这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024

呵呵.别误会了.8的意思不是8位就是256..而是抽取8条记录.

lz设置完这个注册表项之后.
重新设计一下你的数据流..然后在Excel源或OleDB源配置好.
然后再进入高级编辑器..在输入列里看看数据类型.

#30

引用 29 楼 liangck 的回复:

引用 28 楼 billpu 的回复:
引用 27 楼 liangck 的回复:
这样试试吧.
开始->运行->regedit->

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

然后找到 TypeGuessRows这个项.默认为8的.修改为0.
然后重启一下.试试.
这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024


呵呵.别误会了.8的意思不是8位就是256..而是抽取8条记录.

lz设置完这个注册表项之后.
重新设计一下你的数据流..然后在Excel源或OleDB源配置好.
然后再进入高级编辑器..在输入列里看看数据类型.

很遗憾，我修改了注册表，把8改成了十进制0，重新启动机器，在导入导出向导和SSIS数据流中仍然是255，不能变长。

#31

引用 29 楼 liangck 的回复:

引用 28 楼 billpu 的回复:
 引用 27 楼 liangck 的回复:
 这样试试吧.
 开始->运行->regedit->

 如果是:Jet引擎.
 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

 如果是:ACE引擎.
 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

 然后找到 TypeGuessRows这个项.默认为8的.修改为0.
 然后重启一下.试试.
 这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

 我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024


 呵呵.别误会了.8的意思不是8位就是256..而是抽取8条记录.

 lz设置完这个注册表项之后.
 重新设计一下你的数据流..然后在Excel源或OleDB源配置好.
 然后再进入高级编辑器..在输入列里看看数据类型.

汗 原来是8条记录 哈哈 

#32

引用 30 楼 pbsh 的回复:

引用 29 楼 liangck 的回复:
引用 28 楼 billpu 的回复:
引用 27 楼 liangck 的回复:
这样试试吧.
开始->运行->regedit->

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

然后找到 TypeGuessRows这个项.默认为8的.修改为0.
然后重启一下.试试.
这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024


呵呵.别误会了.8的意思不是8位就是256..而是抽取8条记录.

lz设置完这个注册表项之后.
重新设计一下你的数据流..然后在Excel源或OleDB源配置好.
然后再进入高级编辑器..在输入列里看看数据类型.


很遗憾，我修改了注册表，把8改成了十进制0，重新启动机器，在导入导出向导和SSIS数据流中仍然是255，不能变长。

重新设计或重新编辑过你的数据流或数据源了吗?

#33

引用 32 楼 liangck 的回复:

引用 30 楼 pbsh 的回复:
引用 29 楼 liangck 的回复:
引用 28 楼 billpu 的回复:
引用 27 楼 liangck 的回复:
这样试试吧.
开始->运行->regedit->

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

然后找到 TypeGuessRows这个项.默认为8的.修改为0.
然后重启一下.试试.
这样就会抽取所有的记录来解释列的类型.但因为抽取所有的记录.所以会影响性能.

我觉得可行...可以试试 你当然也可以加大一点看看 比如默认是8位就是256 你加大到10位1024


呵呵.别误会了.8的意思不是8位就是256..而是抽取8条记录.

lz设置完这个注册表项之后.
重新设计一下你的数据流..然后在Excel源或OleDB源配置好.
然后再进入高级编辑器..在输入列里看看数据类型.


很遗憾，我修改了注册表，把8改成了十进制0，重新启动机器，在导入导出向导和SSIS数据流中仍然是255，不能变长。


重新设计或重新编辑过你的数据流或数据源了吗?

重做了，向导和数据流都看了。

#34

最后我把系统切换成葡萄牙语，然后把EXCEL转换成CSV格式，直接平面文件源进去了，怨念阿。

#35

我刚测试了一下.是可以的.

#36

引用 35 楼 liangck 的回复:

我刚测试了一下.是可以的.

外部输出列默认长度超过255了？

#37

引用 36 楼 pbsh 的回复:

引用 35 楼 liangck 的回复:
我刚测试了一下.是可以的.


外部输出列默认长度超过255了？

因为修改了这个注册表项之后.就会抽取所有的记录来解释数据类型.
所以有超过255个字符的.就被解释为ntext了.所以在高级编辑器中.看到列的数据类型为ntext

#38

引用 37 楼 liangck 的回复:

引用 36 楼 pbsh 的回复:
引用 35 楼 liangck 的回复:
我刚测试了一下.是可以的.


外部输出列默认长度超过255了？


因为修改了这个注册表项之后.就会抽取所有的记录来解释数据类型.
所以有超过255个字符的.就被解释为ntext了.所以在高级编辑器中.看到列的数据类型为ntext

为什么我有几千行超过255个字符的数据，高级编辑里面外部列依然是Unicode string [DT_WSTR] 长度255？

顺便问下，CSDN不能插图么？

#39

我之前也尝试过把这几列强制转换成ntext格式，不行。

#40

http://blog.csdn.net/liangCK/archive/2009/09/15/4555540.aspx
看看这.上传了N年才上传成功.

#41

引用 40 楼 liangck 的回复:

http://blog.csdn.net/liangCK/archive/2009/09/15/4555540.aspx
看看这.上传了N年才上传成功.

谢谢小梁，我测试了一下，ACE确实可以。
但是EXCEL源确实不可以。

BTW，你博客的头像是你女朋友吗？我怎么觉得好像认识呢？

#42

引用 41 楼 pbsh 的回复:

引用 40 楼 liangck 的回复:
http://blog.csdn.net/liangCK/archive/2009/09/15/4555540.aspx
看看这.上传了N年才上传成功.

谢谢小梁，我测试了一下，ACE确实可以。
但是EXCEL源确实不可以。

BTW，你博客的头像是你女朋友吗？我怎么觉得好像认识呢？

嗯.我没测试过EXCEL源.
原因上面说了SSIS2005的EXCEL源不支持EXCEL 2007.
SSIS 2008才支持.

BTW.嘿嘿.不是我女朋友..网上看到的..所以眼熟.不奇怪..呵呵.

#43

引用 6 楼 fredrickhu 的回复:

我不喜欢用语句 我一般都习惯用导入导出工具

是。

#44

引用 42 楼 liangck 的回复:

引用 41 楼 pbsh 的回复:
引用 40 楼 liangck 的回复:
http://blog.csdn.net/liangCK/archive/2009/09/15/4555540.aspx
看看这.上传了N年才上传成功.

谢谢小梁，我测试了一下，ACE确实可以。
但是EXCEL源确实不可以。

BTW，你博客的头像是你女朋友吗？我怎么觉得好像认识呢？


嗯.我没测试过EXCEL源.
原因上面说了SSIS2005的EXCEL源不支持EXCEL 2007.
SSIS 2008才支持.

BTW.嘿嘿.不是我女朋友..网上看到的..所以眼熟.不奇怪..呵呵.

哈哈。
其实我用的是EXCEL2003。

#45

引用 44 楼 pbsh 的回复:

引用 42 楼 liangck 的回复:
引用 41 楼 pbsh 的回复:
引用 40 楼 liangck 的回复:
http://blog.csdn.net/liangCK/archive/2009/09/15/4555540.aspx
看看这.上传了N年才上传成功.

谢谢小梁，我测试了一下，ACE确实可以。
但是EXCEL源确实不可以。

BTW，你博客的头像是你女朋友吗？我怎么觉得好像认识呢？


嗯.我没测试过EXCEL源.
原因上面说了SSIS2005的EXCEL源不支持EXCEL 2007.
SSIS 2008才支持.

BTW.嘿嘿.不是我女朋友..网上看到的..所以眼熟.不奇怪..呵呵.


哈哈。
其实我用的是EXCEL2003。

那解决了吗?

#46

解决了，我转换成CSV做的，这次是个特例，不是重复使用的包。

#47

引用 46 楼 pbsh 的回复:

解决了，我转换成CSV做的，这次是个特例，不是重复使用的包。

#48

u