loading as per date value

[dr46014]

hi all
i have got a flat file to load into a table along with date..the flat file has different segments where the date value and its corresponding set of records is given..then another date value and another set of records...how to read such a file..
i m giving the format of file below

generation date:11/11/2006
65896565,A123F,C112K
89645235,A123F,C112K
.
.
generation date:12/12/2006
65896565,A123F,C112K
89645235,A123F,C112K
.
.
.

[narasimha]

Welcome Aboard

A complicated requirement for your first post!
How do you want your output to be.
Can you give a sample of how your output should look like?
We can go ahead from there.

[dr46014]

thanks for your response..
the output should be a table consisting of 5 columns.3 are the fields given in the flat file.the 4th one is the generation date which is given as the header of rhe block of records and the 5th one is the load date.
plz suggest me an approach to solve it

[I_Server_Whale]

hi all
i have got a flat file to load into a table along with date..the flat file has different segments where the date value and its corresponding set of records is given..then another date value and another set of records...how to read such a file..
i m giving the format of file below

generation date:11/11/2006
65896565,A123F,C112K
89645235,A123F,C112K
.
.
generation date:12/12/2006
65896565,A123F,C112K
89645235,A123F,C112K
.
.
.

Welcome aboard

I appreciate the way you framed your question and also the neat & clean sample file.

It is pretty easy to read such a file. Use a sequential file stage, read the incoming record as one field. Make sure that you define the length of this field equal to the maximum length of your incoming record.

Later in your transformation. you can use the field function to retrieve relevant information based on your output requirements.

Whale.

[kumar_s]

As mentioned by Whale, read it as single varchar field, parse it in a transformer. Check for "generation date:" and store the date into a stage variable, and populate this variable in 4th column until you get a records with "generation date:". The 5th can be populated with SYSDATE or Date() in datastage.

[DSguru2B]

You can do this by reading the entire record as one column and then split it inside the transformer as Whale suggested.
Define a stage variable

Code: Select all

GenDate  |   if in.Link Matches 'generation...' then Field(in.Link,":",2)   
                   else GenDate

Now your columns will be

Code: Select all

Col1 | Field(in.Link,",",1)
Col2 | Field(in.Link,",",2)
Col3 | Field(in.Link,",",3)
Col4 | GenDate
Col5 | DSJobStartDate

Now your first three columns will be null for the line 'Generation Date:...'
So you need to constraint it by putting a second transformer infront of this transformer and specify a constraint

Code: Select all

IsNull(link.Col1) = 0

I think that should work.

[dr46014]

thaks all you people for your immediate response.i hav a small doubt that how can read the file when i encounter the second generation value...
if u people suggest me code of the field function it will be very helpful

this is a really awesome community....i got the solution within 30mins..
one again thanks to all u people

[narasimha]

if u people suggest me code of the field function it will be very helpful

Field is an inbuilt function in Datastage.

This forum has a nice search functionality too which you can use to get most of your solutions.

[DSguru2B]

I gave you a complete solution as a welcome. The stage variable will take care of the second, third and so on, all the generation values. Make sure the first line in your file is Generation Date.

[dr46014]

i got the point..
now i ll implement it..
i i have any further queries i ll ask you people