Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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 vb.net (.Net framework- 3.5)

kindly give me idea

How to import data from excel workbook into sqlserver 2008R2.
Posted 17-Aug-12 21:49pm
Comments
Sergey Alexandrovich Kryukov at 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.
--SA
Rajeev prasad yadav at 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).

And

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?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
http://en.wikipedia.org/wiki/ADO.NET[^],
http://msdn2.microsoft.com/en-us/library/aa286484.aspx[^].

Use Office Excel Interop:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.11%29.aspx[^].

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.

—SA

  Permalink  
Comments
Espen Harlinn at 18-Aug-12 12:40pm
   
5'ed!
Sergey Alexandrovich Kryukov at 18-Aug-12 12:44pm
   
Thank you, Espen.
--SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
 
<pre>
     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
        MyCommand.Fill(DtSet)
         MyConnection.Close()
        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 & "')")
        Next
        MsgBox("Successfully Saved")
 
    End If  
 
End Sub</pre>
  Permalink  
v2
Comments
Rajeev prasad yadav at 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 at 18-Aug-12 12:46pm
   
You need to use <pre lang="vb"> tags for code samples. Could you "Improve Solution" to format it properly?
--SA
hlsc1983 at 19-Nov-14 9:28am
   
i am getting an error "execute local is not declared".
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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 ...[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 395
2 Maciej Los 285
3 ProgramFOX 265
4 CHill60 200
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 395
2 Maciej Los 285
3 ProgramFOX 265
4 CHill60 200


Advertise | Privacy | Mobile
Web01 | 2.8.150331.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100