Import .csv … Header/Details from separate lines

We have a new customer that sends us a file with an H or D in the first column of a .csv file. H(Header) D(Details) we’re use to files that contain details on each line with the order (H) information repeated on the details. I import these file into a temporary access table then run some import queries to get the data to the correct place. The problem I’m having is creating a definition to import all of the detail information and add the header information to those details. The order number is on every line. I am not well versed with the net script but I’m trying to learn.

This is what I get in the csv file:

Type Number ShipTo/QTY Address/Item
H AB456 John Smith 123 Any St
D AB456 1 57X452
D AB456 1 78Y897
H DB678 Jane Smith 456 Road
D DB678 4 89G654

This is what I need to create:

Number ShipTo Address QTY Item
AB456 John Smith 123 Any St 1 57X452
AB456 John Smith 123 Any St 1 78Y897
DB678 Jane Smith 456 Road 4 89G654

Thanks in advance for any help offered.

Hi JD,

Thanks for this challenge

With the current version 2.1.0 this is very tricky! Soon a new version 2.1.1 is available. In this version you can store parameters/values and use it in other heaters or function.

With this new feature and a short .NET script this is very easy.

I’ve made a little example to explain how it works. Please have a look to the attachment.

Note: Please download first the beta version of FlowHeater here: Download BETA Version . This example works only with version 2.1.1 (coming soon) and later or with the beta version!

How works this example?

1. We have to configure the TextFile Adapter on the READ side to read just hole whole lines
2. Then we need a .NET Script Heater to decide what type of line we have (H = HEADER or D = Detail). If we have a header line the script store this line into a parameter value for later use and tell the Filter Heater to filter this line/row.
3. The SubString Heater gets the detail information for each field on the WRITE side.
4. The Get Parameter Heater gets the last stored header line from the script. The SubString Heater extracts the "ShipTo Address" for each detail line.

Robert,

Thanks for your reply. Sorry I have not responded but I’ve been very busy and traveling.

At first glance it does ignore the header rows. But, I need it to also put the header row information in where the ID numbers are equal. I have a few acceptable ways of dealing with this for now while the volume is low. However, we’re looking for an increase in about six months. I just downloaded version 2.2.1. I see a few new heaters I must look at. I’ll let you know how it goes.

Thanks again for all your help. JD