Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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?

5 Replies
MVP
MVP

Re: Difficulty looping through Excel files

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.

amartinez35
Valued Contributor

Re: Difficulty looping through Excel files

Hi,

Have you tried a Replace()?

FROM

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

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


Regards,

Aurélien

Not applicable

Re: Difficulty looping through Excel files

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
Contributor II

Re: Difficulty looping through Excel files

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
Contributor III

Re: Difficulty looping through Excel files

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