Importing Excel to SQL Database using vb.net and asp.net

Question

Im currently building a project that lets users import Excel files via a web interface (built), which saves the file to the server (built), and then imports the data into the SQL Database on the server depending on a few of the user options (not built).

Im not familiar with SQL database tools within VS at all so I have been fumbling around for the better part of two days just trying to get everything set up. Im pretty sure I need to use BulkCopy, but Im not quite sure how to use it and I can't seem to find specific examples that explain it pertaining to my specific application.

So in my App_Data folder I have an .mdf title "Device Database." In that database I have three tables: "Galaxy Nexus", "Hercules" , and "Ruby"

I am trying to import four cells from each imported excel sheet to their respective tables.

I would like to import cell(2,2) to column1 in the table, cell(2,3) to column2, cell(3,2) to column3 and cell(1,1) to column4.

The code I am trying to accomplish this with is:

    Dim ExcelContentType As String = "application/vnd.ms-excel"
    Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", SavedFile)
    Using connection As New OleDbConnection(excelConnectionString)
        Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)
        connection.Open()
        Using reader As DbDataReader = Command.ExecuteReader()
            Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"
            Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
                bulkCopy.DestinationTableName = DropDown1.SelectedItem.ToString
                bulkCopy.WriteToServer(reader)
            End Using
        End Using
    End Using

Where I am having trouble is, I do not know how to select certain cells from the excel sheet to import and I do not know how to copy those cells to specific columns in the specified table.

Any and all help is always appreciated. Thanks, Zach

I use the Excel COM libraries. Then you can just write insert and/or update queries (or use stored procedures). msdn.microsoft.com/en-us/library/wss56bz7%28v=vs.100%29.aspx — Marnee KG7SIO
– Marnee KG7SIO, Commented Sep 11, 2012 at 22:29
I use the COM libraries too when I am automating excel scripts or transferring excel to excel. Can you work with SQL Databases like excel? Can I go NexusGaxaxyTable.column1(or whatever) = XLSheet1.Cells(2,2).Value.ToString, once both are open? — Nefariis
– Nefariis, Commented Sep 11, 2012 at 22:40
I don't think so. I usually just craft an insert or update query. You would have to work with t-sql. For example, get the value in the cell and use that to update the table: msdn.microsoft.com/en-us/library/ms177523.aspx — Marnee KG7SIO
– Marnee KG7SIO, Commented Sep 11, 2012 at 22:56
That would be really helpful. Could you post a few comments on it too so I can translate it to VB.net? — Nefariis
– Nefariis, Commented Sep 13, 2012 at 22:31

Nefariis · Accepted Answer · 2012-09-18 23:01:02Z

Im posting an answer so that if anyone else stumbles upon this, they might be helped as well.

This is what got everything to work for me. (Shout out to kevin)

Protected Sub Button1_Click(sender As Object, e As System.EventArgs)
    Dim appPath As String = Request.PhysicalApplicationPath
        Dim con As New System.Data.SqlClient.SqlConnection
        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & appPath & "App_Data\Devicedatabase.MDF;Integrated Security=True;User Instance=True;"
        con.Open()
        MsgBox("open")
        con.Close()
        MsgBox("close")
    End Sub

This got the connection open after much trying and frustration.

This got the excel values imported to the database:

 Using con As New SqlClient.SqlConnection With
{
    .ConnectionString =
    "Data Source=.\SQLEXPRESS;AttachDbFilename=" & appPath & "App_Data\Devicedatabase.MDF;Integrated Security=True;User Instance=True;"
}
Using cmd As New SqlClient.SqlCommand With
    {
        .Connection = con,
        .CommandText = "INSERT INTO " & """" & DropDownList1.SelectedItem.ToString & """" & "ColumnName1, ColumnName2)VALUES (@Col1,@Col2)"
    }
    cmd.Parameters.Add(New SqlClient.SqlParameter With {.DbType = DbType.String, .ParameterName = "@Col1"})
    cmd.Parameters.Add(New SqlClient.SqlParameter With {.DbType = DbType.String, .ParameterName = "@Col2"})
    cmd.Parameters(0).Value = "Value obtained from Excel"
    cmd.Parameters(1).Value = "Value obtained from Excel"
    con.Open()
    Dim Result As Integer = cmd.ExecuteNonQuery
    If Result <> 1 Then
        MessageBox.Show("Insert failed.")
    Else
        MessageBox.Show("Row inserted.")
    End If

End Using
End Using

Enjoy guys!

Murali Murugesan · Accepted Answer · 2012-09-12 09:15:35Z

1

Use Excel Data Reader dll for this. It will read the excel file and give the Dataset as result.

answered Sep 12, 2012 at 9:15

Murali Murugesan

22.6k18 gold badges76 silver badges123 bronze badges

4 Comments

Murali Murugesan Over a year ago

ExcelDataReader will give a set of DataTables inside Dataset. There are nothing but each work sheet. Definitely good stuff !!

Nefariis Over a year ago

Im kinda confused about this. I see that it can read the data from an Excel sheet and export it into a data set fairly easily, but how do you put that dataset into a SQL table? More specifically, how would you take a few cells out of excel and put them into specific columns in the SQL table?

Murali Murugesan Over a year ago

That you have to write a code for filtering the columns from DataSet and then code for Database update. The component will give only DataSet.

Nefariis Over a year ago

Im sure I can figure out the dataset part, would you happen to know the code to add values to a particular column in a specific table? I'm struggling to find a way to open a database and add information to it.

The dude · Accepted Answer · 2013-08-09 11:46:30Z

simple code for 'abcConnectionString' as a connection string and 'pqr_table' as sql table. 'Sheet1' is for the sheet1 of excel file. important thing is the table format ie rows n columns of excel n database file should b same

her is the vb.net code foe veb application one FileUpload with name 'FileUpload1' and one button. this code is in side the buton method

Dim excelConnectionString As String = String.Empty
Dim uploadPath As String = "~/Uploads/"
Dim filePath As String = Server.MapPath(uploadPath + FileUpload1.PostedFile.FileName)
Dim fileExt As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim strConnection As [String] = ConfigurationManager.ConnectionStrings("abcConnectionString").ConnectionString
If fileExt = ".xls" OrElse fileExt = "XLS" Then
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & filePath & "'" & "; Extended Properties ='Excel 8.0;HDR=Yes'"
ElseIf fileExt = ".xlsx" OrElse fileExt = "XLSX" Then
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=Excel 12.0;Persist Security Info=False"
End If
Dim excelConnection As New OleDbConnection(excelConnectionString)
Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
excelConnection.Open()
Dim dReader As OleDbDataReader
dReader = cmd.ExecuteReader()
Dim sqlBulk As New SqlBulkCopy(strConnection)
sqlBulk.DestinationTableName = "pqr_table"
sqlBulk.WriteToServer(dReader)
MsgBox("Congratulations! Successfully Imported.")
        excelConnection.Close()

kleopatra · Accepted Answer · 2013-08-09 11:53:35Z

simple code for 'abcConnectionString' as a connection string and 'pqr_table' as sql table. 'Sheet1' is for the sheet1 of excel file. important thing is the table format ie rows n columns of excel n database file should b same

here is the vb.net code foe veb application one FileUpload with name 'FileUpload1' and one button. this code is in side the buton method

Dim excelConnectionString As String = String.Empty     
Dim uploadPath As String = "~/Uploads/" 
Dim filePath As String = Server.MapPath(uploadPath + FileUpload1.PostedFile.FileName)

Dim fileExt As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

Dim strConnection As [String] = ConfigurationManager.ConnectionStrings("abcConnectionString").ConnectionString

If fileExt = ".xls" OrElse fileExt = "XLS" Then
    excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & filePath & "'" & "; Extended Properties ='Excel 8.0;HDR=Yes'"
ElseIf fileExt = ".xlsx" OrElse fileExt = "XLSX" Then
    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=Excel 12.0;Persist Security Info=False"
End If
Dim excelConnection As New OleDbConnection(excelConnectionString)
Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
excelConnection.Open()
Dim dReader As OleDbDataReader
dReader = cmd.ExecuteReader()
Dim sqlBulk As New SqlBulkCopy(strConnection)
sqlBulk.DestinationTableName = "pqr_table"
sqlBulk.WriteToServer(dReader)
MsgBox("Congratulations! Successfully Imported.")
excelConnection.Close()

Collectives™ on Stack Overflow

Importing Excel to SQL Database using vb.net and asp.net

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related