Importing Excel into SQL using ASP.NET 2.0 and SqlBulkCopy
Posted on June 26, 2006 | 11 Comments | No TrackBacks
We were recently faced with a challenge at work where the client wanted to provide an Excel file of data which could be imported into SQL and used to update data within certain tables. While this is a snap via Enterprise Manager, it's trickier when the client wants it all to happen on the back-end so they can do it themselves via a web form.
While there are numerous tutorials for exporting data into Excel format, there didn't seem to be many on how to get the data in, so I'm posting this function in the hopes that it helps someone else.
I've stripped out most of the code so that this is JUST the part that imports the data from Excel. This example assumes that the.xls file is already on your server in the same location as this script. You can write your own upload functions to get it there. (If you need help with uploading, let me know and I'll post an article on that as well) This also assumes that you have a table in SQL that matches the schema of your spreadsheet.
This uses the new SqlBulkCopy class in ASP.NET 2.0 which lets you easily copy bulk amounts of data from one source to another.
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Web.Configuration
'Declare Variables - Edit these based on your particular situation
Dim sSQLTable As String = "TempTableForExcelImport"
Dim sExcelFileName As String = "myExcelFile.xls"
Dim sWorkbook As String = "[WorkbookName$]"
'Create our connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
Dim sSqlConnectionString As String = WebConfigurationManager.ConnectionStrings("MyConnectionString").ToString
'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable
Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString)
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
SqlConn.Close()
'Series of commands to bulk copy data from the excel file into our SQL table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)
OleDbConn.Open()
Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)
bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)
OleDbConn.Close()
NOTE: I left out any error handling functionality for the sake of brevity but it would be a good idea to use a Try/Catch block to handle any exceptions
Nice Post, I wonder if there is a way to make this work other way around? I mean to export data from sqlserver to excel?
Hi
Thank you very much for the article about sqlbulkcopy.
It was very interesting and it really helped me.
best wishes, Meysam
Just wondering if you know of a way to get data from Excel without knowing the worksheet name? I'm trying to automate an upload process but the worksheet name is randomized. I'd be glad of any ideas?
David Hayden has a great article on how to use the ADO.NET GetSchema method which should help you retrieve a dynamically generated workbook name from Excel.
Check out http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx
Mate, your a Genius / Life Saver.
I've been surfing around the Internet looking for a way to upload an excel spreadsheet onto a server, then Import that Data Into an Existing SQL Server Table. Your right there's not much around "how to get the data in". And I must say this is a real life example where there's a huge need because a client needs this. Most examples out there on the internet focus on the theory side of how to do things. But when it comes to actually doing this for client, all these generic ways are useless.
Anyway this is great, like I said Its what I've been looking for. As a matter of interest will please post the entire Upload / Import Project, so I can use it as a reference guide as to how I should tackle this.
yeah also another thing, I'm glad that this is VB.NET because I only code in VB
Muzi,
Thanks for the comment. Due to the sensitive nature of the project I can't upload the whole thing. Basically, it's a two step process: Upload the file and then use that file to do the import. Here's some sample code on how to use a FileUpload control to do the first part. Hope that helps.
<p><asp:FileUpload id="FileUpload1"
runat="server">
</asp:FileUpload></p>
<p><asp:Button id="UploadBtn"
Text="Upload File"
OnClick="UploadBtn_Click"
runat="server">
</asp:Button></p>
<p><asp:Label ForeColor="red" id="UploadStatusLabel"
runat="server">
</asp:Label></p>
Protected Sub UploadBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Specify the path on the server to' save the uploaded file to.
Dim savePath As String = "C:\SomeFolder"
' Before attempting to save the file, verify' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
' Get the name of the file to upload.
Dim fileName As String = Server.HtmlEncode(FileUpload1.FileName)
' Get the extension of the uploaded file.
Dim extension As String = System.IO.Path.GetExtension(fileName)
' Allow only files with .xls extensions' to be uploaded.
If (extension = ".xls") Then
' Append the name of the file to upload to the path.
savePath += "myfile.xls"
FileUpload1.SaveAs(savePath)
' Notify the user that their file was successfully uploaded.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user why their file was not uploaded.
UploadStatusLabel.Text = "Error: Only Excel files are allowed"
End If
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Thanks Jay
Your code worked perfectly. or though i struggled with it initially but at the end of the day i was able to import the data into a DB and Damn this thing is quick. I was surprised how quickly it imported the Data.
Just one thing Jay, maybe you can help: About the GetSchema from David Hayden's blog, Its in C# and I must say its not explained that well. So I was wondering if you could post a VB version of the GetSchema and explaining its process, and how one would use it? (Were would it go in the code). Thanks mate, that will be really helpful.
Later.
Muzi,
See my latest post for a great way to convert C# to VB.
As for where it'd go in the code... somewhere after the file is uploaded and saved to the server and before you hook it up to the SqlBulkCopy.
If you have control over the Excel file then you don't even really need it. GetSchema is more for when you need to peer into an Excel file and see what the workbooks are.
wow, thanks for posting this script! it worked great, and i was able to import 20,000+ records in under 3 seconds (with my slow internet connection)!
Hi sir,
Canu please send me code in c#
I would be thankful if u send me ASAP
Thanking u
ramarao
ramarao - Try www.codechanger.com for converting any VB.NET code to C#