3 Replies Latest reply: Feb 26, 2016 5:00 AM by Georg Gottsmann RSS

    Sub for reading File()-Data from Table

    Georg Gottsmann

      Dear all,

       

      I would like to use the File() functions for extracting data about the table's source like Path, Extension and last date of change. However, most of the functions only work in the current LOAD statement.

       

      Example:

      Load

           A,

           B,

           FileTime() as FileTime,

           FileName() as FileName

      From

      \..\file.txt;

       

      Now it would be great to have a reusable sub which can read these attributes from every source. But then I have to leave the current LOAD statement and the File() functions mostly return NULL. I tried using a preceding LOAD:

       

      Example:

      Load *,

           FileTime() as FileTime,

           FileName() as FileName;

      Load

           A,

           B,

      From

      \..\file.txt;

       

      And putting this into a subroutine:

       

      Example:

      Sub GetFileData

      Load *,

           FileTime() as FileTime,

           FileName() as FileName;

      End Sub

       

      Call GetFileData;

      Load

           A,

           B,

      From

      \..\file.txt;

       

      This will add the File() fields to every row of the table. But I would like to create another table or read this data into variables. The problem is still: Everything is external from the current LOAD and the File() functions return NULL. Also, I only need one row of this data, not for all lines of the table. Does anybody know a way to do it? I just want to know where my data comes from, when it was modified, the name of the txt-file etc.

       

      Thank you in advance!

        • Re: Sub for reading File()-Data from Table
          Settu Periyasamy

          Hi,

          May be try to use Distinct in your subroutine and give the TableName(Here : FileList) also.. Like


          Sub GetFileData

          FileList:
               
          Load Distinct FileTime() as FileTime,
                           
          FileName() as FileName;
          End Sub


          Call GetFileData;


          Table1:
          LOAD A,
               
          B   
          FROM
          SourceFile1;

          Call GetFileData;
          LOAD A,
               
          B   
          FROM

          SourceFile2;

          • Re: Sub for reading File()-Data from Table
            Jonathan Dienst

            The preceding load works on the output of the LOAD below, so none of these options (apart from your original script snippet) will work. To read only the attributes, use something like:

             

            Attribs:

            First 1 LOAD

                 FileTime() As FileTime,

                 FileName() As FileName

            FROM  C:\Data\*.xls

            (....)

             

            You can Peek() these values into variables as required.

              • Re: Sub for reading File()-Data from Table
                Georg Gottsmann

                Thank you Jonathan, the statement FIRST 1 LOAD and the advise to read the same file again led me to the following subroutine. You just need to save the Table Name and the Source File Path to open the connection once again and load one single record with the file attributes:

                 

                SUB GetFileData(vL.TableName, vL.SourceFilePath)
                
                    IF NoOfRows('FileData') > 0 THEN
                    
                        Concatenate (FileData)
                        
                        FileData:
                        
                        First 1 LOAD
                        
                            '$(vL.TableName)' as TableName,
                        
                             FileTime() As FileTime,
                        
                             FileName() As FileName
                        
                        FROM $(vL.SourceFilePath);
                        
                    ELSE
                        
                        FileData:
                        
                        First 1 LOAD
                        
                             '$(vL.TableName)' as TableName,
                        
                             FileTime() As FileTime,
                        
                             FileName() As FileName
                        
                        FROM $(vL.SourceFilePath);
                    
                    ENDIF
                
                END SUB