Mr.J Mr.J - 1 year ago 106 Question

Dynamically edit Contents of a CSV file using

I have this code

Dim fileReader As System.IO.StreamReader
fileReader =

Dim stringReader As String
'read csv file from first to last line
While fileReader.ReadLine <> ""
'get data of line
stringReader = fileReader.ReadLine()
'check the number of commas in the line
Dim meh As String() = stringReader.Split(",")
If meh.Length > 14 Then

My.Computer.FileSystem.WriteAllText("Filepath", "asd", True)

ElseIf meh.Length < 14 Then

My.Computer.FileSystem.WriteAllText("Filepath", "asd", True)

ElseIf meh.Length = 14 Then

MsgBox("This line of the file has " & stringReader & meh.Length & "commas")

End If

End While
End Sub

To explain, the above code would check EACH line of a CSV file to check weather the contents has 14 commas('). Then if that line has more commas, the code will reduce it to 14, and if not, it would write commas so that it would be equal to 14. The above conditions are not created yet, so the code is just for testing. I read something about WriteAllText and this code gives me the error :

The process cannot access the file 'filepath' because it is being used by another process.

Which, I think, means that I cant edit the CSV file because I'm currently using its data.

My question is, how could I edit the contents of the CSV file even when I am checking its contents?

Please do disregard this code

My.Computer.FileSystem.WriteAllText("Filepath", "asd", True)

as I use this code just for testing, if ever I could manage to write it to the CSV file.

I Thank you for all your help.

Answer Source

If your CSV is not too big, you can read it in memory and work with it. When you have finish you can write it again on disk.

    'Declare 2 List (or you can work directly with one)
    Dim ListLines As New List(Of String)
    Dim ListLinesNEW As New List(Of String)

    'Read the file
    Using MyCSVread As New IO.FileStream("C:\MyCSV.csv", IO.FileMode.Open, IO.FileAccess.ReadWrite)

      'Read all the lines and put it in a list of T (string)  
      Using sReader As IO.StreamReader = New IO.StreamReader(MyCSVread)
            Do While sReader.Peek >= 0
        End Using
    End Using

    'Your code for work with the line. Here you can write the new lines in the NEW list of string or work directly in the first
    For L As Integer = 0 To ListLines.Count - 1
        Dim meh As String() = ListLines(L).Split(",")
        If meh.Length > 14 Then
            'your code ...
        ElseIf meh.Length < 14 Then
            'your code ...
        ElseIf meh.Length = 14 Then
            MessageBox.Show("The line " & (ListLines(L) + 1) & " of the file has " & meh.Length & "commas", "MyApp", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If

    'Open again the file for write
    Using MyCSVwrite As New IO.FileStream("C:\MyCSV.csv", IO.FileMode.Open, IO.FileAccess.ReadWrite)

        'Write back the file with the new lines
        Using sWriter As IO.StreamWriter = New IO.StreamWriter(MyCSVwrite)
            For Each sLine In ListLinesNEW.ToArray
        End Using
    End Using

The "Using" auto close the filestream or a streamreader/write or what you use. Then you will not have problem like "file already in use".

Hope this help.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download