How to insert multiple sheet in one excel file into SQL server in VB

Question

0.00/5 (No votes)

See more:

I must import one excel file contain 7 sheets into sql server in vb. Right now im having problem which is sql statement just read and insert one sheet only into database. So how can I insert all 7 sheets into database using vb?

If (rblData.SelectedItem.Value = "FEV") Then

Using sSourceConnection
'Query to select all data in uploaded file
Dim sql As String = String.Format("Select * FROM [Sheet1$]")
Dim command As New OleDbCommand(sql, sSourceConnection)

sSourceConnection.Open()
'SqlDataReader reader = myCommand.ExecuteReader();
Using dr As OleDbDataReader = command.ExecuteReader()
Using bulkCopy As New SqlBulkCopy(sDestConstr)
bulkCopy.DestinationTableName = "FEV"
bulkCopy.ColumnMappings.Add("Part", "part")
bulkCopy.ColumnMappings.Add("Serial #", "serial_no")
bulkCopy.ColumnMappings.Add("Original Serial #", "original_serial_no")
bulkCopy.ColumnMappings.Add("Scrap Location", "scrap_location")
bulkCopy.ColumnMappings.Add("Scrap Reason", "scrap_reason")
bulkCopy.ColumnMappings.Add("shift", "shift")
bulkCopy.ColumnMappings.Add("Day", "day")
bulkCopy.ColumnMappings.Add("Qty Cell", "qty_cell")
bulkCopy.ColumnMappings.Add("ZDL ID", "zdl_id")

bulkCopy.WriteToServer(dr)

fillFEVInfo()

lblmsg.Text = "FEV Defect data entered successfully!!!"

End Using
End Using
End Using
End If

What I have tried:

Right now its just successfully insert one sheet.

Posted 18-Oct-17 17:02pm

Member 13473067

Updated 18-Oct-17 20:22pm

Add a Solution

Comments

Karthik_Mahalingam 19-Oct-17 1:53am

does all the sheets has the same columns names?

Member 13473067 19-Oct-17 2:00am

i rename Sheet1, Sheet2 and so on..So i want all 7 Sheets will be import into database. im using vb. Yes all sheets have same column name.

Karthik_Mahalingam 19-Oct-17 2:15am

table names?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-10-18T20:22:00

Define a dictionary with key as sheetName and Value as TableName, Add all the sheet Name - Table name items to the dictionary
Loop the items in the dictionary to get the sheet Name and the Table Name and pass it as

Dim dictMapping As Dictionary(Of String, String) = New Dictionary(Of String, String)
dictMapping.Add("Sheet1$", "TableName1")
dictMapping.Add("Sheet2$", "TableName2")
.
.

For Each item In dictMapping
    Dim sheetName As String = item.Key
    Dim tableName As String = item.Value
    Dim sql As String = ("select * from " + sheetName)
     . your code
     .
     .

    bulkCopy.DestinationTableName = tableName
Next