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 20: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
 
         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
  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
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
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 490
1 Sergey Alexandrovich Kryukov 316
2 Shai Vashdi 314
3 OriginalGriff 190
4 praveen_07 115
0 Sergey Alexandrovich Kryukov 9,185
1 OriginalGriff 5,315
2 Peter Leow 4,040
3 Maciej Los 3,535
4 Abhinav S 3,308


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid