Solved

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

Posted on 2013-12-04
3
750 Views
Last Modified: 2013-12-05
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
Comment
Question by:danielolorenz
  • 2
3 Comments
 

Author Comment

by:danielolorenz
ID: 39696099
In Addition, What Visual Studio 2010 .NET Libraries do I need to download for the Excel File Read?

Thanks,

Dan
0
 
LVL 9

Assisted Solution

by:dustock
dustock earned 500 total points
ID: 39696332
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
 
LVL 9

Accepted Solution

by:
dustock earned 500 total points
ID: 39696668
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

17 Experts available now in Live!

Get 1:1 Help Now