Reading ugly csv with tons of commas csvhelper

I have a csv file which looks like this:

,"CompanyNane",,,,,,,,,,,,,,,,,,,,,,,,,,,,Issue,,,,2021-02-27,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,Inspection Sheet  ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,<Sub>,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1/200,,,,page,,,,,
,Delivery No.,,,,,,,,SDK2302278101,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,W No.,,,,,,,,AFC1210-22SL,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Manufacturer,,,,,,,,ManufacturerCompanyName,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Diameter,,,,,,,,6in,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Poly-type/Conductivity,,,,,,,,4H/n-type,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,face/Orientation,,,,,,,,Si/(0001) 4deg off,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Surface Finish,,,,,,,,CMP,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,"Coordinate data (X, Y) (mm)",,,,,,,,,,,,,Thickness (um),,,,,,,,Carrier Conc. (cm-3),,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",70.0,,),,,,,14.67,,,,,,,,6829484552804830,,,,,,,,,,,,,,,,,,14.67
,,,,,,,(,0,,", ",60.0,,),,,,,15.18,,,,,,,,7269218633966170,,,,,,,,,,,,,,,,,,15.18
,,,,,,,(,0,,", ",45.0,,),,,,,15.35,,,,,,,,7101511576788490,,,,,,,,,,,,,,,,,,15.35
,,,,,,,(,0,,", ",30.0,,),,,,,15.32,,,,,,,,6874133261805120,,,,,,,,,,,,,,,,,,15.32
,,,,,,,(,0,,", ",15.0,,),,,,,15.2,,,,,,,,6701359170793300,,,,,,,,,,,,,,,,,,15.2
,,,,,,,(,0,,", ",0.0,,),,,,,15.13,,,,,,,,6638519222094540,,,,,,,,,,,,,,,,,,15.13
,,,,,,,(,0,,", ",-15.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-30.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-45.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-60.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-67.0,,),,,,,15.04,,,,,,,,7104368264503040,,,,,,,,,,,,,,,,,,15.04
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,-70,,", ",0.0,,),,,,,14.54,,,,,,,,6828523559220620,,,,,,,,,,,,,,,,,,14.54
,,,,,,,(,-60,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-45,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-30,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-15,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,15,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,30,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,45,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,60,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,70,,", ",0.0,,),,,,,14.65,,,,,,,,6866040706547180,,,,,,,,,,,,,,,,,,14.65
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,Ave.,,,,,,,,,,,,,15.15,,,,,,,,6950000000000000,,,,,,,,,,,,,,,,,,
,,,,,Uniformity(sigma/Ave.),,,,,,,,,,,,,1.6,,,,,%,,,3.1,,,,,,%,,,,,,,,,,,,
,,,,,MAX,,,,,,,,,,,,,15.35,,,,,,,,7270000000000000,,,,,,,,,,,,,,,,,,
,,,,,MIN,,,,,,,,,,,,,14.54,,,,,,,,6640000000000000,,,,,,,,,,,,,,,,,,

I honestly don't even know where to begin, I have no control over this csv file, as it is an incoming file from elsewhere, surely there is a way to replace these commas somehow? I understand this data is very ugly, the top line, I need the issue with the date, then starting at the <sub> line, I need all data from that point forward.

The lines:

,<Sub>,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1/200,,,,page,,,,,
,Delivery No.,,,,,,,,SDK2302278101,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,W No.,,,,,,,,AFC1210-22SL,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Manufacturer,,,,,,,,ManufacturerCompanyName,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Diameter,,,,,,,,6in,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Poly-type/Conductivity,,,,,,,,4H/n-type,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,face/Orientation,,,,,,,,Si/(0001) 4deg off,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,Surface Finish,,,,,,,,CMP,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Have their data horizontally, normally I can do this, just not with all those commas, it makes their indexes all different!

The lines:

,,,,,"Coordinate data (X, Y) (mm)",,,,,,,,,,,,,Thickness (um),,,,,,,,Carrier Conc. (cm-3),,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",70.0,,),,,,,14.67,,,,,,,,6829484552804830,,,,,,,,,,,,,,,,,,14.67
,,,,,,,(,0,,", ",60.0,,),,,,,15.18,,,,,,,,7269218633966170,,,,,,,,,,,,,,,,,,15.18
,,,,,,,(,0,,", ",45.0,,),,,,,15.35,,,,,,,,7101511576788490,,,,,,,,,,,,,,,,,,15.35
,,,,,,,(,0,,", ",30.0,,),,,,,15.32,,,,,,,,6874133261805120,,,,,,,,,,,,,,,,,,15.32
,,,,,,,(,0,,", ",15.0,,),,,,,15.2,,,,,,,,6701359170793300,,,,,,,,,,,,,,,,,,15.2
,,,,,,,(,0,,", ",0.0,,),,,,,15.13,,,,,,,,6638519222094540,,,,,,,,,,,,,,,,,,15.13
,,,,,,,(,0,,", ",-15.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-30.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-45.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-60.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,0,,", ",-67.0,,),,,,,15.04,,,,,,,,7104368264503040,,,,,,,,,,,,,,,,,,15.04
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,(,-70,,", ",0.0,,),,,,,14.54,,,,,,,,6828523559220620,,,,,,,,,,,,,,,,,,14.54
,,,,,,,(,-60,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-45,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-30,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,-15,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,15,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,30,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,45,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,60,,", ",0.0,,),,,,,-,,,,,,,,-,,,,,,,,,,,,,,,,,,
,,,,,,,(,70,,", ",0.0,,),,,,,14.65,,,,,,,,6866040706547180,,,,,,,,,,,,,,,,,,14.65
,,,,,,,(,,,", ",,,),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,Ave.,,,,,,,,,,,,,15.15,,,,,,,,6950000000000000,,,,,,,,,,,,,,,,,,
,,,,,Uniformity(sigma/Ave.),,,,,,,,,,,,,1.6,,,,,%,,,3.1,,,,,,%,,,,,,,,,,,,
,,,,,MAX,,,,,,,,,,,,,15.35,,,,,,,,7270000000000000,,,,,,,,,,,,,,,,,,
,,,,,MIN,,,,,,,,,,,,,14.54,,,,,,,,6640000000000000,,,,,,,,,,,,,,,,,,

These lines are like a traditional csv with a header at the top and the data underneath, but again, the commas make this real tricky. Any help would be appreciated! Just trying to get this data into a list.

Here's my current code:

class CsvHelperTester
    {
        static void Main(string[] args)
        {


            var csvConfig = new CsvConfiguration(CultureInfo.InvariantCulture)
            {
                HasHeaderRecord = false,
                HeaderValidated = null,
                IgnoreBlankLines = true,
                MissingFieldFound = null,
                AllowComments = true,
                Comment = ';',
                Delimiter = ",",
                TrimOptions = TrimOptions.Trim,
                
            };
            using (var streamReader = new StreamReader("C:filestuff"))
            {
                using (var csv = new CsvReader(streamReader, csvConfig))
                {

                    var records = new List<GeneralData>();


                    while (csv.Read())
                    {
                        if (csv.GetField(1) == "")
                        {
                            csv.Read();
                        }
                        records.Add(csv.GetRecord<GeneralData>());
                    }

                    var WRecords = csv.GetRecords<WData>().ToList();
                    var columns = records;

                }
            }
        }
        public class GeneralData
        {
            [Index(1)]
            public string Type { get; set; }
            [Index(2)]
            public string Value { get; set; }
        }
        public class WData
        {
            public string Coordinate_Data { get; set; }
        }

    }

I tried to make 2 lists, 1 for the horizontal data, and 1 for the traditional csv data, but was unsuccessful

To replace all the repeated commas with a single one use Regex.Replace

Get the CSV file as a string then use:

string result = Regex.Replace(csvString, ",+", ",");

Finally, convert the resulting string back to CSV

It is a bit ugly but I think you can use a combination of looking for known stopping points along with data classes that use indexing to find their columns.

void Main()
{
	var config = new CsvConfiguration(CultureInfo.InvariantCulture)
	{
		HasHeaderRecord = false
	};
	
	using (var reader = new StreamReader("C:filestuff"))
	using (var csv = new CsvReader(reader, config))
	{
		csv.Read();
		var company = csv.GetField(1);
		var issue = csv.GetField(29);
		var issueDate = csv.GetField(33);
		
		var topData = new List<TopData>();
		var coordinateData = new List<CoordinateData>();
		var summaryData = new List<SummaryData>();
		
		// Skip to <Sub>
		while (csv.Read() && csv.GetField(1) != "<Sub>") { }
		
		// Read Horizontal data
		while (csv.Read() && csv.GetField(5) != "Coordinate data (X, Y) (mm)")
		{
			if (csv.GetField(1) != string.Empty)
				topData.Add(csv.GetRecord<TopData>());
		}

		// Read Coordinate data
		while (csv.Read() && csv.GetField(5) != "Ave.")
		{
			if (csv.GetField(8) != string.Empty)
				coordinateData.Add(csv.GetRecord<CoordinateData>());
		}
		
		// Read Summary data
		summaryData.Add(csv.GetRecord<SummaryData>());
		while (csv.Read())
		{
			summaryData.Add(csv.GetRecord<SummaryData>());
		}
	}
}

public class TopData
{
	[Index(1)]
	public string Name { get; set; }
	[Index(9)]
	public string Value { get; set; }
}

public class SummaryData
{
	[Index(5)]
	public string Name { get; set; }
	[Index(18)]
	public string Value1 { get; set; }
	[Index(26)]
	public string Value2 { get; set; }
}

public class CoordinateData
{
	[Index(8)]
	public string X { get; set; }
	[Index(11)]
	public string Y { get; set; }
	[Index(18)]
	public string Mm { get; set; }
	[Index(26)]
	public string Thickness { get; set; }
}

Here is my solution. You only had one sample of the input so my code probably will need some tweaks before it will work on entire input.

<!-- begin snippet: js hide: false console: true babel: false -->

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;
namespace ConsoleApplication2
{
class Program
{
const string FILENAME = @"c:\temp\test.csv";
public enum State
{
NONE,
GET_DELIVERY,
GET_PART
}
static void Main(string[] args)
{
StreamReader reader = new StreamReader(FILENAME);
string line = "";
List<Delivery> deliveries = new List<Delivery>();
Delivery delivery = null;
Part part = null;
State state = State.NONE;
while((line = reader.ReadLine()) != null)
{
line = line.Trim(new char[] { ' ', ',' });
if(line.Length > 0)
{
if(line.StartsWith("<Sub>"))
{
state = State.GET_DELIVERY;
delivery = new Delivery();
deliveries.Add(delivery);
continue;
}
if (line.StartsWith("\"Coordinate data"))
{
//do nothing
continue;
}    
switch (state)
{
case State.GET_DELIVERY:
if (line == "(,,,\", \",,,)")
{
state = State.GET_PART;
part = new Part();
if (delivery.parts == null) delivery.parts = new List<Part>();
delivery.parts.Add(part);
continue;
}
delivery.AddProperty(line);
break;
case State.GET_PART:
if (line == "(,,,\", \",,,)")
{
state = State.GET_DELIVERY;
continue;
}
part.AddPoint(line);
break;
}
}
}
}
}
public class Delivery
{
public string deliverNo { get; set; }
public string wNo { get; set; }
public string manufacturer { get; set; }
public string diameter { get; set; }
public string pTypeConductivity { get; set; }
public string faceOrientation { get; set; }
public string surfaceFinish { get; set; }
public decimal average { get; set; }
public decimal uniformitySigma { get; set; }
public decimal uniformityAve { get; set; }
public decimal max { get; set; }
public decimal min { get; set; }
public List<Part> parts { get; set; }
public void AddProperty(string line)
{
string[] splitArray = line.Split(new char[] {','});
switch(splitArray[0])
{
case "Delivery No.":
deliverNo = splitArray[8];
break;
case "W No.":
wNo = splitArray[8];
break;
case "Manufacturer":
manufacturer = splitArray[8];
break;
case "Diameter":
diameter = splitArray[8];
break;
case "Poly-type/Conductivity":
pTypeConductivity = splitArray[8];
break;
case "face/Orientation":
faceOrientation = splitArray[8];
break;
case "Surface Finish":
surfaceFinish = splitArray[8];
break;
case "Ave.":
average = decimal.Parse(splitArray[13]);
break;
case "Uniformity(sigma/Ave.)":
uniformityAve = decimal.Parse(splitArray[13]);
uniformitySigma = decimal.Parse(splitArray[21]);
break;
case "MAX":
max = decimal.Parse(splitArray[13]);
break;
case "MIN":
min = decimal.Parse(splitArray[13]);
break;
}
}
}
public class Part
{
public string part { get; set; }
public decimal thinkness { get; set; }
public List<Tuple<decimal, decimal, decimal>> points { get; set; }
decimal lastThinkness = 0;
public void AddPoint(string line)
{
if (points == null) points = new List<Tuple<decimal, decimal, decimal>>();
string[] splitArray = line.Split(new char[] { ',' });
decimal x = decimal.Parse(splitArray[1]);
decimal y = decimal.Parse(splitArray[5]);
decimal thickness = (splitArray[12] == "-") ? lastThinkness : decimal.Parse(splitArray[12]);
lastThinkness = thickness;
Tuple<decimal, decimal, decimal> point = Tuple.Create(x,y,thickness);
points.Add(point);
}
}
}

<!-- end snippet -->