Need to validate a CSV file based on the first field of the last row

[Former Member]

Hello Experts

I have a scenario wherein a CSV file has to picked up from a SFTP and it has to be pushed into R/3 through proxy.

The first field of the last row of the CSV file has to have a value "EOF".If the first field of the last row doesnt have this value then the file should not be picked ,It should be placed in an error folder.

I will attach the screen shot  for your reference.

Please let me know if any more clarification is required.

Thanks in advance

Advit ramesh

[bhavesh_kantilal · Accepted solution]

Hello Advit,

• If your file is to be considered as a CSV File, then there is no direct way to do this. You need to read this file, and then using a Enhanced Receiver Determination identify if the EOF record exists and then route / process file accordingly.
• I looked at your file and what I notice is while the File is a CSV File, the fields seem to all have a fixed field length. If we can consider this a fixed field length file ,i.e, all records have the same length, then we may use the KeyField option and then make the EOD as a separate Record and if the same doesnt occur make content conversion fail etc.

Ideally I would still go ahead with considering this a CSV file , reading CSV to XML and then using Enhanced Receiver Determination.

Regards

Bhavesh

[Former Member · Accepted solution]

Thanks Bhavesh

[apu_das2]

Hi Ramesh,

There are 3 ways as per my understanding -

1. In PI source structure define only one parameter to take each line of CSV under this parameter using     FCC like       -

    Structure -

        Record   0...unbound

              Text    0...unbound

    FCC - 

              Record.fieldName -  Text

              Record.fieldSeparator -  'nl'

              Record.endSeparator -   'nl'

   So in PI you will get value like -

   <Record>

     <Text>UserID,UserName,Closing Balance,.......</Text>

  </Record>

  <Record>

     <Text>DLTR0000333153,test.................... </Text>

  </Record>

  .

  .

  .

  .

  <Record>

     <Text>EOF,,,,................................. </Text>

  </Record>

Now take the whole thing in a UDF after removing context. Here, you can take the count of records and read the last one first. Split that depending on comma and store it in a array. Check first value of array - arr[0] . If its not EOF then through an exception else map it to the target structure taking multiple result List.

But here PI will pick each file to validate the condition.

2. Go for OS Script. As your source is SFTP so you need to take it first to an NFS location and then need to go for the command prompt solution . But here one pipeline will increase.

3. Some short on Custom Module can be written for that.

Thanks,

Apu

[Former Member]

Thanks Apu