topic Re: Qlik Sense - Loop through Excel Files and Worksheets in App Development

Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Wed, 18 May 2016 12:53:12 GMT

Hi all,

I have a QlikView model which I'm busy converting to Qlik Sense. I'm now stuck and would like to find out if someone has resolved this and/or if there is a resolution.

In QlikView I read all .xlsx files from a specific directory (these files are stored here every month). Then I read all the sheets of each of those Excel files but make use of the "SQLTABLES" to access those Worksheets. In other words, I loop through all Excel files in a specific directory and then read all columns of all Worksheets within each Excel file. And there is no standard this is all dynamic.

Now, I can do this in Sense by creating an ODBC to the Excel file (which is now a LIB connections) but I cannot do this dynamically. So I can loop through the Excel files (the directory), and I can loop through each Worksheet, but ONLY for 1 Excel files which you have to set up on ODBC.

Can someone please tell me if this is actually possible and how this can be achieved in Sense?

Thanks you.

Re: Qlik Sense - Loop through Excel Files and Worksheets

Anonymous — Mon, 20 Jun 2016 16:36:40 GMT

Hi..

Not quite sure this is what you want but here it goes...

try to make sense with your excel files. For example, gave them the same inicial name: ExcelFile[1], ExcelFile[2], etc...

And then on data load editor put FROM [lib://NS/ExcelFile*.xlsx]

This should load all files in directory that start with ExcelFile

And then give your sheets the same name too.

Example: ExcelFileSheet

FROM [lib://NS/ExcelFile*.xlsx]

(ooxml, embedded labels, header is 5 lines, table is ExcelFileSheet

,...

This is suppose to make you data load editor, to load all files, whose name starts with ExcelFile, and all sheets with name EscelFileSheet.

Let me know if there's something you didn´t understand

Regards,

Sílvia Ganhão

Re: Qlik Sense - Loop through Excel Files and Worksheets

undergrinder — Tue, 21 Jun 2016 07:33:42 GMT

Hi Rbert,

Try this:

sub ReadMultipleExcel (Root)

For Each File in filelist (Root&'\*.xlsx')

YourTable:

Load * FROM [$(File)] (ooxml, embedded labels, table is YourSheet)

Next File

End Sub

Call ReadMultipleExcel ('lib://YourFiles')

It will loop through every xlsx file and read it's content.

As far as I get your state, you have same structured files, but other timestamp in filename.

The $(File) variable contains the filename, you can extract the date from it.

Re: Qlik Sense - Loop through Excel Files and Worksheets

brunobertels — Tue, 21 Jun 2016 11:33:09 GMT

Hi Robert

Have a look to this doc , it helped me for the same case :

https://community.qlik.com/docs/DOC-7860

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Tue, 21 Jun 2016 21:12:09 GMT

Hi all, thanks for all the feedback. But there is one thing I think you miss in my question. The above all works 100% in QlikView. My questions is has someone actual managed to get this done in Qlik Sense. I'm using Qlik Sense quite a lot now and I can loop through the files with no problem. As soon as you try to loop through the sheets, you get the problems.

Anyone managed this in Sense somehow?

Regards

Re: Qlik Sense - Loop through Excel Files and Worksheets

johnh — Wed, 22 Jun 2016 09:41:26 GMT

Hi Robert,

You will need to use the Legacy Mode.

Have a look at this post

Qlik Sense Legacy mode

Cheers

Re: Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Thu, 21 Jul 2016 14:15:20 GMT

Thanks Bruno, but I would have liked to do this in Qlik Sense without the "Legacy mode". My guess is that this is not yet possible.

Regards

Re: Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Thu, 21 Jul 2016 14:16:06 GMT

Thanks John, that will work, but I was wondering if it is possible without doing the "Legacy Mode" trick.

Regards

Re: Qlik Sense - Loop through Excel Files and Worksheets

JaMajka1 — Thu, 27 Oct 2016 14:08:33 GMT

Hi Robert,

did you somehow figure it out? I have the same problem now.

Best Regards,

Maria

Re: Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Fri, 28 Oct 2016 06:15:39 GMT

Hi Maria,

No I did not yet find another way, but the above solution from John. I will try again with the new Sense versions, and reply once I can get this to work.

Regards

Re: Qlik Sense - Loop through Excel Files and Worksheets

mstoler — Fri, 02 Nov 2018 12:41:44 GMT

Hello,

Is there an update to this without having to use Legacy Mode?

Thanks,

Michael

Re: Qlik Sense - Loop through Excel Files and Worksheets

rcurlewis — Sat, 03 Nov 2018 07:54:19 GMT

Hi Michael,

Not that I know of yet. Legacy mode seems to be the only way at this moment.

Regards