I'm looking to be able to move through a pipe ("|") delimited text file, controlling both the row and column I read.
I have previously achieved this in MS Access VBA and am currently starting to in VB.NET, but struggling to complete this.
In Access, VBA allowed me to link to the text file as a recordset (Dim rs As DAO.Recordset), pre-defined as delimited and move through rows (rs.MoveNext or rs.MovePrevious) whilst selecting the fields/columns I required (rs!Field1).
Is there any equivalent in VB.NET? so far I have only been able to find the following from MSDN, but don't know how to manipulate it:
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters("|")
Dim currentrow As String()
While Not MyReader.EndOfData
currentrow = MyReader.ReadFields()
Dim currentfield As String
For Each currentfield In currentrow
MsgBox(currentfield)
Next
End While
End Using
I want to be able to store certain fields to variables to be written to .CSV files later on.
For anyone whom this may help, I managed to resolve this with the help the of the two following websites:
Loop Through Files in Directory
Using these I created the following button click sub and accompanying function:
Private Sub Button_Run_Click(sender As Object, e As EventArgs) Handles Button_Run.Click
' Make a reference to a directory
Dim strDir As String = ("C:\")
Dim Dir As New System.IO.DirectoryInfo(strDir)
' Get a reference to each file in that directory
Dim fiArr As System.IO.FileInfo() = Dir.GetFiles()
Dim fri As System.IO.FileInfo
'For each file call the formatter to write to a text file
'Then delete each file after writing it
For Each fri In fiArr
Call Formatter(strDir & fri.Name)
My.Computer.FileSystem.DeleteFile(strDir & fri.Name)
Next fri
End Sub
Then I split each line by the delimiter where each field can be obtained (using the values(0) variable defined), and modified (using it's index). Controlling the line is as simple as creating a new variable using or modifying existing variable by changing the i integer. E.g:
Dim Nvalues() As String = lines(i + 1).Split("|")
Using this I created the following function:
Public Sub Formatter(ByVal FName As String)
'Standard start defining stream reader to loop
'through and pull data from text files
'FName is provided from the form button sub
Dim StrReader As New System.IO.StreamReader(FName)
Dim text As String = StrReader.ReadToEnd
StrReader.Close()
Dim lines() As String = text.Split(vbLf)
'Define all variables that may need to be stored before writing
Dim x1, x2, x3, x4, x5, x6, x7, x8, x9, x0 As String
'Define where the data will be written to (line by line)
Dim FPath As String = "C:\Sample.txt"
If Not System.IO.File.Exists(FPath) Then
System.IO.File.Create(FPath).Dispose()
End If
Dim objwriter As System.IO.StreamWriter
'Set loop to go through all data from beginning to end using i as line index
Dim i As Double
For i = 0 To lines.Length - 1
'Split each line by pipes (|) allowing "values" to be indexed to pull by column
Dim values() As String = lines(i).Split("|")
'Set 1st column in row as switch item
Select Case values(0)
'If 1st column in row is 001, store required data in variables
'specified using the index of the value to give accurate column
Case "001"
x1 = values(1)
x2 = values(2)
x3 = values(3)
x3 = values(4)
'Repeat same as 001 but for 002
Case "002"
x4 = values(1)
x5 = values(2)
'Repeat same as 001 but for 003
Case "003"
x6 = values(1)
x7 = values(2)
'Write to file including fields 1 & 3 from 004 group
Case "004"
objwriter = System.IO.File.AppendText(FPath)
objwriter.WriteLine(x1 & "," & x2 & "," & x3 & "," & x4 _
& "," & x5 & "," & x6 & "," & x7 & "," _
& Nvalues(1) & "," & Nvalues(2))
objwriter.Close()
Case Else
End Select
Application.DoEvents()
Next
End Sub