deleting data from a file

Question

I need to delete some rows from csv file based on a column value. Here is my sample.csv file

Name     no     date        sal  
abc      12     06/06/10    2345  
xyz      11     06/06/10    2321  
qwe      10     05/07/10    4323  
asd      10     05/07/10    3221  

In my vb.net winforom application, I have to read this file and need to delete the rows related to 06/06/10 date column values then write the remaining rows into new.csv file. So far my program works on reading whole data present in the file. Any suggestion on this please?

Here is my code:

 Dim ioFile As New System.IO.StreamReader("C:\sample.csv")      
 Dim ioLine As String
 Dim ioLines As String      
 ioLine = ioFile.ReadLine     
 ioLines = "ID,Name,Number,Amount"
 ioLines &= vbCrLf & ioLine 
 While Not ioLine = ""         
    ioLine = ioFile.ReadLine         
    ioLines = ioLines & vbCrLf & ioLine      
 End While     
 Dim ioWriter As New System.IO.StreamWriter("C:\new.csv")     
 ioWriter.WriteLine(ioLines)     
 ioFile.Close()     
 ioWriter.Close()

Answer 1

This is a very naive and simplistic solution, and assumes that you are working with a very small file, that you don't care about performance, and that the .csv data itself never contains commas. But since you didn't state otherwise, let's assume that getting the job done is the goal, and this will do it:

Dim data() As String = System.IO.File.ReadAllLines("C:\Temp\sample.csv")
Dim newData = From a In data Where DateTime.Parse(a.Split(","c)(2)) <> #6/10/10#
System.IO.File.WriteAllLines("C:\Temp\new.csv", newData)

If you need to speed this up or process a larger file, wrap your file reader in an IEnumerable that yields ReadLine() calls instead of reading the whole file into memory at once with ReadAllLines(). If you're .CSV will have quoted identifiers and commas in the data, then you'll need to use a more advance .CSV reader. Microsoft built one in. See here.

-- EDIT --

Per request, here's how you could do a file line enumerator in VB. With the class the code changes to From a In New TextFileEnumerator("C:\Temp\sample.csv") Where...

''' Handy helper class for iterating over lines in a text file.  Lets you do something like a
''' Linq-to-files.
''' </summary>
Public Class TextFileEnumerator
   Implements IEnumerator(Of String), IEnumerable(Of String)

   Private _filePath As String
   Private _rdr As StreamReader
   Private _line As String

   Private ReadOnly Property StreamReader() As StreamReader
      Get
         _rdr = If(_rdr, New StreamReader(_filePath))
         Return _rdr
      End Get
   End Property

   Public Sub New(filePath As String)
      If Not System.IO.File.Exists(filePath) Then Throw New FileNotFoundException(filePath)
      _filePath = filePath
   End Sub

   Public ReadOnly Property Current As String Implements System.Collections.Generic.IEnumerator(Of String).Current
      Get
         Return _line
      End Get
   End Property

   Public ReadOnly Property Current1 As Object Implements System.Collections.IEnumerator.Current
      Get
         Return _line
      End Get
   End Property

   Public Function MoveNext() As Boolean Implements System.Collections.IEnumerator.MoveNext
      _line = Me.StreamReader.ReadLine()
      Return (_line IsNot Nothing)
   End Function

   Public Sub Reset() Implements System.Collections.IEnumerator.Reset
      If _rdr IsNot Nothing Then
         _rdr.Close()
      End If
      _rdr = Nothing
   End Sub

   Public Function GetEnumerator() As System.Collections.Generic.IEnumerator(Of String) Implements System.Collections.Generic.IEnumerable(Of String).GetEnumerator
      Return Me
   End Function

   Public Function GetEnumerator1() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
      Return Me
   End Function

#Region "IDisposable Support"
   Private disposedValue As Boolean   ' To detect redundant calls

   ' IDisposable
   Protected Overridable Sub Dispose(disposing As Boolean)
      If Not Me.disposedValue Then
         If disposing Then
            If _rdr IsNot Nothing Then
               _rdr.Close()
               _rdr.Dispose()
               _rdr = Nothing
            End If
         End If

         ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
         ' TODO: set large fields to null.
      End If
      Me.disposedValue = True
   End Sub

   ' TODO: override Finalize() only if Dispose(disposing As Boolean) above has code to free unmanaged resources.
   'Protected Overrides Sub Finalize()
   '    ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
   '    Dispose(False)
   '    MyBase.Finalize()
   'End Sub

   ' This code added by Visual Basic to correctly implement the disposable pattern.
   Public Sub Dispose() Implements IDisposable.Dispose
      ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
      Dispose(True)
      GC.SuppressFinalize(Me)
   End Sub
#End Region

End Class

Answer 2

Hopefully this will get you started (I assumed fixed column widths as that appears to be the case from your sample file):

Const dateColumnOffset As Integer = 16 ' I hope I counted that right
Const dateColumnWidth As Integer = 8
Using writer As StreamWriter = File.CreateText("new.csv")
    For Each line As String In File.ReadLines("sample.csv")
        Dim recordDate As String = line.Substring(dateColumnOffset, dateColumnWidth)
        If recordDate <> "06/06/10" Then
            writer.WriteLine(line)
        End If
    Next
End Using