Reading csv file that is organized differently in 2 different places

My csv file looks like

Metals:,E10
Al,0.1906
Ca,0.1132
Co,0.01951
Cu,0.5824
Cu,0.02383
Fe,0.03828
K,0.09577
Li,0.03024
Mg,0.007145
Na,0.1833
Ni,0.3236
Pb,0.0005787
Ti,0.4931
Tl,0.001887
Zn,0.07644

GLot,id,Slot,Scribe,Diameter,MPD,SResistivity,SThickness,TTV,LTV,Warp,Bow,S_U_A,Ep,Epi_L,Epi_Layer,Epi_Layer_2,EThick,E2thick,E2Dope,E2DopeT,E2DopeMax,E2DopeMin
31075046-001,XFB-LE00674.CP10023+001-12,1,22C1285,149.98,0,0.0217,334.71,1.91,1.03,5.35,-0.91,99.590582,1.0,1.0E18,9.8,1.12,9.9,9.6,9926193600000000,4.5574,10834500800000000,9551876800000000

My code looks like this:

namespace CsvHelperTest
{
    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, 
                PrepareHeaderForMatch = header => Regex.Replace(header.Header, ",", "\n"),
            };

            using (var streamReader = new StreamReader("C:\\Users\\eyoung\\Desktop\\parse test files\\XFB-1C2002A_62152_CoA.csv"))
            {
                using (var csvReader = new CsvReader(streamReader, csvConfig))
                {
                    for (var i = 0; i < 1; i++)
                    {
                        csvReader.Read();
                    }

                    var records = csvReader.GetRecords<EpiDataNames>().ToList();

                    var table = records[0];

                    records.RemoveAt(0);

                    var columns = records;

                    using (var writer = new CsvWriter(Console.Out, CultureInfo.InvariantCulture))
                    {
                        //writer.WriteField(records[0].Type);
                        //writer.NextRecord();

                        //records.RemoveAt(0);
                        //foreach (var item in records.Select(r => r.Type))
                        //{
                        //    writer.WriteField(item);
                        //}
                        //writer.NextRecord();
                        //foreach (var item in records.Select(r => r.Value))
                        //{
                        //    writer.WriteField(item);
                        //}
                        //writer.NextRecord(); 
                    }
                }
            }
        }

        public class EpiDataNames
        {
            [Index(0)]
            public string Type { get; set; }
            [Index(1)]
            public string Value { get; set; }
        }
    }
}

This is great as it takes the first set of data and makes them into 2 columns, 'Type' and 'Value', however, the issue arises when the 2nd set of data is shown, is there a way I can only read the first block of data? When I try to leave out those last headers, it acts weird and removes the first block of data.

for (var i = 0; i < 1; i++)
{
    csvReader.Read(); //this skips the first line of data
}

for (var i = 0; i > 18; i++)
{
    csvReader.Read(); //I thought this would skip the last lines of data, but it doesn't.
}

The issue with the second block of headers is that the headers are reading like

when it should be,

Any ideas? I'm pretty lost on this one, I should also preface, I have no control over editing this csv file beforehand.

Your text file consists of two separate CSV tables with headers, separated by a blank line. It is possible to read such a file with CsvHelper, however you will need to read it manually line-by-line and track when one table ends and a new table begins. Then when a new table begins, you will need to introduce some heuristic to determine which type of table it is.

The following method CsvExtensions.ReadTwoTableCsv() is one way to do this:

public static class CsvExtensions
{
	public static void ReadTwoTableCsv<TRecord1, TRecord2>(TextReader reader, 
														   ClassMap<TRecord1> map1, out List<TRecord1> list1,
														   ClassMap<TRecord2> map2, out List<TRecord2> list2)
	{
		(List<TRecord1> l1, List<TRecord2> l2) = (new(), new());
		ReadMultiTableCsv(reader,
						  (map1, HeaderMatchesFirstMember, (map, csv) => l1.Add(csv.GetRecord<TRecord1>())),
						  (map2, HeaderMatchesFirstMember, (map, csv) => l2.Add(csv.GetRecord<TRecord2>())));
		(list1, list2) = (l1, l2);
	}
										 
	static bool HeaderMatchesFirstMember(ClassMap map, CsvReader reader)
	{
		var firstMember = map.MemberMaps.Where(p => !p.Data.Ignore && p.Data.IsNameSet).SelectMany(p => p.Data.Names).FirstOrDefault();
		return Enumerable.Range(0, reader.Parser.Count)
			.Any(i => string.Equals(firstMember, reader.Parser[i], StringComparison.OrdinalIgnoreCase));
	}
	
	enum ReadState
	{
		Initial,
		Header,
		Data,
		UnknownData,
	}		
	
	public static void ReadMultiTableCsv(TextReader reader, 
										 params (ClassMap map, Func<ClassMap, CsvReader, bool> isMatch, Action<ClassMap, CsvReader> readRecord) [] maps)
	{
		CsvConfiguration config = new(CultureInfo.InvariantCulture)
		{
			// These options are required to make ReadMultiTableCsv() work correctly:
            HasHeaderRecord = true,   // Headers are required, and are used to determine the table type,
            IgnoreBlankLines = false, // A blank line is used to delimit CSV sections, so we can't ignore it.
			// Other options as required by your application:
            HeaderValidated = null,
            MissingFieldFound = null,
            AllowComments = true,
            Comment = ';',
            Delimiter = ",",
            TrimOptions = TrimOptions.Trim, 
            PrepareHeaderForMatch = header => header.Header.ToLowerInvariant(),
		};
		
		using (var csv = new CsvReader(reader, config))
		{
            (int currentMap, ReadState state) = (-1, ReadState.Initial);
			int currentCount = -1;
			while (csv.Read())
			{
				if (csv.Parser.Count < 1 || csv.Parser.Count != currentCount)
				{
					// Blank line or change in the number of columns
					if (currentMap != -1)
						csv.Context.UnregisterClassMap();
					(currentMap, state) = (-1, ReadState.Initial);
				}

				currentCount = csv.Parser.Count;
				if (currentCount < 1 || state == ReadState.UnknownData)
				{
					// Do nothing
				}
				else if (state == ReadState.Initial)
				{
					var newMap = maps.Select((map, index) => (map, index)).Where(p => p.map.isMatch(p.map.map, csv)).Select(p => p.index).SingleOrDefault(-1);
					if (newMap >= 0)
					{
						csv.Context.RegisterClassMap(maps[newMap].map);
						csv.ReadHeader();
						(currentMap, state) = (newMap, ReadState.Data);
					}
					else
					{
						(currentMap, state) = (-1, ReadState.UnknownData);
					}
				}
				else if (state == ReadState.Data)
				{
					maps[currentMap].readRecord(maps[currentMap].map, csv);
				}
				else
				{
					throw new InvalidOperationException("Unexpected state");
				}
			}
		}			
	}
} 

Then if your two data models look like:

public class EpiDataNames
{
	[Name("Metals:")]
	public string Type { get; set; }
	[Name("E10")]
	public string Value { get; set; }
}

class EpiDataNamesMap : ClassMap<EpiDataNames>
{
	public EpiDataNamesMap() : this(new CsvConfiguration(CultureInfo.InvariantCulture)) {}
	public EpiDataNamesMap(CsvConfiguration config) => AutoMap(config);
}

public class Model2
{
	// Auto generated by https://toolslick.com/generation/code/class-from-csv
	public string GLot { get; set; }
	public string Id { get; set; }
	public int Slot { get; set; }
	public string Scribe { get; set; }
	public double Diameter { get; set; }
	public int MPD { get; set; }
	public double SResistivity { get; set; }
	public double SThickness { get; set; }
	public double TTV { get; set; }
	public double LTV { get; set; }
	public double Warp { get; set; }
	public double Bow { get; set; }
	public double SUA { get; set; }
	public double Ep { get; set; }
	public string EpiL { get; set; }
	public double EpiLayer { get; set; }
	public double EpiLayer2 { get; set; }
	public double EThick { get; set; }
	public double E2thick { get; set; }
	public long E2Dope { get; set; }
	public double E2DopeT { get; set; }
	public long E2DopeMax { get; set; }
	public long E2DopeMin { get; set; }
}

public class Model2ClassMap : ClassMap<Model2>
{
	// Auto generated by https://toolslick.com/generation/code/class-from-csv
	public Model2ClassMap()
	{
		Map(m => m.GLot).Name("GLot");
		Map(m => m.Id).Name("id");
		Map(m => m.Slot).Name("Slot");
		Map(m => m.Scribe).Name("Scribe");
		Map(m => m.Diameter).Name("Diameter");
		Map(m => m.MPD).Name("MPD");
		Map(m => m.SResistivity).Name("SResistivity");
		Map(m => m.SThickness).Name("SThickness");
		Map(m => m.TTV).Name("TTV");
		Map(m => m.LTV).Name("LTV");
		Map(m => m.Warp).Name("Warp");
		Map(m => m.Bow).Name("Bow");
		Map(m => m.SUA).Name("S_U_A");
		Map(m => m.Ep).Name("Ep");
		Map(m => m.EpiL).Name("Epi_L");
		Map(m => m.EpiLayer).Name("Epi_Layer");
		Map(m => m.EpiLayer2).Name("Epi_Layer_2");
		Map(m => m.EThick).Name("EThick");
		Map(m => m.E2thick).Name("E2thick");
		Map(m => m.E2Dope).Name("E2Dope");
		Map(m => m.E2DopeT).Name("E2DopeT");
		Map(m => m.E2DopeMax).Name("E2DopeMax");
		Map(m => m.E2DopeMin).Name("E2DopeMin");
	}
}

You will be able to read your CSV file into a List<EpiDataNames> and List<Model2> as follows:

using var textReader = new StreamReader(fileName, Encoding.UTF8);
CsvExtensions.ReadTwoTableCsv(textReader, 
							  new EpiDataNamesMap(), out var list1, 
							  new Model2ClassMap(), out var list2);

Notes:

• The algorithm HeaderMatchesFirstMember(ClassMap map, CsvReader reader) I am using to determine the correct model from the current header -- either EpiDataNames or Model2 -- is pretty crude. I look to see whether the column name of the first mapped model member -- either "Metals:" or "GLot" -- is present in the current list of headers. This works because the two names differ. If the first names were identical -- say "Id" and "Id" -- one would need to use a smarter algorithm

• I auto-generated the second data model using https://toolslick.com/generation/code/class-from-csv.

Demo fiddle here.

If you only want to read the first block of data, your code can be pretty simple.

var config = new CsvConfiguration(CultureInfo.InvariantCulture)
{
	HasHeaderRecord = false
};

using (var reader = new StreamReader("C:\\Users\\eyoung\\Desktop\\parse test files\\XFB-1C2002A_62152_CoA.csv"))
using (var csv = new CsvReader(reader, config))
{
	var records = new List<EpiDataNames>();
	
	while(csv.Read() && csv.GetField(0) != "GLot" && csv.GetField(1) != "id")
	{
		records.Add(csv.GetRecord<EpiDataNames>());
	}
	
	records.Dump();
}

You also have the option of setting IgnoreBlankLines = false and then use the blank line as your break point. For both solutions, after you read the first block of data, you could read the second block.

The only potential issue I see with reading the second block is if you leave the default of HasHeaderRecord = true, you lose that first line of data Metals:,E10 as a header, but if you set it to false you can't read the header for the second block of data.

void Main()
{
	var data = @"Metals:,E10
Al,0.1906
Ca,0.1132
Co,0.01951
Cu,0.5824

GLot,id,Slot,Scribe
31075046-001,XFB-LE00674.CP10023+001-12,1,22C1285";

	var config = new CsvConfiguration(CultureInfo.InvariantCulture)
	{
		IgnoreBlankLines = false
	};

	using (var reader = new StringReader(data))
	using (var csv = new CsvReader(reader, config))
	{
		var records = new List<EpiDataNames>();

		while (csv.Read() && !csv.Parser.Record.All(r => r == string.Empty))
		{
			records.Add(csv.GetRecord<EpiDataNames>());
		}
		
		csv.Read(); // Remove this line if using the first solution
		csv.ReadHeader();
		
		var otherRecords = csv.GetRecords<MyOtherClass>().ToList();

		records.Dump();
		otherRecords.Dump();
	}
}

public class EpiDataNames
{
	[Index(0)]
	public string Type { get; set; }
	[Index(1)]
	public string Value { get; set; }
}

public class MyOtherClass
{
	public string GLot { get; set; }
	[Name("id")]
	public string Id { get; set; }
	public string Slot { get; set; }
	public string Scribe { get; set; }
}

Try following :

<!-- 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;
namespace ConsoleApplication52
{
class Program
{
const string FILENAME = @"c:\temp\test.csv";
static void Main(string[] args)
{
StreamReader reader = new StreamReader(FILENAME);
EpiDataNames dataNames = new EpiDataNames(reader);
EpiDataParameters parameters = new EpiDataParameters(reader);
}
}
public class EpiDataNames
{
public List<EpiDataNames> names { get; set; }
string Type { get; set; }
string Value { get; set; }
public EpiDataNames() { }
public EpiDataNames(StreamReader reader)
{
names = new List<EpiDataNames>();
int row = 0;
string line = "";
while((line = reader.ReadLine()) != null)
{
line = line.Trim();
if (line.Length == 0) break;
row++;
if (row > 1)
{
string[] splitLine = line.Split(new char[] { ',' });
EpiDataNames epi = new EpiDataNames();
epi.Type = splitLine[0].Trim();
epi.Value = splitLine[1].Trim();
names.Add(epi);
}
}
}
}
public class EpiDataParameters
{
public List<EpiDataParameters> parameters { get; set; } 
string GLot { get;set;}
string id { get;set;}
int Slot { get;set;}
string Scribe { get;set;}
decimal Diameter { get;set;}
decimal MPD { get;set;}
decimal SResistivity { get;set;}
decimal SThickness { get;set;}
decimal TTV { get;set;}
decimal LTV { get;set;}
decimal Warp { get;set;}
decimal Bow { get;set;}
decimal S_U_A { get;set;}
decimal Ep { get;set;}
double Epi_L { get;set;}
decimal Epi_Layer { get;set;}
decimal Epi_Layer_2 { get;set;}
decimal EThick { get;set;}
decimal E2thick { get;set;}
decimal E2Dope { get;set;}
decimal E2DopeT { get;set;}
decimal E2DopeMax { get;set;}
decimal E2DopeMin { get; set; }
public EpiDataParameters() { }
public EpiDataParameters(StreamReader reader)
{
parameters = new List<EpiDataParameters>();
int row = 0;
string line = "";
while ((line = reader.ReadLine()) != null)
{
line = line.Trim();
if (line.Length == 0) break;
row++;
if (row > 1)
{
string[] splitLine = line.Split(new char[] { ',' });
EpiDataParameters epi = new EpiDataParameters();
epi.GLot = splitLine[0].Trim();
epi.id = splitLine[1].Trim();
epi.Slot = int.Parse(splitLine[2]);
epi.Scribe = splitLine[3].Trim();
epi.Diameter = decimal.Parse(splitLine[4]);
epi.MPD = decimal.Parse(splitLine[5]);
epi.SResistivity  = decimal.Parse(splitLine[6]);
epi.SThickness = decimal.Parse(splitLine[7]);
epi.TTV = decimal.Parse(splitLine[8]);
epi.LTV = decimal.Parse(splitLine[9]);
epi.Warp= decimal.Parse(splitLine[10]);
epi.Bow = decimal.Parse(splitLine[11]);
epi.S_U_A = decimal.Parse(splitLine[12]);
epi.Ep = decimal.Parse(splitLine[13]);
epi.Epi_L = double.Parse(splitLine[14]);
epi.Epi_Layer = decimal.Parse(splitLine[15]);
epi.Epi_Layer_2 = decimal.Parse(splitLine[16]);
epi.EThick = decimal.Parse(splitLine[17]);
epi.E2Dope = decimal.Parse(splitLine[18]);
epi.E2DopeT = decimal.Parse(splitLine[19]);
epi.E2DopeMax = decimal.Parse(splitLine[20]);
epi.E2DopeMin = decimal.Parse(splitLine[21]);
parameters.Add(epi);
}
}
}
}
}

<!-- end snippet -->