In C# how do I take a csv and seperate data into two tables depending on if the data is line data or header data

Question

I am trying to do the following but am really struggling on coming up with the solution. Please help.

I have a CSV file and this file has data related to invoices from an accounting system.

An invoice can have what I define as header and line data.

Header data is your fields like customer name, reference, document date etc. Line data is repetitive and can be your items/service and prices.

These two types of data are mixed together in a single line.

For example the 3 lines below represent a single invoice:

Line1: Customer Name, reference, document date, item1 price1
Line2: Customer Name, reference, document date, item2 price2
Line3: Customer Name, reference, document date, item3 price3

I want to put the line data into 1 data table and the header in another. However as the above example has 3 lines the header data will be repeated 3 times. how do I add the data into the header table and prevent duplication.

---

Edit

Hi guys. Sorry, my explanation was poor. Here is an example:

Some data:

Customer1, INV-184691035,Item1, 10.00
Customer1, INV-184691035,Item2, 20.00
Customer1, INV-184691035,Item3, 30.00
Customer2, INV-184691034,Item1, 10.00
Customer2, INV-184691034,Item2, 20.00
Customer2, INV-184691034,Item3, 30.00

I want to spilt this data like this into 2 tables:

Header

Customer1|INV-184691035
Customer2|INV-184691034

Lines

INV-184691035 |Item1 |10.00
INV-184691035 |Item2 |20.00
INV-184691035 |Item3 |30.00
INV-184691034 |Item1 |10.00
INV-184691034 |Item2 |20.00
INV-184691034 |Item3 |30.00

Does this make sense?

question from:https://stackoverflow.com/questions/66063071/in-c-sharp-how-do-i-take-a-csv-and-seperate-data-into-two-tables-depending-on-if

Answer 1

So you are looking for a way to parse this csv into to tables, one containing the header info and one containing the line data, right?

First read the csv line by line with something like:

using(StreamReader SR = new StreamReader(path2file)){
line = SR.ReadLine();
var entries = line.Split(',');
//now move the elements of entries to the table where they belong, entries should be an array
}

After this reading, loop through the table containing the headers and remove duplicate entries. Is that, what you are looking for?