excel to sql

Hi,

Can someone please point me to some well-written asp/vb.net 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!
santaspores1Asked:
Who is Participating?
 
LIONKINGCommented:
0
 
YZlatCommented:
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
        Try
            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)

            conn.Open()

            da.Fill(ds)
           
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            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
        Try
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & path & ";" & _
                "Extended Properties=""Excel 12.0;"""

            conn = New OleDbConnection(strConn)
            conn.Open()

            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            sSheetName = dt.Rows(dt.Rows.Count - 1).Item("Table_Name")

        Catch ex As Exception
            Console.Write(ex.Message)
        End Try
        GetSheetName = sSheetName
    End Function

Open in new window

0
 
YZlatCommented:
replace

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

with something like this:

"SELECT ColumnName1, ColumnName2 FROM [" & sheet & "]"
0
 
santaspores1Author Commented:
Both excellent resources - thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.