Sequential File

kirands04 · Post by **kirands04** » Fri Jan 04, 2013 12:18 am

Hello Friends,

I got the below requirement for our project.

1) We receive 6 sequential files with only one column (simple text)

2)Every row of this file will have two codes. These codes will be there in the fixed positions of every line

ex: In every line, postion 5-9 will give one code ; 34-38 will have another code

3) We need to extarct these code and do some transactions on them and writhe result to another sequential file.

4) Name of the input file and ouput file should be the same.

Here my issue is how to read the input file once it reached my source folder and create another sequential with the same name in my target folder. Also I need to give the same name in the sequential stage (for writing the data to the file) at the output.

hmvikram · Post by **hmvikram** » Fri Jan 04, 2013 12:57 am

Hi Kiran,

Follow the below steps.
1. In sequential stage, define only 1 column. Say col with datatype as varchar. Specify delimiter as "None" under format tab.
2. Use a transformer and extract the codes by specifying the postion and length of the string to extracted in derivation.

ex: col[5,5] will extract code from position 5-9.
col[34,5] will extract code from position 34-38

3. Do the required transformations using codes you extracted and use a sequential file stage as target to write the output.

kirands04 · Post by **kirands04** » Fri Jan 04, 2013 1:19 am

Hello Vikram,

Thank you for the apply. But my actual problem is with the output file name. Output file name should be the same as the input file. So how to read the input/source file name and give the name in the sequentail stage (for writing the data to output file)

hmvikram · Post by **hmvikram** » Fri Jan 04, 2013 1:36 am

Are you trying to write the output file to the directory where your source file resides?

kirands04 · Post by **kirands04** » Fri Jan 04, 2013 1:47 am

No Vikram.. that will be in a different folder.. I've two folders in my DS server.. input files comes into "Source" folder by every tuesday morning. I need to read those files and make the transformations and write to the output file in "Target" folder. here output file name should be the same as the input filename.

chetan.c · Post by **chetan.c** » Fri Jan 04, 2013 2:03 am

See if this helps.

viewtopic.php?t=146613

The filename can be read in the sequential file stage using the filename column.

chulett · Post by **chulett** » Fri Jan 04, 2013 8:19 am

Sounds like you may need to look into a looping sequence job. Basically, a process would be used to generate a delimited list of the filenames to process and then the job you build to process them is 'looped'. Meaning it is run once for each filename and the filename is passed in as a job parameter. Since you have a job parameter with the name of the file, you can easily leverage it to name the output file as well.

Let us know if you'd like to pursue that methodology.

kirands04 · Post by **kirands04** » Sun Jan 06, 2013 11:20 pm

Hi Craig,

I'm designing exactly in the same way as you said.

1) First i'll calculate the number of files to be processed and pass it to start loop activity. Now the loop runs for "n" times.

2)In each iteration, we need to read the nth (n = iteration number) file name and pass it as a parameter to the Job activity.

3) Job activity takes file name as a parameter and completes my Job.

In the above design I have solution to 1st and 3rd points. But i am struck in the 2nd point. I mean how to read the name of 1st file in the 1st iteration and send it as a parameter; 2nd file name in the 2nd iteration and so on.. Please help me in this step.... thank you very much everyone for helping me..

chulett · Post by **chulett** » Mon Jan 07, 2013 7:06 am

Search here or in your docs for the $Counter variable that is automatically populated per interation from your delimited list. Assuming the StartLoop stage has such a list, then pass "StartLoop_StageName.$Counter" as the filename inside the loop.

ShaneMuir · Post by **ShaneMuir** » Mon Jan 07, 2013 10:12 am

Use a combination of the a sequential file stage and an external target stage.

For the input use a sequential file stage using a file pattern. Include a column for the file name itself.

Extract your required values and perform whatever transforms you need to perform.

Prior to output concatenate all the fields into a single column with a chosen delimiter. Ensure that a fully qualified file name is the first value.

Pass to an external target stage. I think you would want to look at sorting the incoming data here also by the 1st column. But I am not sure its entirely necessary.

It in the destination program of the external target stage you would set your Target method to Specific program and use the following code.

Code: Select all

awk '{nPosField1=index($0,",");print substr($0,nPosField1+1)>substr($0,1,nPosField1-1)}'

This should create a file with the required values and then append the values from the job to the named file. This process can create and write many output files at a time.

kirands04 · Post by **kirands04** » Tue Jan 08, 2013 9:55 pm

Hello Craig,

Thanks for the suggestion. i got the clue to use $counter. Most of your information is not visible to me. Anyhow let me think how to use this $counter and complete my design. If I struck again in any place, I'll post here..

ray.wurlod · Post by **ray.wurlod** » Tue Jan 08, 2013 11:31 pm

Why not invest in a premium membership (less than Rs12 per day) that would allow you to read the entirety of premium posts and, as a bonus, contribute to keeping DSXchange alive. Premium memberships are the model by which the hosting and bandwidth costs of DSXchange are met.

If you are a DataStage Developer, it may even be a valid business expense for tax purposes (though I don't profess to know Indian tax law).