紧急,关于opendatasource语句

wildroseck 2010-10-29 06:02:42

现有一EXCEL导入语句,写固定路径没问题,如下:
insert into heiwado.WEB_NEW_MEMBER_TBL select *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\192.168.6.36\card\card.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

现想改为以日期为名的文件如下,就不知道怎么去拼接那个字符串,怎么拼都错,如下:
insert into heiwado.WEB_NEW_MEMBER_TBL select *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\192.168.6.36\card\'+ convert(varchar,getdate(),112) + '.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

...全文

142 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

dawugui 2010-10-29

打赏
举报

exec('insert into heiwado.WEB_NEW_MEMBER_TBL select *
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="\\192.168.6.36\card\'+ convert(varchar,getdate(),112) + '.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]')

-->

insert into heiwado.WEB_NEW_MEMBER_TBL select *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\192.168.6.36\card\20101029.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

试过上面这段了吗？

dawugui 2010-10-29

打赏
举报

7楼有错？

wildroseck 2010-10-29

打赏
举报

按楼上的写了后,
insert into WEB_NEW_MEMBER_TBL select *
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="\\192.168.6.36\card\''+convert(varchar(8),getdate(),112)+ ''.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]
这样写了后,报Microsoft 附近有语法错误

dawugui 2010-10-29

打赏
举报

再测试一下，应该这样：

exec('insert into heiwado.WEB_NEW_MEMBER_TBL select *  

FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',

  ''Data Source="\\192.168.6.36\card\'+ convert(varchar,getdate(),112) + '.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]')

「已注销」 2010-10-29

打赏
举报

DECLARE @str VARCHAR(100)  

SET @str='Data Source="\\192.168.6.36\card\'+ convert(varchar,getdate(),112) + '.xls"'

SELECT @str



---------------------------------------------

Data Source="\\192.168.6.36\card\20101029.xls"



(1 row(s) affected)

dawugui 2010-10-29

打赏
举报

[Quote=引用 3 楼 wildroseck 的回复:]
报的错是"+号附近有错误"
[/Quote]这样：

exec('insert into heiwado.WEB_NEW_MEMBER_TBL select *  

FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',

  ''Data Source="\\192.168.6.36\card\''+ convert(varchar,getdate(),112) + ''.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]')

dawugui 2010-10-29

打赏
举报

[Quote=引用 2 楼 wildroseck 的回复:]
楼上,不行,还是报+号附近有错误

就是这样写也不行,不是CONVERT函数的问题,是拼接的问题

'Data Source="\\192.168.6.36\card\'+ '12354' + '.xls";User
[/Quote]

exec('insert into heiwado.WEB_NEW_MEMBER_TBL select *  

FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',

  ''Data Source="\\192.168.6.36\card\'''+ convert(varchar,getdate(),112) + '''.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[Sheet1$]')

wildroseck 2010-10-29