5 Replies Latest reply: Oct 15, 2013 11:35 AM by Philippe Grenier RSS

    Difficulty looping through Excel files

      I'm sure somebody will be able to help us with this........

       

      The dashboard we've inherited uses the following code

       

      /*Live*/FOR EACH SQR_SpreadSheet IN FileList('..\..\Excel Documents\MetroPerformanceRegime\SQR\*.xlsx')

             FindHeader_StepOne:
            
      LOAD
               
      D                              AS [DATE CHECKED],
               
      RowNo()                        AS HeaderPosition
            
      FROM
            
      $(SQR_SpreadSheet)
             (
      ooxml, no labels, header is 1 lines, table is [CARS ])
             ;

       

      It has been working OK on the server, but we're debugging another issue.

      When we run it from our desktop, it tries to evalueat SQR_Spreadsheet. It fails because it has inserted ~$ between the path and the filename and can't find that file.

       

      I'm sure there's a simple solution - can anyone advise?

        • Re: Difficulty looping through Excel files
          Stefan Wühl

          Seems to me as if you have one of these excel files open in Excel, this creates a temp. ~$Filename.xlsx file, which will be part of the FileList() then, but QV can't open this file.

           

          Please assure that you won't have a file open when using the FileList() function, if necessary, remove the ~$.. files if they are relicts only.

           

          Or try to filter the pathname, that you won't try to open these files.

            • Re: Difficulty looping through Excel files
              David Vasseur

              Based on swuehl's idea, add this in your for each loop:

               

              LET vFileName = SubField('$(SQR_SpreadSheet)', '\', -1);

              If Left('$(vFileName)', 2) <> '~$' Then

                FindHeader_StepOne:

                     LOAD

                        D                              AS [DATE CHECKED],

                        RowNo()                        AS HeaderPosition

                     FROM $(SQR_SpreadSheet) (ooxml, no labels, header is 1 lines, table is [CARS ])

                     ;

              End If

            • Re: Difficulty looping through Excel files
              Aurélien Martinez

              Hi,

               

              Have you tried a Replace()?

               

              FROM

               

              Replace($(SQR_SpreadSheet), '~$', '')

              (ooxml, no labels, header is 1 lines, table is [CARS ])
              ;


              Regards,

              Aurélien

              • Re: Difficulty looping through Excel files
                Philippe Grenier

                Hello Stephen,

                 

                I believe with a simple check statement, you might be able to ignore those temporary Excel files:

                 

                FOR Each SQR_SpreadSheet IN FileList('..\..\Excel Documents\MetroPerformanceRegime\SQR\*.xlsx')

                 

                   IF Index('$(SQR_SpreadSheet)', '~$') = 0 then

                       FindHeader_StepOne:

                       LOAD

                          D                              AS [DATE CHECKED],

                          RowNo()                        AS HeaderPosition

                       FROM

                       $(SQR_SpreadSheet)

                       (ooxml, no labels, header is 1 lines, table is [CARS ])

                       ;

                   ENDIF

                NEXT

                 

                Regards,

                 

                Philippe