topic Re: Difficulty looping through Excel files in QlikView

Difficulty looping through Excel files

Tue, 15 Oct 2013 14:06:41 GMT

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

swuehl — Tue, 15 Oct 2013 14:58:49 GMT

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

Aurelien_Martinez — Tue, 15 Oct 2013 15:00:36 GMT

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

Tue, 15 Oct 2013 15:14:46 GMT

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

Re: Difficulty looping through Excel files

dvasseur — Tue, 15 Oct 2013 15:25:55 GMT

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

pgrenier — Tue, 15 Oct 2013 15:35:46 GMT

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

Regards,

Philippe