Loading Excel file with Header and Line Item detail

CloverDX Platform
1

I am a new CloverETL user and have an Excel invoice file that contains both header and line item detail.

For example, row 3 is the header row and contains supplier name and invoice number. Rows 4 through 6 contain the line items. Row 7 contains the next invoice header and rows 8 and 9 contain the line items. etc. The number of lines can vary for each invoice.

I want to create an excel or csv file that adds the supplier name and invoice number to each line item. The attached excel file shows the format of the input data in the “Original Data” sheet and the preferred format in the “Transformed Data” sheet.

I can create the two files, one with header rows and the other with the line item rows; but I don’t know how to get the header row information in the line item rows.

Any assistance would be appreciated.

2

Hi, regcider,

You did not attach any Excel file (xls or xlsx), just csv files. So I will describe the procedure for a csv file.

Basic idea is in adding some key joining item lines with their PO line. Then you can filter the input lines into two streams. I have noticed that you may filter the lines by the first character. Lines starting with “P” would be sent to another output than the others. And then you can join them using join key.

I hope everything will be clear when you see the example attached here. csv_processing.zip

Best regards,