Read only particular fields from CSV File in vb.net

Question

Hi,

I have this code to read a CVS file. It reads each line, devides each line by delimiter ',' and stored the field values in array 'strline()' .

How do I extract only required fields from the CSV file?

For example if I have a CSV File like

Type,Group,No,Sequence No,Row No,Date (newline) 0,Admin,3,345678,1,26052010 (newline) 1,Staff,5,78654,3,26052010

I Need only the value of columns Group,Sequence No and date.

Thanks in advance for any ideas.

Dim myStream As StreamReader = Nothing
    ' Hold the Parsed Data
    Dim strlines() As String
    Dim strline() As String
    Try
      myStream = File.OpenText(OpenFile.FileName)
      If (myStream IsNot Nothing) Then
        ' Hold the amount of lines already read in a 'counter-variable' 
        Dim placeholder As Integer = 0
        strlines = myStream.ReadToEnd().Split(Environment.NewLine)
        Do While strlines.Length <> -1 ' Is -1 when no data exists on the next line of the CSV file
          strline = strlines(placeholder).Split(",")
          placeholder += 1
        Loop
      End If
    Catch ex As Exception
      LogErrorException(ex)

    Finally

      If (myStream IsNot Nothing) Then
        myStream.Close()
      End If
    End Try

Answer 1

1) DO NOT USE String.Split!!

CSV data can contain comma's, e.g.

id,name
1,foo
2,"hans, bar"

Also as above you would need to handle quoted fields etc... See CSV Info for more details.

2) Check out TextFieldParser - it hadles all this sort of thing.

It will handle the myriad of different escapes you can't do with string.split...

Sample from: http://msdn.microsoft.com/en-us/library/cakac7e6.aspx

Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TestFolder\test.txt")
    MyReader.TextFieldType = FileIO.FieldType.Delimited
    MyReader.SetDelimiters(",")
    Dim currentRow As String()
    While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields()
            Dim currentField As String
            For Each currentField In currentRow
            MsgBox(currentField)
            Next
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
        End Try
    End While
End Using

The MyReader.ReadFields() part will get you an array of strings, from there you'll need to use the index etc...

PK :-)

Answer 2

Maybe instead of only importing selected fields, you should import everything, then only use the ones you need.