The purpose of this article is to demonstrate how to retrieve data from an Excel sheet and put it in a table in a SQL Server database. Introduction Anyone. Hi Experts, I have an aspx page that is creating a db connection using the following code dbconn New OleDbConnection ProviderMicrosoft. Jet. OLEDB. 4. 0. Retrieving Data From Excel Using OPENROWSET. The purpose of this article is to demonstrate how to retrieve data from an Excel sheet and put it in a table in a SQL Server database. Introduction. Anyone whos ever used a computer for a significant amount of time has probably come into contact with Excel, the spreadsheet application part of the Microsoft Office suite. Its main purposes are to perform calculations and create charts and pivot tables for analysis. But people have great imagination and invent new uses for it every day. Ive even seen it used as a picture album. Sorry dad, but I know you wont be reading this anyway. Ever since he had this specific YACI, or Yet Another Computer Issue, because his PC wasnt powerful enough to open his 4. MB Excel file, uh, picture collection, he took some evening classes. Hes now putting his Photoshopped pictures in Power. Point.  Anyway, lets get back on track now. Another use, and the one thats the subject of this article, is when Excel has been used as a database. Come on, you know what Im talking about, with the first row containing the column headers followed by possibly thousands of data rows. The following screenshot contains an example, and is also the file that I will be using in this article. I took all records from the Production. Product table in the Adventure. MicrosoftODBC Driver Manager Data source name not found and no default driver specified If you keep running into this on the 64 bit versions of windows, ie server. Je possde un PC sous Windows 7 Professionnel 64 bits et jai programm une application JAVA devant avoir accs une base de donnes Microsoft Access. Mon. QAPlanet. QTP 10. M. Sreedhar Reddy. Quick Test Professional 10. M. Sreedhar Reddy Test Architect www. UploadFile/rahul4_saxena/crystal-report-from-more-than-one-table-in-Asp-Net/Images/OLE%20DB%20Provider%20for%20SQL%20Server.jpg' alt='Jet.Oledb.4.0 Windows 7 64 Bit' title='Jet.Oledb.4.0 Windows 7 64 Bit' />Works 2. R2 database and dumped them in Excel. At some point people will realize, either because someone told them or because they lost some data due to inattentiveness, that it wasnt a really good idea to keep all that data in an Excel sheet. And theyll ask you to put it in a real database such as SQL Server. Thats what Im going to show you in the next paragraphs how to import data from Excel into SQL Server. Using OPENROWSET To Query Excel Files. There are actually several different ways to achieve this. In this article I will use the OPENROWSET function. This is a T SQL function that can be used to access any OLE DB data source. All you need is the right OLE DB driver. The oldest version which I could confirm that contains this function is SQL Server 7. My sample Excel files are located in C temp. This folder contains two files Products. Products. xlsx.  The first file is saved in the old format, Excel 9. Excel 2. 01. 0.  Both files contain the same data. The sheet containing the list of products is called Product. List. And here are the queries Excel 2. Home Audiometer Hearing Test Serial. SELECT INTO productlist. FROM OPENROWSETMicrosoft. ACE. OLEDB. 1. 2. Excel 1. 2. 0 Xml HDRYES DatabaseC tempProducts. SELECT FROM Product. List Excel 9. SELECT INTO productlist. FROM OPENROWSETMicrosoft. Jet. OLEDB. 4. 0, Excel 8. HDRYES DatabaseC tempProducts. Product. List Select all. Open in new window. These queries are just returning the data from the Excel file into the Results window, when executed using the Management Studio. To insert the data into a table, uncomment the INTO clause. When uncommented, the statement retrieves the data from the Excel sheet and puts it into a newly created local temporary table called productlist. Furthermore, the query assumes that the first row contains the header. If thats not the case, replace HDRYES with HDRNO. Note if you get an error message when running the query, look further down in this article. Ive covered a couple of them. With the INTO clause uncommented and the query executed, the temporary table can now be queried just like any other table SELECT FROM productlist. Select all. Open in new window. What Type Is Your Data Lets have a look if this method of using a SELECT INTO in combination with OPENROWSET and a temporary table is smart enough to interpret the correct data types of the data coming in. Use the following command to describe the metadata of the temporary table USE tempdb GOsphelp productlist Select all. Open in new window. Because a temporary table is stored in the tempdb, the sphelp command should be issued against that database. Heres the part of the output in which were interested As you can see, anything that looks like text will be put in a field of type nvarchar5. Not a lot of intelligence there. This is the result when no formatting was put on the cells in Excel. As an experiment Ive changed the format of some fields in the Excel file and then retried the SELECT INTO statement. What did I changeI identified Product. ID as being a number without any decimals, changed Standard. Cost and List. Price to a currency with four decimal digits and I changed Sell. Start. Date and Sell. End. Date to a custom datetime format showing both date and time. The effect on the table creation was not completely as I would have expected Product. ID is still being stored into a float field, even though in Excel its defined as having no decimals. And the datetime values are not recognized either. Okay, I used a custom format there, so maybe its due to that. Its up to you of course how you use this method of importing the data. You can put your records into a temporary table to process further, or you can create a table with the expected data types upfront and import the data directly into that one. Some Possible Issues. Lets cover some issues related to this method. Enable AD Hoc Distributed Queries. The OPENROWSET function expects that the Ad Hoc Distributed Queries option is enabled on the server. When thats not the case youll see the following message Msg 1. Level 1. 6, State 1, Line 1. SQL Server blocked access to STATEMENT Open. RowsetOpen. Datasource of component Ad Hoc Distributed Queries because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of Ad Hoc Distributed Queries by using spconfigure. For more information about enabling Ad Hoc Distributed Queries, see Surface Area Configuration in SQL Server Books Online. This is one of the advanced options. To enable it you can use the following command spconfigure show advanced options, 1 GORECONFIGURE GOspconfigure Ad Hoc Distributed Queries, 1 GORECONFIGURE GOSelect all. Open in new window. To get a good look at all the different settings, just run the spconfigure procedure without any parameters. Note if youre not the administrator of the server, you should talk to the DBA whos responsible before attempting this. The File Needs To Be Closed. When the Excel file is not closed, youll end up with the following error Msg 7. Level 1. 6, State 1, Line 1. The OLE DB provider Microsoft. Jet. OLEDB. 4. 0 for linked server null reported an error. The provider did not give any information about the error. Msg 7. 30. 3, Level 1. State 1, Line 1. Cannot initialize the data source object of OLE DB provider Microsoft. Qpst Software Tool. Jet. OLEDB. 4. 0 for linked server null. So close the file and try the query again. OLE DB Driver Not Installed. The OPENROWSET function uses OLE DB, so it needs a driver for your data source, in this case for Excel. If the right driver is not installed, youll see the following error or similar, depends on the version used. Msg 7. 30. 2, Level 1. State 1, Line 1. Cannot create an instance of OLE DB provider Microsoft. ACE. OLEDB. 1. 2. To solve the issue, install the right driver and try again. How can you tell what drivers are installed Open up the ODBC Data Source Administrator window Start  Run  type ODBCAD3. EXE and enter and have a look in the Drivers tab. The following screenshot taken on a Dutch Windows XP shows both the JET 4.