Using CsvReader (or another lib) to parse CSV with uneven columns

Question

I hav a CSV file that looks like this:

 |------------|-----------|------------|-----------|
 |Generic Text|           |            |           |
 |------------|-----------|------------|-----------|
 |Generic Text|           |            |           |
 |------------|-----------|------------|-----------|
 |Header 1    |Header 2   |Header 3    | Header 4  |
 |------------|-----------|------------|-----------|
 |Val 1       | Val 2     |    Val 3   |   Val 4   |
 |------------|-----------|------------|-----------|
 |Val 1       | Val 2     |    Val 3   |   Val 4   |
 |------------|-----------|------------|-----------|
 |Val 1       | Val 2     |    Val 3   |   Val 4   |
 |------------|-----------|------------|-----------|
 
  

The problem is, the first 2 lines of the CSV file appear as though the entire file only has one column - ie it's something like:

  Generic Text
  Generict Text
  Header 1,Header 2,Header 3,Header 4 
  Val 1,Val 2,Val 3,Val 4 
  Val 1,Val 2,Val 3,Val 4 
  etc etc

When I run the following (from CsvReader NuGet package from LumenWorks), the outcome appears to only register 1 column for every row.

This is the code:

  var csvTable = new DataTable();         
  using (var csvReader = new CsvReader(new StreamReader(System.IO.File.OpenRead(filePath)), true))
  {
      csvTable.Load(csvReader);

  }

Problem: As mentioned above - CsvTable result only has a single column as opposed to registering 4 columns.

What can I do in order register the remaining 3 columns (as blanks) for the first 2 rows?

Answer 1

You can try Cinchoo ETL - An open source library to parse such files.

If you want to load your CSV file ignoring the first 2 lines, you can do so as below

string csv = @"Generic Text1
Generict Text2
Header 1,Header 2,Header 3,Header 4 
Val 1,Val 2,Val 3,Val 4 
Val 1,Val 2,Val 3,Val 4";


using (var r = ChoCSVReader.LoadText(csv).WithHeaderLineAt(3)
      )
{
    foreach (var rec in r)
        rec.Print();
}

Sample fiddle: https://dotnetfiddle.net/o6F3Cd

On the other hand, you want to load your CSV file including the first 2 lines as well, you can do so as below

string csv = @"Generic Text1
Generict Text2
Header 1,Header 2,Header 3,Header 4 
Val 1,Val 2,Val 3,Val 4 
Val 1,Val 2,Val 3,Val 4";


using (var r = ChoCSVReader.LoadText(csv)
       .WithField("Col1")
       .WithField("Col2")
       .WithField("Col3")
       .WithField("Col4").ThrowAndStopOnMissingField(false)
      )
{
    foreach (var rec in r)
        rec.Print();
}

Sample fiddle: https://dotnetfiddle.net/zNDrlg