Click here to Skip to main content
12,700,838 members (30,836 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB SQL-Server SQL-Server-2008 , +
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
   
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 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 18-Aug-12 12:40pm
   
5'ed!
Sergey Alexandrovich Kryukov 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 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?
--SA
hlsc1983 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2017
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