Excel导入SQL　Server时，类型转换问题

临座城池 2014-02-19 09:26:25

请问大家：

我在用SQL Server的task时，是把数据从Excel中导入SQL Server的数据库表中。数据库里的表示已经建好了的，每个字段的类型也是确定好的。

然后在我导数据的过程中，发现：Excel中的数据类型从Float无法转换到数据中表的Int类型；Excel中的Datetime类型不能转换到数据库中表的Date类型。。然后就是导数据出错了。。

这里主要问下大家的解决方案。。。。数据库里面表的数据类型是不可以改的，我的尝试是在Excel里面把全部类型转换成Text类型，结果还是这两个类型转换的时候出来错。。

求大家的解决方案，，急求，谢谢了！！！

...全文

2121 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

changuncle 2016-10-13

打赏
举报

我也来顶一贴，遇到了double无法转换为int的问题，谢谢各位的回答。

LongRui888 2014-02-20

打赏
举报

引用 15 楼 ling45480867 的回复:

[quote=引用 14 楼 spiritofdragon 的回复:] 用公式转成真文本啊。=YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2)

解决了，谢谢。。。不过在mapping的时候，Excel字段还是显示的格式是Varchar类型，但是可以转为SQL的Date类型。。。。我把Excel的类型转为Datetime的时候，是不可以转换成为SQL的date类型的。。。不知道其中的原因。。。麻烦大神解释下哇。谢谢。。[/quote] 那估计是excel里面也存储了时间部分，所以只能转成datetime，而没办法转成了date类型把

临座城池 2014-02-20

打赏
举报

引用 16 楼 spiritofdragon 的回复:

...9楼最后一段，你没仔细看么！！！

额，好吧，谢谢哈。。

spiritofdragon 2014-02-20

打赏
举报

...9楼最后一段，你没仔细看么！！！

临座城池 2014-02-20

打赏
举报

引用 14 楼 spiritofdragon 的回复:

用公式转成真文本啊。=YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2)

解决了，谢谢。。。不过在mapping的时候，Excel字段还是显示的格式是Varchar类型，但是可以转为SQL的Date类型。。。。我把Excel的类型转为Datetime的时候，是不可以转换成为SQL的date类型的。。。不知道其中的原因。。。麻烦大神解释下哇。谢谢。。

spiritofdragon 2014-02-20

打赏
举报

用公式转成真文本啊。=YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2)

临座城池 2014-02-20

打赏
举报

引用 9 楼 spiritofdragon 的回复:

这不是动数据库的事，是Excel保存数据总是这样：

在Excel里：

数字字段里估计是有大数值，这种会被当成科学技术法"x.xxExx"。这种导入是就是带"E"的文本，这种当然不能转成Int型，遇到这种字段，你先在Excel把它转成真数字文本（不带E）。用Excel里的Text函数。

时间型在Excel里存的数据类型是4万多的浮点型，这你应该知道吧。数字导入sql里的Date型，当然要报错。所以这种字段也要在Excel里先加工好。比如用公式：=YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2)
这样不管用task还是用openrowset，都行了

你好可以具体说下这个么？
我的数据是这样的：
一个列里面都是：2014/2/18
应该怎么加工，然后才能导入sql里的date类型啊？？

谢谢了，，急求。。

spiritofdragon 2014-02-20

打赏
举报

当然还有别的方便方法，只是不好用文字描述。比如我一般遇到这种烦人的类型，都把Excel另存为TXT，在用SQL向导把txt导入SQL。这样比写公式加工快点

临座城池 2014-02-20

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

方案1：用sqlserver自带的导入导出工具，在导入过程中有可以给你选择对应类型的
方案2：用openquery/openrowset这些导入语句。
方案3：在excel中拼接insert语句，然后批量生成，这完全是excel操作了，以前初始化银行数据的时候用过，也不是完全没用处

这个图。。

临座城池 2014-02-20

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

方案1：用sqlserver自带的导入导出工具，在导入过程中有可以给你选择对应类型的
方案2：用openquery/openrowset这些导入语句。
方案3：在excel中拼接insert语句，然后批量生成，这完全是excel操作了，以前初始化银行数据的时候用过，也不是完全没用处

这样的问题，没有解决。。

spiritofdragon 2014-02-20

打赏
举报

这不是动数据库的事，是Excel保存数据总是这样：在Excel里：数字字段里估计是有大数值，这种会被当成科学技术法"x.xxExx"。这种导入是就是带"E"的文本，这种当然不能转成Int型，遇到这种字段，你先在Excel把它转成真数字文本（不带E）。用Excel里的Text函数。时间型在Excel里存的数据类型是4万多的浮点型，这你应该知道吧。数字导入sql里的Date型，当然要报错。所以这种字段也要在Excel里先加工好。比如用公式：=YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2) 这样不管用task还是用openrowset，都行了

LongRui888 2014-02-20

打赏
举报

引用 7 楼 ling45480867 的回复:

[quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 ling45480867 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 你可以用sql server 的导出数据的功能来试试的：

我是说导入SQL Server的。。task里面没有对应的数据类型？[/quote] 你的意思是，你用的是sql server的导入数据的功能是吧，用这个代码试试。之前，我在导入数据的时候，也有类似的问题，主要是导入的时候，报数据类型错误，你可以先通过，下面的语句，查询出数据，然后可以进行数据类型的转换：

--3.2.1用openrowset函数,引用4.0的库
select *
from 
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  
            sheet1$)                        
            
select *
from
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  --文件必须存在
           'select * from [sheet1$]')       --表必须存在


/*=========================================================================
如果在要导入数据的目标机器上装有office2007及其以上版本，
那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,
excel的版本为:12.0

注意：不能在openrowset中写任何的空格等多余的字符，
      否则会报错，而且错误没有针对性
===========================================================================*/
--3.2.2用openrowset函数,引用12.0的库
select *
from openrowset('microsoft.ace.oledb.12.0',
                'Excel 12.0;database=c:\test.xlsx',
                'select * from [sheet1$]')

[/quote] 你的意思是先用Openrowset函数，先查询出数据，然后自己进行类型转换之后，再插入数据库中，对么？ [/quote] 对的，就是这个意思，先尝试一下

临座城池 2014-02-20

打赏
举报

引用 6 楼 yupeigu 的回复:

[quote=引用 4 楼 ling45480867 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 你可以用sql server 的导出数据的功能来试试的：

--3.2.1用openrowset函数,引用4.0的库
select *
from 
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  
            sheet1$)                        
            
select *
from
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  --文件必须存在
           'select * from [sheet1$]')       --表必须存在


/*=========================================================================
如果在要导入数据的目标机器上装有office2007及其以上版本，
那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,
excel的版本为:12.0

注意：不能在openrowset中写任何的空格等多余的字符，
      否则会报错，而且错误没有针对性
===========================================================================*/
--3.2.2用openrowset函数,引用12.0的库
select *
from openrowset('microsoft.ace.oledb.12.0',
                'Excel 12.0;database=c:\test.xlsx',
                'select * from [sheet1$]')

[/quote] 你的意思是先用Openrowset函数，先查询出数据，然后自己进行类型转换之后，再插入数据库中，对么？

LongRui888 2014-02-20

打赏
举报

引用 4 楼 ling45480867 的回复:

[quote=引用 2 楼 yupeigu 的回复:] 你可以用sql server 的导出数据的功能来试试的：

--3.2.1用openrowset函数,引用4.0的库
select *
from 
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  
            sheet1$)                        
            
select *
from
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  --文件必须存在
           'select * from [sheet1$]')       --表必须存在


/*=========================================================================
如果在要导入数据的目标机器上装有office2007及其以上版本，
那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,
excel的版本为:12.0

注意：不能在openrowset中写任何的空格等多余的字符，
      否则会报错，而且错误没有针对性
===========================================================================*/
--3.2.2用openrowset函数,引用12.0的库
select *
from openrowset('microsoft.ace.oledb.12.0',
                'Excel 12.0;database=c:\test.xlsx',
                'select * from [sheet1$]')

临座城池 2014-02-20

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

方案1：用sqlserver自带的导入导出工具，在导入过程中有可以给你选择对应类型的方案2：用openquery/openrowset这些导入语句。方案3：在excel中拼接insert语句，然后批量生成，这完全是excel操作了，以前初始化银行数据的时候用过，也不是完全没用处

我用的是方案一。。。但是就是在选数据类型的时候，，Excel中的数据类型从Float无法转换到数据中表的Int类型；Excel中的Datetime类型不能转换到数据库中表的Date类型。。然后就是导数据出错了。。。数据库表的类型不能改了，，但是问下excel里面对应的数据类型，该怎么选？

临座城池 2014-02-20

打赏
举报

引用 2 楼 yupeigu 的回复:

你可以用sql server 的导出数据的功能来试试的：

我是说导入SQL Server的。。task里面没有对应的数据类型？

LongRui888 2014-02-20

打赏
举报

也可以通过openrowset来导出，这个适合sql server 2005及以上的版本：

--3.2.1用openrowset函数,引用4.0的库
select *
from 
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  
            sheet1$)                        
            
select *
from
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls',  --文件必须存在
           'select * from [sheet1$]')       --表必须存在


/*=========================================================================
如果在要导入数据的目标机器上装有office2007及其以上版本，
那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,
excel的版本为:12.0

注意：不能在openrowset中写任何的空格等多余的字符，
      否则会报错，而且错误没有针对性
===========================================================================*/
--3.2.2用openrowset函数,引用12.0的库
select *
from openrowset('microsoft.ace.oledb.12.0',
                'Excel 12.0;database=c:\test.xlsx',
                'select * from [sheet1$]')

LongRui888 2014-02-20