Sub for reading File()-Data from Table

[GeorgeQV]

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!

[settu_periasamy]

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;

[jonathandienst · Accepted solution]

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.

[GeorgeQV]

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