How do I import and read data from Excel file? RRS feed

  • Question

  • Hey guys,

    I am trying to import and read data in from an Excel file that is being updated every half second by way of a DDE Link from RSLinx Gateway,which I think is a default setting, though that data only changes every few minutes.  There are 5 rows and 8 columns of data that I am reading in.

    Each column is a different tester starting with the second row, Testers 1 through 7.  The first column has titles that describe the data coming in from the DDE links.

    Each column has 5 rows of data related to the tester.

    I know how to display the data which I will post my code for below, but as far as reading the data and determining whether or not the part was good or bad, based on a boolean value, I am stuck.

    I would also like to save the data into a different excel file after determining the status of the part so that we can look at past data.

    Let me know if you need more details.

    Code display:

    Imports System.IO
    Imports System.Data.OleDb
    Imports System.Drawing.Printing
    Public Class DataForm
        Private Sub DataForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
        End Sub
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.DataGridView1.DefaultCellStyle.Font = New Font("Tahoma", 9)
            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\DataAcquisition.csv")
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            Dim TextFileTable As DataTable = New DataTable("TextFileTable")
            ' Declare variables for DataColumn and DataRow objects.
            Dim Column0 As DataColumn
            Dim Column1 As DataColumn
            Dim Column2 As DataColumn
            Dim Column3 As DataColumn
            Dim Column4 As DataColumn
            Dim Column5 As DataColumn
            Dim Column6 As DataColumn
            Dim Column7 As DataColumn
            Dim Row As DataRow
            ' Create DataTable with 8 text columns
            Column0 = New DataColumn()
            Column0.DataType = System.Type.GetType("System.String")
            Column0.ColumnName = "Data Fields"
            Column0.Caption = "Column"
            Column0.ReadOnly = True
            Column0.Unique = False
            Column1 = New DataColumn()
            Column1.DataType = System.Type.GetType("System.String")
            Column1.ColumnName = "Cylinder Tester 1"
            Column1.Caption = "Column"
            Column1.ReadOnly = True
            Column1.Unique = False
            Column2 = New DataColumn()
            Column2.DataType = System.Type.GetType("System.String")
            Column2.ColumnName = "Cylinder Tester 2"
            Column2.Caption = "Column"
            Column2.ReadOnly = True
            Column2.Unique = False
            Column3 = New DataColumn()
            Column3.DataType = System.Type.GetType("System.String")
            Column3.ColumnName = "Cylinder Tester 3"
            Column3.Caption = "Column"
            Column3.ReadOnly = True
            Column3.Unique = False
            Column4 = New DataColumn()
            Column4.DataType = System.Type.GetType("System.String")
            Column4.ColumnName = "Pump Tester 1"
            Column4.Caption = "Column"
            Column4.ReadOnly = True
            Column4.Unique = False
            Column5 = New DataColumn()
            Column5.DataType = System.Type.GetType("System.String")
            Column5.ColumnName = "Pump Tester 2"
            Column5.Caption = "Column"
            Column5.ReadOnly = True
            Column5.Unique = False
            Column6 = New DataColumn()
            Column6.DataType = System.Type.GetType("System.String")
            Column6.ColumnName = "Pump Tester 3"
            Column6.Caption = "Column"
            Column6.ReadOnly = True
            Column6.Unique = False
            Column7 = New DataColumn()
            Column7.DataType = System.Type.GetType("System.String")
            Column7.ColumnName = "Pump Tester 4"
            Column7.Caption = "Column"
            Column7.ReadOnly = True
            Column7.Unique = False
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                    CurrentRow = TextFileReader.ReadFields()
                    Row = TextFileTable.NewRow
                    Row("Cylinder Tester 1") = CurrentRow(0).ToString
                    Row("Cylinder Tester 2") = CurrentRow(1).ToString
                    Row("Cylinder Tester 3") = CurrentRow(2).ToString
                    Row("Pump Tester 1") = CurrentRow(3).ToString
                    Row("Pump Tester 2") = CurrentRow(4).ToString
                    Row("Pump Tester 3") = CurrentRow(5).ToString
                    Row("Pump Tester 4") = CurrentRow(6).ToString
                Catch ex As  _
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
            End While
            DataGridView1.DataSource = TextFileTable
        End Sub

    Thanks for your help!


    Auburn University Student IT/MIS Intern War Eagle!

    Friday, September 23, 2011 7:45 PM


All replies