Contents Guide Contents

Home Page DigitalDan Home Page

---

There are two approaches, the first opens the file with "Dim" and the second involves "Using" You can use either of these approaches in most applications. These examples involve the "Using" approach.
Readers who prefer the "Dim" approach can convert the examples as follows...
Replace the first "Using" with "Dim"
Replace the "End Using" with the approrioate file ".Close" and teh file ".Dispose"
 
These examples can be modified to work with other delimiters. E.g. for a .TSV (Tab Separated) you will need to replace every reference to "," with Chr(9) (Chr(9) is teh Tab character)
 
Private Shared Function ReadCsv(filename As String) As List(Of String())
Dim ret As New List(Of String()) : ret.Clear()
Try
Using tfp As New FileIO.TextFieldParser(filename) With {.Delimiters = New String() {","}, .TextFieldType = FileIO.FieldType.Delimited}
While Not tfp.EndOfData
ret.Add(tfp.ReadFields())
End While
End Using
Catch ex As Exception
' handle the file error here
End Try
Return ret
End Function

 
VB.net provides the file .TextFieldParser which automatically splits CSV records into fields as each record is read. I normally agree with most other guides - whenever possible, use the .TextFirldParser to split the records because many CSV files contain hidden challenges for anyone writing their own parser. e.g. A valid CSV file can contain fields with embedded vbCrLf characters. If you try to read records with StreamReader, the presence of vbCrLf couldsread one record across deverl lines!
 
The following code will split a string containing 1 complete CSV record into a List(of String).
 
Private Shared Function SplitCsv(record As String) As List(Of String)
Dim ret As New List(Of String) : ret.Clear()
Dim start As Integer = 1
Dim insideQuotes As Boolean = False
Dim c As Char
Dim field As String
For i As Integer = 1 To record.Length
c = CChar(Mid(record, i, 1))
If c = """"c Then insideQuotes = Not insideQuotes
If (c = ","c) AndAlso Not insideQuotes Then
ret.Add(Mid(record, start, i - start))
start = i + 1
End If
Next
If start <= record.Length Then
ret.Add(Mid(record, start))
End If
For i As Integer = 0 To ret.Count - 1
field = ret.Item(i).Trim
If Mid(field, 1, 1) = """" AndAlso Mid(field, field.Length, 1) = """" Then field = Mid(field, 2, field.Length - 2)
field = field.Replace("""""", """")
ret.Item(i) = field
Next
Return ret
End Function

 
When copying this example into your program, you will also need to copy the next example - Build_CSV_Line. (The next example on this page.) I did not want to type all the code twice, hence, this function calls the next function.
 
Private Shared Function WriteCsv(filename As String, records As List(Of String())) As String
Dim errorMessage As String = ""
Dim regex1 As New System.Text.RegularExpressions.Regex("^x20-x7f")
Try
Using sw As New IO.StreamWriter(filename, False)
For Each record As String() In records
sw.WriteLine(Build_CSV_line(record))
Next
End Using
Catch ex As Exception
errorMessage = ex.Message
End Try
Return errorMessage
End Function

 
Private Shared Function Build_CSV_line(parts() As String) As String
Dim field As String
Dim needsQuotes As Boolean
Dim regex1 As New System.Text.RegularExpressions.Regex("^x20-x7f")
For i As Integer = 0 To parts.Length - 1
needsQuotes = False
field = parts(i)
If field.Contains(""""c) Then field = field.Replace("""", """""") : needsQuotes = True
If field.Contains(","c) OrElse field.Contains("'"c) Then needsQuotes = True
If field <> field.Trim Then needsQuotes = True
If regex1.IsMatch(field) Then needsQuotes = True
If needsQuotes Then parts(i) = """" & field & """"
Next
Return String.Join(","c, parts)
End Function

 
The above function will create records suitable for a CSV file and the only fields to be surrounded by quotes will be those that need to be surrounded. (Fields that contain non-Ascii, quote or comma.) This is the standard approach for CSV files. However, the CSV format should tolerate every field being surrounded by quotes. If program speed is more important than optimised CSV formatting, you may get away with this.
 
Private Shared Function Build_CSV_line1(parts() As String) As String
For i As Integer = 0 To parts.Length - 1
parts(i) = """" & parts(i).Replace("""", """""") & """"
Next
Return String.Join(","c, parts)
End Function


---