Merge Sequential Files

[Champa]

Team,

I have 13 files with 2 columns. In each file first column is id & the second is count. What I want to do is combine all 13 files group by first column & sum the count. I tried using a link collector to combine all the files into a single file & then used an aggregator to sum up. It does not work. Any suggestions?

Thanks

[ray.wurlod]

Use one Sequential file stage with a filter command that uses cat (UNIX) or type or copy (Windows) to load all the rows from all the files into a single stream. Run this through the Aggregator stage. Even better would be to run the output of cat through sort (on the key) as part of the filter; this will mean your Aggregator stage runs MUCH faster.

[Champa]

Ray,

Can you please expand on your first sentence.

Thanks.

[rasi]

Champa

Use one Sequential file stage with a filter command the uses cat to load all the rows from all the files into a single stream

You can use filter command inside your Sequential File Stage. By default it is not enabled. Go to the Stage tab inside Sequential File stage and tick "Stage uses filter commands". This will allow you to use filter commands as Ray mentioned.

[Champa]

Thanks Siva

I will try it out tomorrow

[I_Server_Whale]

Once you enable the "Stage uses filter commands", you will be able pass on any filter command on the 'Outputs' tab of your sequential file stage.

For your case,

As Ray suggested,

you can concatenate(merge) your 13 files this way to one final file:

Code: Select all

cat  file1  file2 ....... file13  finalfile

And pipe the output of this merge to a sort command for the aggregator stage to run faster.

HTH,
Naveen.

[lstsaur]

Hi Guys,
Champa's job is running on Windows platform; there is no "CAT" command to concatnate the files. Am I missing something?

[kumar_s]

Hi Guys,
Champa's job is running on Windows platform; there is no "CAT" command to concatnate the files. Am I missing something?

Then 'TYPE' would help.

[I_Server_Whale]

That's right. Somehow we all have overlooked it. Thanks for correcting us lstsaur.

As kumar suggested, we can use TYPE instead of 'cat' or we can also use 'COPY' to merge multiple files in DOS.

Thanks,
Naveen.

[vsi]

I have done that using TYPE as well as COPY command. It didnt work.

I used the the following in filter command

COPY c:\temp\items.txt c:\temp\items1.txt c:\temp\to.txt


which resulted in the following error


Gave the below error :
uniting..Transformer_2: |uniting..Sequential_File_0.DSLink3: DSD.SEQOpen No Filename to open..|

[ray.wurlod]

What file name did you provide for the Sequential File stage to read?

[ray.wurlod]

When you're using a filter, the Sequential File stage reads stdout from the filter directly (so you don't terminate the type command with a target file name, just type directly onto stdout). DataStage consumes these rows directly but must - for some strange reason - have the File Name property supplied with a value. It can be any valid file name, such as /dev/null (UNIX) or .\NUL (Windows).

[ogmios]

A little bit off-topic but what I would do for the original problem would be to load all files to a database table and then use aggregation via SQL on the database using a DB2/Oracle stage. The aggregator stage doesn't work well with large input sets.

Ogmios

[vsi]

When you're using a filter, the Sequential File stage reads stdout from the filter directly (so you don't terminate the type command with a target file name, just type directly onto stdout). DataStage consumes these rows directly but must - for some strange reason - have the File Name property supplied with a value. It can be any valid file name, such as /dev/null (UNIX) or .\NUL (Windows).

do u mean stdout or stdin? i didnt understand about the termination and consumption?

scenario is like:

seq_source-->X-->seq_target

i have 2 files c:\source1.txt,c:\source2.txt

i need to copy in c:\target.txt

What do i need to write in Filter Command and File Name in Windows and in UNIX.

[ray.wurlod]

Effectively what happens is filter_cmd | seq_source --> xfmr --> seq_target (yes, I DO mean stdout of the filter command)