Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

Using Visual Basic How Do You Read an Excel File Line By Line for Parsing?

Using VB .NET, how do you import an Excel file for Reading Line By Line?  I am parsing an Excel file and then inserting each data row into a DataBase table line by line using a SQL Stored Procedure.

Thanks,

Dan
0
danielolorenz
Asked:
danielolorenz
  • 2
2 Solutions
 
danielolorenzAuthor Commented:
In Addition, What Visual Studio 2010 .NET Libraries do I need to download for the Excel File Read?

Thanks,

Dan
0
 
dustockCommented:
I have used this function in the past to read data from an xlsx sheet, but it just does a select * and it returns a dataset.  Then you could loop through the dataset and import your records.  Hope this helps.

 Private Sub btnReadExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnReadExcel.Click
        Dim filePath As String
        Dim dsData As DataSet

        filePath = txtFilePath.Text
        dsData = GetDataFromExcel(filePath)
    End Sub


    Public Function GetDataFromExcel(ByVal a_sFilepath As String) As DataSet
        Dim ds As New DataSet()
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
        Try
            cn.Open()
        Catch ex As OleDbException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        Dim dt As New System.Data.DataTable()
        dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then
            For sheet_count As Integer = 0 To dt.Rows.Count - 1
                Try
                    Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()
                    Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
                    da.Fill(ds, sheetname)
                Catch ex As DataException
                    Console.WriteLine(ex.Message)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            Next
        End If
        cn.Close()
        Return ds
    End Function

Open in new window

0
 
dustockCommented:
Sorry forgot to mention, add the following imports to your file for the code I posted above to work.

Imports System.IO
Imports System.Data.OleDb

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now