How to read delimited files with header and footer

Support/help with CloverETL implementation problems

brendanv
Posts: 1
Joined: Sat Jul 08, 2017 5:10 am

How to read delimited files with header and footer

Postby brendanv » Sat Jul 08, 2017 5:17 am

Hi

I have a pipe delimited file, with a header, multiple transactions, and finally a footer.
How do i go about reading in a file like this where I would want to use the each record type in the rest of my graph. The footer, for example, contains a total that I need to validate against the sum of tranactions.
Would I use a Complex DataReader?

Thanks

jandikovae
Posts: 27
Joined: Fri Nov 04, 2016 8:51 am

Re: How to read delimited files with header and footer

Postby jandikovae » Wed Jul 12, 2017 12:03 pm

Hi,

Yes, you are right. For data like this, we would usually recommend ComplexDataReader. However setting up this component might sometimes be a little tricky and whether this is the best solution depends on the exact structure of your data. For example:
1. Is there always just one header, some number of transactions and one footer?
2. Is it always in this order?
3. Does each of this records have any prefix or standard field?

I assume that the answer is "yes" for all those questions and based on that assumption I have prepared two different examples of the solution for you.

One of them is using the ComplexDataReader (complexData.grf). It splits the data into three parts:
1. Header: The ComplexDataReader assumes that the header is just one line, and then it automatically continues to the next state: transactions.
2. Transactions: The lines are being filtered out to the second output as long as the prefix is not FOOTER.
3. Footer: The ComplexDataReader, as it is setup in my example, supposes that after it turns to state "$2 footer", there is just one "footer" line left to be processed.
The data are then passed to three output ports, each with its own metadata.

With this kind of data, you can also consider filtering record lines based on the known prefix (or part of a string and so on). For example, if the transaction line always starts with a prefix TRANS, you can filter the records to a given output using the following expression: startsWith($in.0.field1,"TRANS") in a Filter component (see filterData.grf). This way you can then reformat each line and process it independently.

Please review and if none of it is a suitable solution for you, please provide me with an example of your data (feel free to remove any sensitive information from the file, or you can send it to email support@cloveretl.com).

Thanks and have a nice day Eva
Attachments
filterData.grf
Graph
(1.53 KiB) Downloaded 7 times
complexData.grf
Graph
(5.07 KiB) Downloaded 7 times
complexdata.txt
Input Data
(83 Bytes) Downloaded 7 times
---
Eva Jandikova
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support


cron