如何将Excel数据动态导入到SQL Server 2008中的数据库表 [英] How to dynamically import excel data to database table in sql server 2008

问题描述

你好朋友
在一种情况下，我想从动态Excel文件中加载SQL Server中的表.在C#代码中，我只是获取文件名，然后将其作为存储过程的参数传递给sql Server.但是在我的存储过程中，我需要从中导入数据将该特定文件添加到数据库表中.直到传递文​​件名我都可以做到.但我不知道如何进行查询以导入此excel文件.
这是我的存储过程.错误接近+语法

Hi friends
In a situation that i want to load table in sql server from dynamic excel file.In c# code i just get the file name and i pass it to sql server as a parameter to storedprocedure.but in my stored procedure i need to import data from this particular file to the database table. upto passing the file name i can do it. but i dont know how to make query to import this excel file.
This is my stored procedure. the error is near + syntax

create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
 as
 set nocount on
 --declare @sql nvarchar(max)
 begin
 --insert into General_holydays_details
 select *  from OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE='+@filname+ 'Select Date,Day,Reason from [sheet1$])'

 end

推荐答案

)' 结束

)' end

您没有发布确切的错误，但在查询中却出现了错误，我猜您的文件名中没有撇号.

另外，如果我没记错的话，openrowset命令应连接到字符串，然后在使用变量的情况下执行.

因此，请尝试使用以下内容:

You didn''t post the exact error you''re getting but bsaed on your query, I''d guess that you don''t have apostrophes in your filename.

Also if I recall correctly the openrowset command should be concatenated to a string and then executed if you use variables.

So, have a try with something like:

...
declare sqltext varxhar(1000);
...
set sqltext = 'select * into General_holydays_details from OPENROWSET(''Microsoft.jet.OLEDB.4.0'',''Excel 8.0;DATABASE='''+@filname+ '''Select Date,Day,Reason from [sheet1

)'中选择日期，日期，原因； 打印(sqltext) 执行(sqltext) ...

)'; print (sqltext) execute (sqltext) ...

这篇关于如何将Excel数据动态导入到SQL Server 2008中的数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！