How do I import and read data from Excel file?

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
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:

<pre lang="x-vbnet 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:UsersDataAcquisition.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
[/code]
<br/>
<br/>

<br/>
Thanks for your help!

<
Auburn University Student IT/MIS Intern War Eagle!<br/>
<br/>

View the full article
 
Back
Top