Skip to Content
Author's profile photo Arijit Mukherjee

File LookUP using UDF and Graphical Variable

Like other LookUp requirements(RFC/JDBC), we sometime get requirements where we need to LookUp a file from the PI/PO mapping and get the required data. The below wiki is a very good example that demonstrate the process to achieve this.

File Lookup in UDF – Process Integration – SCN Wiki

But in case you need to lookup the file where there can be thousands/millions of records and you need to lookup the content of the file for each record. In such cases, the mapping can run for hours if you try to connect the FTP for each record to get the corresponding data!

So what would be the workaround. The  new feature , in the Message Mapping tool of PI 7.1.,called Graphical Variable along with the WIKI mentioned earlier can make our life easy. How? Instead of checking the value for each record, we take all the contents of the File in the Graphical Variable and use them as and when required!

So in this case, the approach would be similar to below.

First we create a Graphical Variable in the target structure.

/wp-content/uploads/2015/12/pic_841927.jpg

Map that Variable like below.

/wp-content/uploads/2015/12/pic_841927.jpg

Then we write a code like below which is going to pick all the contents from the file(second function in above pic). Please make sure that the execution type is “All Values of Queue” if you use the below code!

/wp-content/uploads/2015/12/pic_841927.jpg

String server = “NAME OF THE FTP SERVER“;

int port = “PORT OF THE FTP SERVER“;

String user = “USER NAME“;

String pass = “PASSWORD“;

String seller = “”;

      

FTPClient ftpClient = new FTPClient();

    try {

         ftpClient.connect(server, port);

         ftpClient.login(user, pass);

         ftpClient.enterLocalPassiveMode();

         String remoteFile1 = “FILE PATH/FILENAME WITH EXTENSION“;

         InputStream inputStream = ftpClient.retrieveFileStream(remoteFile1);

         InputStreamReader reader = new InputStreamReader(inputStream);

    BufferedReader buffer = new BufferedReader(reader);

          

    String read;

        for(String line=buffer.readLine();line!=null;line=buffer.readLine())

        {

         result.addValue(line);

        }

     

        buffer.close();

        } catch (IOException ex) {

            System.out.println(“Error: ” + ex.getMessage());

            ex.printStackTrace();

        } finally {

            try {

                if (ftpClient.isConnected()) {

                    ftpClient.logout();

                    ftpClient.disconnect();

                }

            } catch (IOException ex) {

                ex.printStackTrace();

            }

        }

Now once the mapping runs, the Variable has got all the content from the FTP.

/wp-content/uploads/2015/12/pic_841927.jpg

Now you can use them as and when required without doing another polling to the FTP server! Below is an example.

In case you need further information on Graphical Variable, please seeSAP PI 7.1 Mapping Enhancements Series: Using Graphical Variable

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice blog and very usefull...

      Author's profile photo Former Member
      Former Member

      Hello Arijit,

      Interesting blog. I have a similar requirement wherein i need to read file name of a file from particular location on SFTP server. I was wondering if ftpClient object will work with SFTP server.

      Also in order to use this ftpClient object which packages need to be imported?

      Thanks in advance for your help

      Anand

      Author's profile photo Arijit Mukherjee
      Arijit Mukherjee
      Blog Post Author

      Hi Anand,

      I am sorry...I also need to check the JAVA for SFTP.

      Thanks

      Arijit