Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
Contributor

Using Excel FileName in LoadScript

Good day!

In script i load excel files from some directory:

FOR Each File in filelist ('$(DataPath)Files_*.xlsx')

Main:

  LOAD

<>

FROM

  '$(File)'

(ooxml, embedded labels, table is Sheet1);

NEXT File;

I need to use FileName for each file in load directory..

Such as:

FOR Each File in filelist ('$(DataPath)Files_*.xlsx')

Main:

  LOAD

if(left(FileName,1)='f', 'Value1','Others') as DataType

FROM

  '$(File)'

(ooxml, embedded labels, table is Sheet1);

NEXT File;

  

Is this possible?

Please, help

Thanks.

3 Replies
eespiritu
Contributor

Re: Using Excel FileName in LoadScript

Hi Andrey,

First, you need load the FileName for each file. Use this as example:

let v_RootS= chr(39) & 'PATH' & chr(39) ;

Sub DoDirShare(Root)

  For each Ext in '.xlsx'

  For each File in Filelist (Root&'\*.xlsx')

  let FileExtension = lower(mid(File,Index(File,'.',-1)+1));

FileLoad:

  Load

  Name,

  Right(Name,Len(Name)-Index(Name,'\',-1)) AS File,

  Left(Name,Index(Name,'\',-1)) AS FilePath;

  Load * Inline "

  Name

  $(File)";

  Next File

  Next Ext

End sub

call DoDirShare ($(v_RootS));

NewTable:

Load    File,

  FilePath,

  Name as FullName

Resident FileLoad;

Let NumberOfRecords=NoOfRows('NewTable')-1;

For Count=0 to $(NumberOfRecords)

  Let XFile=peek('FullName',$(Count),'NewTable');

  Let XName=peek('File',$(Count),'NewTable');

  FinalTable:

  LOAD

if(left($(XFile),1)='f', 'Value1','Others') as DataType

  FROM [$(XFile)] (ooxml, embedded labels, table is Sheet1);

Next

Regards,

MVP
MVP

Re: Using Excel FileName in LoadScript

use filename() function

directory;

set vPath='.\excel load multiple files*.xlsx';

table: load '' as dropme autogenerate 0;

For Each vFile in FileList('$(vPath)')

  

  concatenate(table)

    LOAD  

    *,

    filename() as FileName,

    left(filename(), 1) as FileName1,

    if(left(Right(filename(), 6),1)='a', 'a', 'other') as FileName2

    From [$(vFile)]

    (ooxml, embedded labels, table is Sheet1);

next;

Highlighted
qvraj123
Contributor II

Re: Using Excel FileName in LoadScript

try something similar to this

Sub DoDir (Root)  
For Each Ext In 'csv'
         For Each File In FileList (Root & '\*.' & Ext) 
                    List_of_Changes_Files:  
                    Load  if(wildmatch('$(File)', '*Change_Request*'), '$(File)') as Name,  
                    FileTime('$(File)') as FileTime,
                    RangeSum(Peek('FileCount')) as FileCount  
                    Autogenerate 1;  
           Next File  
    Next Ext  
     For Each Dir In DirList (Root & '\*')
           Call DoDir (Dir)  
      Next Dir  
End Sub 

//Call subroutine defined above with the complete folder path as variable
Call DoDir ('$(vDataLocation)$(vDataChanges)');

//Exclude the Microsoft Temporary Files; Exclude any file name starting with the ~ character
Exclude_Temp_Files: 
LOAD
  Name AS File_Name,
  FileTime AS File_Time,
  FileCount  AS File_Count
Resident List_of_Changes_Files
where index(Name,'~') = 0
ORDER BY FileTime DESC;

////Get the most recent 3 files using the FileTIme or date modified - load the data from the most recent 3 files into the QVD; in Change Request dashboard we only need the 3 months of data
Three_Recent_Files: 
FIRST 3 LOAD
  File_Name AS Three_Recent_File_Names
Resident Exclude_Temp_Files
ORDER BY File_Time DESC;

//use variables to store the most recent files file names
LET vFileToLoad1 = FieldValue('Three_Recent_File_Names', 1);  
LET vFileToLoad2 = FieldValue('Three_Recent_File_Names', 2);  
LET vFileToLoad3 = FieldValue('Three_Recent_File_Names', 3);