excel to sql

Posted on 2012-09-10
Last Modified: 2012-09-10

Can someone please point me to some well-written asp/ source code showing how to import 2 (of several) fields from an excel spreadsheet into an sql database?

I need the user to browse to and select the file.  I then need to import all of the rows of only two column and place them into a pre-existing sql table.  

I appreciate the help!
Question by:santaspores1
    LVL 13

    Accepted Solution

    LVL 35

    Assisted Solution

    this code below will read all the data from spreadsheet into a DataTable. You can modify SELECT statement to read only two columns. Than you can import the dataTable into your database:

    Function ReadDataFromExcel(ByVal path As String) As System.Data.DataTable
            Dim ds As New DataSet
            Dim da As OleDbDataAdapter
            Dim conn As OleDbConnection = Nothing
            Dim sheet As String
                conn = New OleDbConnection( _
                     "provider=Microsoft.ACE.OLEDB.12.0; " & _
                     "data source=" & path & "; " & _
                     "Extended Properties=Excel 12.0;")
                ''get sheet name
                sheet = GetSheetName(path)
                da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "]", conn)
            Catch ex As Exception
                If conn.State = ConnectionState.Open Then
                End If
            End Try
            ReadDataFromExcel = ds.Tables(0)
        End Function
        Function GetOldSheetName(ByVal path As String) As String
        Function GetSheetName(ByVal path As String) As String
            Dim dt As New DataTable
            Dim conn As OleDbConnection
            Dim sSheetName As String = ""
            Dim strConn As String
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & path & ";" & _
                    "Extended Properties=""Excel 12.0;"""
                conn = New OleDbConnection(strConn)
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                sSheetName = dt.Rows(dt.Rows.Count - 1).Item("Table_Name")
            Catch ex As Exception
            End Try
            GetSheetName = sSheetName
        End Function

    Open in new window

    LVL 35

    Expert Comment


    "SELECT * FROM [" & sheet & "]"

    with something like this:

    "SELECT ColumnName1, ColumnName2 FROM [" & sheet & "]"

    Author Closing Comment

    Both excellent resources - thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now