如何将Excel数据动态导入到SQL Server 2008中的数据库表

本文介绍了如何将Excel数据动态导入到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) ...