Difficulty looping through Excel files

[#1]

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?

[swuehl]

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.

[Aurelien_Martinez]

Hi,

Have you tried a Replace()?

FROM

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

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

Regards,

Aurélien

[#4]

We tried using Purge rather than replace, but this removed everything after the last slash in the path. I've had a really promising reply from swuehl, which I'll try in the morning

Thanks for your time

Steve

[dvasseur]

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

[pgrenier]

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