Solved ** - How to read a Line in a CSV File twice - Please help

[NJDevils28]

Hello All,

I am having a bit of difficulty processing a CSV File.

My goal is to read the file and parse the separate fields (per record) into an array. If any of the fields are invalid, I need to write an error log with the record exactly as I read it, quotes/commas and all.

I am able to read the record into the array and process it but my error checking routine is giving me trouble. What I need to do is re-read the current record and write it to a file.

I do not know how to accomplish this. Here is how I am reading the file. If you could, would you point me in the right direction?


'Read the first record as the header Then write it to the Error File Then close the file

        Dim HeaderRow As String = myReader.ReadLine
        Dim OutputFile As String = ART2CSDfrm.OutputFile
        Dim myStreamWriter As New StreamWriter(OutputFile)
        myStreamWriter.WriteLine(HeaderRow)
        myStreamWriter.Close()
        Dim ErrorRow As String
        ' Create my Array
        Dim currentRow As String()

        While Not myReader.EndOfData
            Try
                'ErrorRow = myReader.ReadLine
                currentRow = myReader.ReadFields()

                Requestnum = currentRow(0)
                type = currentRow(1)
                ProgramName = currentRow(2)
                region = currentRow(3)
                ClientName = currentRow(4)
                ClientPhase = currentRow(5)
                ProgramType = currentRow(6)
                Admin = currentRow(7)
                Status = currentRow(8)
                Requestor = currentRow(9)
                RequestorEmail = currentRow(10)
                RequestorPhone = currentRow(11)
                dateSubmitted = currentRow(12)
                Priority = currentRow(13)
                CaseDesc = currentRow(14)
                EstimateLevel = currentRow(15)
                EstimateHours = currentRow(16)
                EstimatedBy = currentRow(17)

          End Try
      End While

[Joacim Andersson]

I'm guessing that your myReader is an instance of the TextFieldParser class, if so you can use the PeekChars method to read a number of chars without advancing the cursor. If the number of chars you want to read exceeds the number of characters for the current line, only the characters of the current line is returned. So the trick is to pass a high enough number to the PeekChars method so that you know that all the characters of the current line is returned.

While Not myReader.EndOfData
  Try
    ErrorRow = myReader.PeekChars(8192)
    '... rest of the code

I just picked the number 8192 here, since I suspect that no line in your CSV would be larger than 8KB, if it is then just increase this number.

[dday9]

I currently have a function that I wrote a while back that converts a text file to a data table. Take a look in my signature, under contributions. That won't give you error log option that you're desiring, but it is a start.

Edit - I'm thinking of textfile with a comma delimiter. If it's an excel file that you saved as a .CSV then disregard this post :P

[dbasnett]

It is not clear why you need to re-read? Why not just make a copy and use that.

[Españolita]

Why not just

                ErrorRow = myReader.ReadLine
                currentRow = Split(ErrorRow,",")

Then if you need to put ErrorRow in your log, you can, if not, it is overwritten with the next line, ready to be written to the log if necessary.

[NJDevils28]

I'm guessing that your myReader is an instance of the TextFieldParser class, if so you can use the PeekChars method to read a number of chars without advancing the cursor. If the number of chars you want to read exceeds the number of characters for the current line, only the characters of the current line is returned. So the trick is to pass a high enough number to the PeekChars method so that you know that all the characters of the current line is returned.

While Not myReader.EndOfData
  Try
    ErrorRow = myReader.PeekChars(8192)

    '... rest of the code

I just picked the number 8192 here, since I suspect that no line in your CSV would be larger than 8KB, if it is then just increase this number.

Awesome!! That Did it!

Thanks You very much!

-NJ

[NJDevils28]

I'm guessing that your myReader is an instance of the TextFieldParser class, if so you can use the PeekChars method to read a number of chars without advancing the cursor. If the number of chars you want to read exceeds the number of characters for the current line, only the characters of the current line is returned. So the trick is to pass a high enough number to the PeekChars method so that you know that all the characters of the current line is returned.

While Not myReader.EndOfData
  Try
    ErrorRow = myReader.PeekChars(8192)
    '... rest of the code

I just picked the number 8192 here, since I suspect that no line in your CSV would be larger than 8KB, if it is then just increase this number.

That did it!
Thank you very much!

-NJ

[Joacim Andersson]

Why not just

                ErrorRow = myReader.ReadLine
                currentRow = Split(ErrorRow,",")

Then if you need to put ErrorRow in your log, you can, if not, it is overwritten with the next line, ready to be written to the log if necessary.

That will not work if some of the data in the CSV contains a comma. Normally you would quote such data, take this line as an example:

C197642, "Smith, John", Carbon, 27

That will be parsed to these fields:

C197642
Smith, John
Carbon
27

Notice that the comma in the second field was not parsed, however the quotes was removed. The OP wants a copy of the original line for his error log so he can't just join these values either since the quotes are now missing.

Another reason is that some CSV files allow comments in the file which should not be parsed and empty lines should also normally be ignored. This is all handled by the TextFieldParser for you.

[techgnome]

But wouldn't the original "copy" be in ErrorRow? so if the data in CurrentRow fails for what ever reason, the original read in data would still be intact in ErrorRow.

-tg

[Joacim Andersson]

Yes, if you use the code I showed. The ReadLine that he had commented out will read the whole line and move the cursor to the next line so he never get to parse the line.