导入问题

Question

declare @s varchar(5000)
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="e:\aa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'insert into HR.dbo.tb_youxiaouser SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)执行提示如下:服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。
---------------------------------------------------------
tb_youxiaouser定义如下:
1 WEIJIBIANMA char 7
0 NASHUIRENJC varchar 50
0 DIANHUA varchar 50 1
0 YOUXIAORIQI datetime 8
0 CHUANJIANREN varchar 50
0 NASHUIRENJC_py varchar 50
----------------------------------------------------------------------
aa.xls内容如下:
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王
1234567 王王王王 1111111 2007-12-12 sxl 王王王王

Answer 1

xls導的時候,時間欄位經常會出現這種情況,
建議先導臨時表,(即把YOUXIAORIQI設為varchar)
然後再將臨時表數據導入HR.dbo.tb_youxiaouser ,導的時候,轉換類型

Answer 2

declare @s varchar(5000)
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="e:\aa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'insert into HR.dbo.tb_youxiaouser SELECT [f1], [f2], [f3], convert(dateime,[f4]), [f5], [f6] FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)

Answer 3

to :michaelgy(guye) ( )
提示:
服务器: 消息 243，级别 16，状态 1，行 1
类型 dateime 不是已定义的系统类型。
to:playwarcraft(时间就像乳沟,挤挤还是有的) 
我正在尝试谢谢大家的答

Answer 4

to :michaelgy(guye) ( )
提示:
服务器: 消息 243，级别 16，状态 1，行 1
类型 dateime 不是已定义的系统类型。不好意思,输错了不好意思,dateime->datetime搞定了

Answer 5

CREATE TABLE tb_youxiaouser
(
WEIJIBIANMA VARCHAR(70),
NASHUIRENJC VARCHAR(50),
DIANHUA varchar(50),
YOUXIAORIQI datetime,
CHUANJIANREN varchar(50),
NASHUIRENJC_py varchar(50)
)
declare @s varchar(8000)
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="e:\TEST.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'insert into TEST.dbo.tb_youxiaouser SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
SELECT @s
exec(@s)
测试已经通过，导入结果：
WEIJIBIANMA                                                            NASHUIRENJC                                        DIANHUA                                            YOUXIAORIQI             CHUANJIANREN                                       NASHUIRENJC_py
---------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- --------------------------------------------------
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
1.23457e+006                                                           王王王王                                               1.11111e+006                                       2007-12-12 00:00:00.000 sxl                                                王王王王
可尝试将excel中的时间列转换为时间格式。再进行导入。