VB.net conversion RRS feed

  • 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.

    Sunday, March 3, 2019 2:03 AM

All replies

  • 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

    Sunday, March 3, 2019 11:57 AM
  • 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

    Sunday, March 3, 2019 7:51 PM
  • 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.


    • Edited by Imb-hb Sunday, March 3, 2019 8:53 PM
    Sunday, March 3, 2019 8:52 PM