none
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
            TextFileReader.SetDelimiters(",")
    
            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
            TextFileTable.Columns.Add(Column0)
    
            Column1 = New DataColumn()
            Column1.DataType = System.Type.GetType("System.String")
            Column1.ColumnName = "Cylinder Tester 1"
            Column1.Caption = "Column"
            Column1.ReadOnly = True
            Column1.Unique = False
            TextFileTable.Columns.Add(Column1)
    
            Column2 = New DataColumn()
            Column2.DataType = System.Type.GetType("System.String")
            Column2.ColumnName = "Cylinder Tester 2"
            Column2.Caption = "Column"
            Column2.ReadOnly = True
            Column2.Unique = False
            TextFileTable.Columns.Add(Column2)
    
            Column3 = New DataColumn()
            Column3.DataType = System.Type.GetType("System.String")
            Column3.ColumnName = "Cylinder Tester 3"
            Column3.Caption = "Column"
            Column3.ReadOnly = True
            Column3.Unique = False
            TextFileTable.Columns.Add(Column3)
    
            Column4 = New DataColumn()
            Column4.DataType = System.Type.GetType("System.String")
            Column4.ColumnName = "Pump Tester 1"
            Column4.Caption = "Column"
            Column4.ReadOnly = True
            Column4.Unique = False
            TextFileTable.Columns.Add(Column4)
    
            Column5 = New DataColumn()
            Column5.DataType = System.Type.GetType("System.String")
            Column5.ColumnName = "Pump Tester 2"
            Column5.Caption = "Column"
            Column5.ReadOnly = True
            Column5.Unique = False
            TextFileTable.Columns.Add(Column5)
    
            Column6 = New DataColumn()
            Column6.DataType = System.Type.GetType("System.String")
            Column6.ColumnName = "Pump Tester 3"
            Column6.Caption = "Column"
            Column6.ReadOnly = True
            Column6.Unique = False
            TextFileTable.Columns.Add(Column6)
    
            Column7 = New DataColumn()
            Column7.DataType = System.Type.GetType("System.String")
            Column7.ColumnName = "Pump Tester 4"
            Column7.Caption = "Column"
            Column7.ReadOnly = True
            Column7.Unique = False
            TextFileTable.Columns.Add(Column7)
    
            Dim CurrentRow As String()
    
            While Not TextFileReader.EndOfData
    
                Try
    
                    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
    
                    TextFileTable.Rows.Add(Row)
    
                Catch ex As  _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
    
            End While
    
            TextFileReader.Dispose()
    
            DataGridView1.DataSource = TextFileTable
        End Sub
    



    Thanks for your help!

     


    Auburn University Student IT/MIS Intern War Eagle!

    Friday, September 23, 2011 7:45 PM

Answers

All replies