Click here to Skip to main content
15,920,503 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Dear Sir/Madam,

kindly help me.....

we have a excel workbook which contain five worksheets and we have to import data from all worksheets into MS-SqlServer 2008R2 in (.Net framework- 3.5)

kindly give me idea

How to import data from excel workbook into sqlserver 2008R2.
Sergey Alexandrovich Kryukov 18-Aug-12 3:00am    
Not a question. What exactly do you miss? ADO.NET? Office Excel interop? Just read on both. If this is not enough, ask more specific question. Don't forget that we don't know your data schema, requirements, etc. You need to develop and provide mapping rules, as the part of requirements.
Rajeev prasad yadav 18-Aug-12 3:21am    
Right now my data is importing but if i'm writing sql query like- Qstring= (select * from [Sheet1$], con)
then it upload just from sheet1 (Sheet1 is a name of worksheet).


we have five worksheets in workbook1 namely Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5.
we have to import data from all sheets in to sql server 2008R2
Also we have five tables corresponding to each one sheets to hold the data.

how we can do it?

Please see my comment to the question and learn the fields required for this.

This is a good introductory CodeProject article for ADO.NET:
Using ADO.NET for beginners[^].

For further detail, please start with:[^],[^].

Use Office Excel Interop:[^].

Basically, add a reference to the project from the tab ".NET" of the "Add reference" window, as you will have the assembly in your GAC. Choose "Office Excel Interop" assembly of appropriate version. Use it.

Share this answer
Espen Harlinn 18-Aug-12 12:40pm    
Sergey Alexandrovich Kryukov 18-Aug-12 12:44pm    
Thank you, Espen.
Try This code.Where Test.Xlsx Is excel sheet and Excel is DataBase Table

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

         Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        Dim fBrowse As New OpenFileDialog
        With fBrowse
            .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Import data from Excel file"
        End With
        If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim fname As String
            fname = fBrowse.FileName
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Test")
            DtSet = New System.Data.DataSet
            For Each Drr As DataRow In DtSet.Tables(0).Rows
                Execute_Local("INSERT INTO Excel(Name, Designation, Salary) VALUES ('" & Drr(0).ToString & "','" & Drr(1).ToString & "','" & Drr(2).ToString & "')")
            MsgBox("Successfully Saved")

        End If  

    End Sub
Share this answer
Rajeev prasad yadav 18-Aug-12 7:55am    
@Priyanka Jain:

A lot of thanks to you m/s Jain

Your code is so helpful and work successfully.
Sergey Alexandrovich Kryukov 18-Aug-12 12:46pm    
You need to use <pre lang="vb"> tags for code samples. Could you "Improve Solution" to format it properly?
hlsc1983 19-Nov-14 9:28am    
i am getting an error "execute local is not declared".
Here is a similar question, but it uses C#, you may get something useful here, good luck.
save data from excel sheet to sql database ...[^]
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900