Wednesday, March 28, 2012

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

No comments: