VB.net conversion

Question

Public Class Form1

    Dim Lines As List(Of FileLine) = New List(Of FileLine)
    Dim Incidents As List(Of Incident) = New List(Of Incident)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim InputFile As String = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "sample.txt")

        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(InputFile)

            MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            MyReader.Delimiters = New String() {","c}
            Dim currentRow As String()
            'Loop through all of the fields in the file.  
            'If any lines are corrupt, report an error and continue parsing.  
            While Not MyReader.EndOfData
                Try
                    currentRow = MyReader.ReadFields()
                    Dim thisLine As New FileLine
                    thisLine.LineDate = CDate(currentRow(0))
                    thisLine.LineTime = CDate(currentRow(1))
                    thisLine.N1 = CInt(currentRow(2))
                    thisLine.N2 = CInt(currentRow(3))
                    Lines.Add(thisLine)
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & " is invalid.  Skipping")
                End Try
            End While
        End Using

        Dim FirstFlag As Boolean = True

        For Each thisLine As FileLine In Lines
            Dim thisEvent As Incident
            If FirstFlag Then
                If thisLine.N2 = 0 Then
                    thisEvent = New Incident
                    thisEvent.EventDate = thisLine.LineDate
                    thisEvent.StartTime = thisLine.LineTime
                    FirstFlag = False
                End If
            Else
                If thisLine.N2 <> 0 Then
                    thisEvent.EndTime = thisLine.LineTime
                    Incidents.Add(thisEvent)
                    FirstFlag = True
                End If
            End If
        Next
    End Sub
End Class


Public Class FileLine
    Public LineDate As Date
    Public LineTime As DateTime
    Public N1 As Integer
    Public N2 As Integer
End Class

Public Class Incident
    Public EventDate As Date
    Public StartTime As DateTime
    Public EndTime As DateTime
End Class

Hi, I have this code that works fine in VB.NET but am now looking at using a similar code in Access VBA.

Is this possible to do in VBA?

It is to read a CSV file and sort through the data to find only specific lines.

Thanks always people.

Answer 1

There are different ways you could handle this.

Read the entire file into memory (http://www.devhut.net/2012/05/14/vba-read-file-into-memory/) then parse it using Split() and further parse it by parsing individual field using Split() again.

Import (Link) to the CSV directly then you could simply loop through the content link any other table.

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

As Daniel says, different ways.

I would import the file into a scratch table, and work over it w/ queries.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 3

It is to read a CSV file and sort through the data to find only specific lines.

For any import I use a generalized function that reads an input file line by line.

Each line is split with the Split function, using the appropriate separator. From then on you can make your own logic depending on whatever criterion on the different array elements.

Imb.