Export Excel Spreadsheets to SQL Server

There are many posts out there about moving data from Excel to SQL Server and there are many twists/solutions to this need.  Here is another one that I have successfully been able to use in my projects.
In the past, I have used the OPENROWSET feature in SQL Server to upload data in an Excel Spreadsheet to a temporary/staging table in SQL Server.  That way I can use that table in queries however I want to or need to, but that comes with a cost. You would need to enable ad-hoc query feature in SQL Server. That might work in development environment, but, often times, the DBAs won’t enable that in the production environment for various security reasons.
So if you are an ASP.NET/C# developer and can quickly create an ASP.NET page or a simple C# console app, you can use this technique to quickly load data from an Excel Spreadsheet to SQL Server. This also frees you from having to upload the Excel file to the server as you would have to do in the case of OPENROWSET.
Here is the class in C# to export the data from Excel to SQL Server:
ExcelDataImport.cs
Here is one way how you could use the above class:

ExcelDataImport xlsDataImport = 
   new ExcelDataImport("data.xls", "Sheet1", "aTable");
xlsDataImport.UploadDataToSqlServerDb(connStr);

You could either pass a single sheet/table name pair or an array of sheet names and corresponding table names.
Most of the code should be self-explanatory, however, some things to keep in mind while using/running this code:

1. Names in the first row of Excel Sheet would be used as column names in the SQL Server table if the table need to be created. So, make sure first row contains valid column names. Though SQL Server allows non-standard identifiers as long as they are delimited, I prefer standard identifiers. More about this topic is at Database Identifiers.
2. As shown in the code, you could use “*” to indicate you want to import all columns in a given Sheet or specify specific list of column names just like you would do in SQL Server.
3. For table names, you could use fully qualified names as you would in TSQL, if no schema is specified, SQL Server would simply look in the default schema.
4. Target table must exist in the database and it will simply append rows to the target table in the database.
5. You could also programmatically create Tables in SQL Server from your DataTable definition as done in the sample code. Idea for this was obtained from the blog: http://clicpharmacodebits.wordpress.com/2011/01/28/create-sqlserver-table-using-smo-sql-server-management-objects-in-net/
6. Note that we are using Microsoft.ACE.OLEDB.12.0 driver, which supports both .xls and .xlsx formats unlike the old Microsoft.Jet.OLEDB.4.0. driver, which supports only the .xls format.

Other links related to this:
How to import data from Excel to SQL Server
OPENROWSET and not Text files but Excel files

Thanks for reading.
Sonny

OPENROWSET and not Text files but Excel files

The other day, I was wondering if you can query against a database that is on a remote server without first having to create a linked server in SQL Server 2005. A co-worker told me that I could use OPENROWSET and then I read the following page on MSDN, which gives a very good overview of what it is and how to use it:

OPENROWSET Help Page on MSDN

Most of the SQL Experts out there already knows about this functionality, but I was very excited to learn about it. I spent quite a bit of time learning about it as it seems very powerful in what it can do, I mean, you can use it to not only query databases on remote server, you could also use it with your text files, Excel spreadsheets etc. Suppose you have textual data that relates to some tables in your database and using OPENROWSET you could turn that data table and use it like any other table.

For example, just let's say you have a business and you have a database similar to Northwind and you received an Excel spreadsheet from one of your consultants that you hired to do a customer satisfaction survey for all your customers in Canada. And your consultant is one of those people, who only works with Excel to do all her tracking. Since your consultant only works with Excel, you exported few columns (CompanyName, ContactTitle, ContactName, Phone) from the Customers table to an Excel file and gave her that file.

You ran the following query to create the Excel file:

SELECT CompanyName, ContactTitle, ContactName, Phone
FROM Customers
WHERE Country = 'Canada'

You got the following rows in the Excel file:



She filled in the satisfaction on a scale of 1 to 10 for last year on quartely basis and optional comments associated with each score. She sent you back the Excel sheet with the following extra columns:




So, now you can join this data as a table in your queries or import into a temporary table using something like this:

SELECT A.* INTO #temp1 FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\CanadaCustomersWithScores.xls','SELECT * FROM [Sheet1$]' ) AS A
WHERE A.CompanyName IS NOT NULL

Assuming the data is in Sheet1 and by saying CompanyName IS NOT NULL we will only include rows that are valid.

Now, you might ask, can I do this with a text file instead of using Excel. Theortically yes you can, at least, according to the help page listed above. However, I was not able to get it working using the samples given there plus another annoying thing about using a text file is that you need to have a format file. It seems like you would have better flexibility using an Excel file, plus you can easily edit the data in Excel and the OPENROWSET seems to work without must fuss.

Another thing to note here is that, I have used xls files not xlsx file because I could not get to link to xlsx (Excel 2007) files with this provider. My later research revealed to me that I need to use a different provider to open xlsx files, which I listed later on this post. I also wonder if I can use OPENXML on xlsx sheets, which I am going to try it out later.

Speaking of OPENXML, I am planning to explore other OPENSET functions and will post my findings in a future blog:

CONTAINSTABLE
FREETEXTTABLE
OPENDATASOURCE
OPENQUERY
OPENXML

Another thing I had a hardtime trying to find is the typical list of providers as it does not list them in the help page and few searches on the web did not turn up anything. Then later on I stumbled onto the node in Object Explorer in SQL Server Management Studio that lists all the providers that are registered on my system. To see the list on your SQL Server instance:

Expand the Server Objects node > Linked Server > Providers.

You don't have SQL Server installed, but you still would like to know all the OLE DB Providers registered on your system, you could try the following C# code:

Guid guidOleDbEnum = new Guid("{C8B522D0-5CF3-11ce-ADE5-00AA0044773D}");

Type type = Type.GetTypeFromCLSID(guidOleDbEnum);

using (OleDbDataReader rdr = OleDbEnumerator.GetEnumerator(type))
{

while (rdr.Read())

Console.WriteLine("{0} - {1}", rdr["SOURCES_NAME"], rdr["SOURCES_DESCRIPTION"]);

}

Here is the list of providers that I see on my SQL Server instance:

ADsDSOObject - OLE DB Provider for Active Directory Services
DTSPackageDSO - OLE DB Provider for DTS Package Decision Support Object
Microsoft.ACE.OLDDB.12.0 - OLE DB Provider for Microsoft Access/Excel 2007
Microsoft.Jet.OLDDB.4.0 - Microsoft Jet OLE DB Provider for ISAM Database, e.g. Access/Excel 2003, dBase III, Lotus WK3, Paradox 5.x etc.
MSDAIPP.DSO - OLE DB Provider for Microsoft Internet Publishing Provider
MSDAORA - Microsoft OLE DB Provider for Oracle
MSDAOSP - Microsoft OLE DB Simple Provider
MSDASQL - Microsoft OLE DB Provider for ODBC
MSDMine - Microsoft OLE DB Provider for Data Mining Services
MSIDXS - Microsoft OLE DB Provider for Index Server
MSOLAP - Microsoft OLE DB Provider for OLAP Services (or Analysis Services)
SQLNCLI - Microsoft SQL Native Client OLE DB provider
SQLOLEDB - Microsoft OLE DB Provider for SQL Server 2000
SQLReplication.OLEDB - Microsoft SQL Server Replication OLE DB Provider for DTS
SQLXMLOLEDB.4.0 - OLE DB provider that exposes Microsoft SQLXML functionality through ActiveX Data Objects (ADO)

I do not think we can use all of these with OPENROWSET function, I would have to investigate as to which ones can be used and which one can't be used. I kind of feel bad to leave so many questions unanswered in this post, but at the same time, it is giving me an opportunity to learn more about this area and share it with my blog readers.

I feel like I am just scratching the surface and there is a lot to learn in this area.

I would love to hear from you what you think about the OPENROWSET function and the related information and any of your experiences in using it.

Thanks for reading.