3 Replies Latest reply: Mar 4, 2015 1:47 PM by Rajender Thadakamadla RSS

    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.

        • Re: Using Excel FileName in LoadScript
          Enrique Espiritu

          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,

          • Re: Using Excel FileName in LoadScript
            Massimo Grossi

            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;

            • Re: Using Excel FileName in LoadScript
              Rajender Thadakamadla

              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);