How to read CSV file with cell that has multiple lines using C#

Question

I am trying to read CSV file that has cell with multiple rows inside it.

This is how the CSV looks like:

row 1, column 'Detail' has multiple lines.

When I am trying to read it using ReadLine() method:

private void buttonBrowse_Click(object sender, EventArgs e)
        {
            openFileDialog.Filter = "Excel Worksheets|*.csv";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                String filePathAndName = openFileDialog.FileName;
                StreamReader reader = new StreamReader(filePathAndName);
                String line = reader.ReadLine();
                Console.WriteLine(line);
                do
                {
                     line = reader.ReadLine();
                     Console.WriteLine(line);
                } while (line != null);
            }
        }

it splits the cell with the multiple rows to number of rows:

[1]"Time of Day","Process Name","PID","Operation","Path","Result","Detail","Image Path"
[2]"22:52:24.2905182","notepad.exe","4828","Process Start","","SUCCESS","Parent PID: 2484, Command line: ""C:\Windows\system32\notepad.exe"" , Current directory: C:\Users\User\, Environment: 
[3];    =::=::\
[4];    ALLUSERSPROFILE=C:\ProgramData
[5];    APPDATA=C:\Users\User\AppData\Roaming
[6];    asl.log=Destination=file
[7];    CommonProgramFiles=C:\Program Files\Common Files
...

"22:52:24.2905201","notepad.exe","4828","Thread Create","","SUCCESS","Thread ID: 8008","C:\Windows\system32\notepad.exe"
"22:52:24.2915842","notepad.exe","4828","Load Image","C:\Windows\System32\notepad.exe","SUCCESS","Image Base: 0x6f0000, Image Size: 0x30000","C:\Windows\system32\notepad.exe"

in the above logs rows 2-7 should be one row.

I want to read it like powershell did it nicely here using import-csv function:

And you can easily pull data from specific cell by its row and column using the command (example):

$csvContent[0] |select -expand Detail

Example:

Answer 1

Instead of manually reading in the lines, you can use a library like CsvHelper, which will remove a lot of the headache from parsing a csv.

Answer 2

I know this is not a pretty way to do it, but it Works in my case:

lineCounter = 0;
while (!reader.EndOfStream)
{
     var line = reader.ReadLine();
     var values = line.Split(',');

     if(values.Length == 1)
     {
        list4[lineCounter-1] += values[0];
     }
     else
     {
          list1.Add(values[0]);
          list2.Add(values[1]);
          list3.Add(values[2]);
          list4.Add(values[3]);
          lineCounter++;
     }

}

Answer 3

.Net does not yet offer a standard library to read CSV.

Although the CSV specification is relatively simple, parsing a csv with multi-line data is not exactly trivial.

Some people "cheat" with Regular Expression, but then you need to read the whole file into string, since the regex cannot pull in more lines on demand, and you still need to detect and handle row breaks. And that is before we measure its performance, conformant, or your new problem.

The standard recommendation is to use a well-tested parser package. CsvHelper is pretty comprehensive, and I would suggest NReco.Csv if you just want to read raw data.

---

With that said, sometimes you may not prefer a package or have restricted options. Whatever the reason, I have written a csv parser in a few static methods that you can copy and paste into your project and get going.

Usage:

using ( var r = new StreamReader( filePath, Encoding.UTF8, true ) ) {
   while ( r.TryReadCsvRow( out var row ) ) {
      foreach ( string cell in row ) {
         // Your code here.
      }
   }
}

using ( var r = new StringReader( csvString ) ) {
   while ( r.TryReadCsvRow( out var row ) ) {
      string[] cells = row.ToArray();
      // `cells` is reusable and random-accessible
   }
}

Parser Code:

/**
 * <summary>Try read a csv row from a Reader.  May consume multiple lines.  Linebreaks in cells will become \n</summary>
 * <param name="source">Reader to get line data from.</param>
 * <param name="row">Cell data enumeration (forward-only), or null if no more rows.</param>
 * <param name="quoteBuffer">Thread-local buffer for quote parsing. If null, one will be created on demand.</param>
 * <returns>True on success, false on no more rows.</returns>
 * <see cref="StreamReader.ReadLine"/>
 */
public static bool TryReadCsvRow ( this TextReader source, out IEnumerable<string> row, StringBuilder quoteBuffer = null ) {
   row = ReadCsvRow( source, quoteBuffer );
   return row != null;
}

/**
 * <summary>Read a csv row from a Reader.  May consume multiple lines.  Linebreaks in cells will become \n</summary>
 * <param name="source">Reader to get line data from.</param>
 * <param name="quoteBuffer">Thread-local buffer for quote parsing. If null, one will be created on demand.</param>
 * <returns>Cell data enumeration (forward-only), or null if no more rows.</returns>
 * <see cref="StreamReader.ReadLine"/>
 */
public static IEnumerable<string> ReadCsvRow ( this TextReader source, StringBuilder quoteBuffer = null ) {
   var line = source.ReadLine();
   if ( line == null ) return null;
   return ReadCsvCells( source, line, quoteBuffer );
}

private static IEnumerable<string> ReadCsvCells ( TextReader source, string line, StringBuilder buf ) {
   for ( var pos = 0 ; line?.Length >= pos ; )
      yield return ReadCsvCell( source, ref line, ref pos, ref buf );
}

private static string ReadCsvCell ( TextReader source, ref string line, ref int pos, ref StringBuilder buf ) {
   var len = line.Length;
   if ( pos >= len ) { // EOL
      pos = len + 1;
      return "";
   }

   // Unquoted cell.
   if ( line[ pos ] != '"' ) {
      var end = line.IndexOf( ',', pos );
      var head = pos;
      // Last cell in this row.
      if ( end < 0 ) {
         pos = len + 1;
         return line.Substring( head );
      }
      // Empty cell.
      if ( end == pos ) {
         pos++;
         return "";
      }
      pos = end + 1;
      return line.Substring( head, end - head );
   }

   // Quoted cell.
   if ( buf == null )
      buf = new StringBuilder();
   else
      buf.Clear();
   var start = ++pos; // Drop opening quote.
   while ( true ) {
      var end = pos < len
         ? line.IndexOf( '"', pos )
         : -1;
      var next = end + 1;

      // End of line.  Append and read next line.
      if ( end < 0 ) {
         buf.Append( line, start, len - start );
         if ( ( line = source.ReadLine() ) == null )
            return buf.ToString();
         buf.Append( '\n' );
         start = pos = 0; len = line.Length;

       // End of cell.
      } else if ( next == len || line[ next ] == ',' ) {
         pos = end + 2;
         return buf.Append( line, start, end - start ).ToString();

      // Two double quotes.
      } else if ( line[ next ] == '"' ) {
         buf.Append( line, start, end - start + 1 );
         pos = start = end + 2;

      // One double quote not followed by EOL or comma.
      } else
         pos++;
   }
}

Pros

• Low overhead, works with big files. (e.g. 800mb census data)
• Works with all line breaks, parse quoted cells.
• Optional buffer to improve quote parsing speed.
• Thread safe, if buffer is not shared between threads. No locking.
• No dependency. No Nuget. Works in all modern .Net.

Cons

• All line breaks will be converted to \n.
• Output is forward-only / use-once. Solve with ToArray or ToList.
• Buffer, if provided, is not cleared after read.