26 Replies Latest reply: Apr 21, 2017 9:26 AM by Arghya Ray RSS

    How to fetch Files from an FTP Location?

    Arghya Ray

      Hi,

      I want to fetch an excel file from an FTP location present under the folder structure say /home/recent. The server name is suppose server_123.xyz.com and password is pqr@12. The file name is abc.xlsx. How to fetch this file from the specified path to my Qlikview script? The excel file has two columns say 'Attribute' and 'Values'. How to load the data from these two columns after fetching the excel file from the FTP location? Can anyone help me out with this. Thanks!!!!

        • Re: How to fetch Files from an FTP Location?
          Rahul Pawar

          Hello Arghya,

           

          Trust that you are doing well!

           

          To fetch an excel file from an FTP location please follow below steps:

          1. Go to Edit Script (Ctrl + E), Select Use FTP and Click on Table Files button.

          2. It will pop-up Open Internet Files window. Enter server_123.xyz.com as FTP Server Address, required credentials and hit Connect button.

          3. Once Connected, navigate to the /home/recent in left hand side window

          4. Select abc.xlsx file from right hand side window and Click Open.

          5. It will open File Wizard window select respective parameter and Click finish.


          Hope this will be helpful.


          Regards!

          Rahul

            • Re: How to fetch Files from an FTP Location?
              Arghya Ray

              Thanks Rahul for your help!! It worked.

              Now suppose if i have many file names of the format abc_dddmmyyyy.txt in a folder of the FTP location and I am required to fetch the latest file. How would I proceed to achieve this?

              Need your suggestion to solve the problem. Thanks once again!!!

                • Re: How to fetch Files from an FTP Location?
                  Rahul Pawar

                  Hello Arghya,

                   

                  Thank you for validating the inputs.

                   

                  As a work around, you need to write a batch file which will fetch all the files and place it in a file. Post that you can load that file in QlikView to get the name of Latest file name. Post that using variable load the latest file (refer below sample script).


                  tmpFileInfo:
                  LOAD MAX(Num(Date#(Right(FileName,8),'DDMMYYYY'))) AS LatestFileName
                  FROM
                  C:\FileNames.txt
                  (txt, codepage is 1252, no labels, delimiter is ',', msq);
                  
                  LET vLatestFile = 'ftp://server_123.xyz.com/home/recent/'& 'abc_' & Date(Peek(LatestFileName, 0, 'tmpFileInfo'),'DDMMYYYY') & '.txt';
                  
                  FileInfo:
                  LOAD Filed1,
                       Filed2,
                       .....,
                       FieldN
                  FROM
                  $(vLatestFile)
                  (html, codepage is 1252, embedded labels, table is @1);
                  
                  
                  
                  

                  Hope this will be helpful.


                  Regards!

                  Rahul