cancel
Showing results for 
Search instead for 
Did you mean: 

Record Count + Time Stamp verification for 5 files to process

Former Member
0 Kudos

Hi All,

I ahve five files of different structure with different file names... and each file name has same timestamp... and each file has a tailor starting with T followed by count..

Now I need to check the two checks before actual processing the file:

Open each file and check the record count with number followed by T if it matches then only process

Check all five files with the time stamp...if they have the same then only process

If any of these two checks fails I need to send a mail.... and no files I need to process...

Can any one let me know the steps I need to do in DS.

Many Thanks

Rajeev

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

For all this sort of thing, there are dozens of ways to handle.

Off the top of my head, for the record count check, you could (for instance):

For table #1, pull it into a dataflow. I assume you have a fixed, usable schema to the input table, but modify the schema of the first column to be able to absorb, as the value of the first field, the contents of that "footer" record, the one that begins with a "T" and contains the record count. Add a identity field to the schema -- call it, say, "i' -- and number the rows (just use gen_row_num()).

Former Member
0 Kudos

Split the data stream into a query transform called "Last_Record" in which you have a single column, "last_record," int, with mapping max(i). Join this back to the query transform in which you added the identity column, with no join condition (in 3.x, no WHERE clause) (this will perform a cross join or Cartesian product, which is usually b-a-d, but, in this case, fine -- only have a single column and single row in Last_Record. Add all the fields to this new query.

Split the data stream again into two pieces: the "last record" and everything else. You id the last record by looking for i = last_record, and all the others with i [not equal to] last_record. Send the two streams off to two different tables. In the stream which holds the footer record -- the one with the record count inside -- parse the field and extract the numeric count and send it to some numeric field -- we'll call it "record_count".

Former Member
0 Kudos

Outside this dataflow, in a script, you can collect the "real" record count and the declared record count into two variables and then compare them.

Former Member
0 Kudos

You'd do this for all five of your tables. Do all the dataflows first, upstream of the script. Then, with your 10 variables in hand, I assume you can figure out how to test for inequality and script-out an email alert.

Former Member
0 Kudos

For the datetime stamps on the files, collect the five different file properties into variables using get_file_attribute(), and then compare.

Best wishes,

Jeff Prenevost

Former Member
0 Kudos

Hi Jeff Prenevost ,

I am not clear what you mean? can you please give me abit clear step by step how to fullfill the requirement in DS?

I have the following source file

H timestamp

rajeev,B,C,D

Paul,D,E,F

T,2

Now I need to count in between H and T and match with the value 2 (This is the value we can get from the row which has the identifier T in the file)

I tried with the flat file but I cant able to get this data in to DS as it says invalid data because last row is incomplete as per the delimilater file format/strcutrue is concern.... Can any one let me know how can I get this value?

Many Thanks

Rajeev

Edited by: rajeev raj on Jun 25, 2011 9:41 AM

Former Member
0 Kudos

As I believe someone else suggested, you can make three different formats to read a given file -- one for the header, one for the detail records, and one for the footer. When reading the file with the "footer" format, add a filter (if possible) to just get the footer record.

Note, too, that it's always possible, albeit a pain in the butt, to make a file import format which brings in each record into one single varchar() field. After you suck them all in, you can parse the fields out, and identify your footer record.

Best wishes,

Jeff Prenevost

Answers (0)