.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

import multiple Excel 2007 files using openrowset

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
hello, I Have a folder that includes multiple excle files (ver 2007), i am trying  to loop through each file using vbscript to import its data to sql 2000 using openrowset but i am getting this error in the openrowset line "[Microsoft][ODBC sql server driver] [sql server] [OLE/DB provider returned message: the microsoft office access DB engine cannot open or write to the file ', it is already open exclusively by another user or you need permission to view or write its data] code 80040E14 source microsoft OLEDB provider for ODBC drivers Any help please    

View Complete Post

More Related Resource Links

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

Import Multiple Excel Files of Different Format

Looking for suggestion on how to go about importing multiple distinctly different excel spreadsheets into separate tables. File names will be unique and wondered what options there are in SSIS to conditionally process files. Running SQL Server 2005 and BIDS 2005. What I would like to stay away from is 1 package for each file. Thanks.

Excel 2007 Filter on multiple values

Hi Everyone,How do I use Excel 2007 Pivot Table's label filters/value filters to filter with multiple conditions? for example, I want to get all customers with customer name start with "G" and doesn't include "E" and with measure>100 and measure between 50-60.Regards,George

Import Multiple XML files into Sql Server

Hi, i have nearly 1000 xml files in one folder of similar format and I have xsd for the same as well. I would like to import all the files in to sql table by either looping through all the files or any other way. Thanks

Import Multiple Text Files

I have 150 data delimited text files that need to be imported to a text file. Each of them is named for the table for which they contain data e.g. address.txt contains data that needs to imported to the address table. So what I have done is create a SSIS package that contains a ForEach loop that iterates through the the files, and has a Load Data field object. What I haven't been able to figure out is what logic needs to be added for the Data Flow. I know I will need a Flat File Source with a connection source that is tied to file it is processing.  However, after all of that I am not sure what do because each text file contains a different table with different columns. Can someone explain to me how to handle this? Thanks, Isaiah 

Text truncated to 255 using SSIS ACE.oledbprovider to import excel 2007 spreadsheet

Hi All, Trying to use SSIS SQL 2005 to import an Excel 2007 file (.xlsx). I'm using Office 12.0 Access Database Engine OLEDB Provider with extended properties Excel 12.0;HDR=Yes;IMEX=1 The problem is when I have text with more than 255 characters. It basically defaults the source output to Unicode string[DT_WSTR] 255, If I change it it errors out. I have done the registry change according to http://msdn.microsoft.com/en-us/library/ms141683.aspx but I think it only applies to Excel source not OleDb Source. (A Excel source does give me a text instead of string )   I have also tried to increasing the length of output such as 4000 instead of 255 but it doesn't allow either.   Anybody can help?I'm stuck for a day. Thank you very much for your help. Regards, Koala  

How to import two excel files into one list?

I know that I can export Excel data into new list in MOSS 2010 via Excel 2010. But when I was trying to add data into existing list, it was impossible. I know one way - copy data in Excel and paste it into the list in DataSheet View. Is there another way?

Creating Multiple Excel files Using SSIS

Hello Friends,  I have to create multiple excel files(Per Year eg 2008 , 2009, 2010....) from datatable using SSIS  For that i have to use one template excel file that is already created on some location.  Any help would be appreciated.  Thanks Thanks

Is there a way to upload multiple files to a SharePoint List Item in MOSS 2007? I can not use Doc Li



Is there a way to upload multiple files to a SharePoint List Item in MOSS 2007? I can not use Doc Library because of the funcationality.

Thank you,



How to merge sheets from multiple excel files into one .xls file?


 I have several excel spreadsheet fils, each of them contains multiple work sheets that may storing images or data.

 How could I merge all of these work sheets to just one .xls file and keep their original layout and style using asp.net?

 Thanks in advance.

OPENROWSET on Excel 2007 file



I am seriously loosing hair over this one! I have successfully used OPENROWSET to query data in an Excel 2003 file (*.xls) in SQL Server, however I am now looking to replicate this functionality with an Excel 2007 (*.xlsx) file.


I have tried the usual google searches but nothing concrete seems to come up, can anyone give me any pointers?


My Excel 2003 file sql is as follows: (works great)


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls',

Import Excel-File (OPENROWSET) on x64 SQL Server


Hi, recently I encountered the following problem:

I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.

We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.


Is there another way to import excel data using a stored procedure.


thank you in advance, rene

Merging Multiple Excel files (exports from Reporting Services)


So we have Office 2007 on our system and I'm trying to use Excel Interop to take a number of Excel files, which are exports from Reporting Services, and combine them into one large workbook.  I'm using the code below which works fine to get all the information from all the files into one workbook but the colors of things goes absolutely haywire.  Anyone know why my colors are getting all out of wack?

protected void MergeFiles(string destinationFile, string[] sourceFiles, string guid)
                //1. Validate folder,  
                //2. Instantiate excel object  
                //3. Loop through the files  
                //4. Add sheets  
                //5. Save and enjoy!  


SSIS package to import excel 2007 with different column numbers



I have an already developed SSIS 2005 package which can import maximum of 50 columns of *.xlsx file. The file format is same except with the column numbers. I have many components in SSIS package (e.g. unpivot data, derrived column etc) which are developed for 50 columns. The package works fine for importing 50 columns. But sometimes it fails for excel sheet having 12 columns (columns < 50) it gives error for the source columns cannot be found. Surprisingly, if the contents are copied to another file it may work but not surely. Please let me know if I can do anything in existing package.

Thanks in advance,

Import List of Worksheet Names from Multiple Sheets Excel File

I am only interested in OPENROWSET/OPENQUERY or xp_cmdshell solutions. Thanks.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Excel 2007 Spreadsheet - need to import it to SQL Server 2005


Trying to import spreadsheet from Excel 2007 to a table in SQL Server 2005, when I follow the steps for a linked server ,, I am able to create a linked server but because the instructions I am following are calling for a Microsoft 4.0 Jet something or another as "Provider" and I do not have that I am getting an error.. I have the a list of providers but the 4.0 Jet is not one of them...


Any help is greatly appreciated

Import multiple xml files(sharepoint webservices out put) to sql database using ssis



I am importing xml file in to sql server database i was able to do this task for single xml file in to sql database using ssis( i am using dataflow task, xml source, data conversion, oledb destination creating a ssis package).

Now i am having 3 xml files, need to store in different tables in sql server database can someone explain how i can import multiple xml files in to different tables in sql.can we create a xsd for each xml file while importing.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend