Solved

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

Posted on 2013-12-04
3
771 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

822 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