Looping header and details records in same file

Question

I have re edit this question below I have an example file which as multiple purchase orders in the file which is identified by the second column.

Order Number, Purchase Number,DATE,Item Code ,Qty, Description 1245456,98978,12/01/2019, 1545-878, 1,"Test"

1245456,98978,12/01/2019,1545-342,2,"Test"

1245456,98978,12/01/2019,1545-878,2,"Test"

1245456,98979,12/02/2019,1545-878,3,"Test 3"

1245456,98979,12/02/2019,1545-342,4,"Test 4"

1245456,98979,12/02/2019,1545-878,5,"Test 4"

What I want the end result to be is to be able to place the above into one class like the following

At the min I am using filelpers to parse the csv file this would work fine if I had sep header file and row file but they are combined as you see

var engine = new FileHelperEngine<CSVLines>();
var lines = engine.ReadFile(csvFileName);

So the Class should be like below

  [DelimitedRecord(",")]
  public class SalesOrderHeader
  {
     private Guid? _guid;


    public  Guid RowID
    {
        get
        {
            return _guid ?? (_guid = Guid.NewGuid()).GetValueOrDefault();
        }
    }
    public string DocReference { get; set; }
    public string CardCode { get; set; }
    public string DocDate { get; set; }
    public string ItemCode { get; set; }

    public string Description { get; set; }
    public string Qty { get; set; }

    public string Price { get; set; }


    [FieldHidden]
    public List<SalesOrderHeader> OrdersLines { get; set; }
  }

What I imagine I will have to do is two loops as you will see from my createsales order routine i first create the header and then add the lines in.

 public void CreateSalesOrder(List<SalesOrderHeader> _salesOrders)
    {
        foreach (var record in _salesOrders.GroupBy(g => g.DocReference))
        {

            // Init the Order object
            oOrder = (SAPbobsCOM.Documents)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
            SAPbobsCOM.SBObob oBob;
            // set properties of the Order object
           // oOrder.NumAtCard = record.Where(w=>w.RowID = record.Where()
            oOrder.CardCode = record.First().CardCode;
            oOrder.DocDueDate = DateTime.Now;

            oOrder.DocDate =Convert.ToDateTime(record.First().DocDate);
            foreach (var recordItems in _salesOrders.SelectMany(e=>e.OrdersLines).Where(w=>w.DocReference ==record.First().DocReference))
            {
                oOrder.Lines.ItemCode = recordItems.ItemCode;
                oOrder.Lines.ItemDescription = recordItems.Description;
                oOrder.Lines.Quantity = Convert.ToDouble(recordItems.Qty);
                oOrder.Lines.Price = Convert.ToDouble(recordItems.Price);

                oOrder.Lines.Add();
                log.Debug(string.Format("Order Line added to sap Item Code={0}, Description={1},Qty={2}", recordItems.ItemCode, recordItems.Description, recordItems.Qty));

            }
            int lRetCode = oOrder.Add(); // Try to add the orer to the database
        }

        if(lRetCode == 0)
        {
            string body = "Purchase Order Imported into SAP";               
 
        }
        if (lRetCode != 0)
        {
            int temp_int = lErrCode;
            string temp_string = sErrMsg;
            company.GetLastError(out temp_int, out temp_string);
            if (lErrCode != -4006) // Incase adding an order failed
            {
                log.Error(string.Format("Error adding an order into sap ErrorCode {0},{1}", temp_int, temp_string));
            }

        }
    }

The problem you will see i have is how do I first split the csv into the two lists and second how do i access the header rows correctly in the strongly type object as you see I am using first which will not work correctly.

Answer 1

With FileHelpers it is important to avoid using the mapping class for anything other than describing the underlying file structure. Here I suspect you are trying to map directly to a class which is too complex.

A FileHelpers class is just a way of defining the specification of a flat file using C# syntax.

As such, the FileHelpers classes are an unusual type of C# class and you should not try to use accepted OOP principles. FileHelpers should not have properties or methods beyond the ones used by the FileHelpers library.

Think of the FileHelpers class as the 'specification' of your CSV format only. That should be its only role. (This is good practice from a maintenance perspective anyway - if the underlying CSV structure were to change, it is easier to adapt your code).

Then if you need the records in a more 'normal' object, then map the results to something better, that is, a class that encapsulates all the functionality of the Order object rather than the CSVOrder.

So, one way of handling this type of file is to parse the file twice. In the first pass you extract the header records. Something like this:

var engine1 = new FileHelperEngine<CSVHeaders>();
var headers = engine1.ReadFile(csvFileName);

In the second pass you extract the details;

var engine2 = new FileHelperEngine<CSVDetails>();
var details = engine2.ReadFile(csvFileName);

Then you combine this information into a new dedicated class, maybe with some LINQ similar to this

var niceOrders = 
    headers
        .DistinctBy(h => h.OrderNumber)
            .SelectMany(d => details.Where(d => d.OrderNumber = y))
                 .Select(x => 
                      new NiceOrder() { 
                          OrderNumber = x.OrderNumber,  
                          Customer = x.Customer,
                          ItemCode = x.ItemCode                                     
                          // etc.
                      });