Load Multiple excel sheets using For loop

[jagan]

Hi All,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements. 

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this helps others.

Regards,

Jagan.

[Anonymous]

I am using this and it works very good when using LOAD * , i.e. loading all fields from all the sheets.

But my files do not have the same info on all sheets and the info on the sheets may vary.

I also only need some of the fields from the different files and would then like to use:

LOAD

     Field1,

    Field 2

   etc.

Here I get errors since it does not find the fields on some of the sheets.

Any idea on how to solve this?

[jagan]

Hi Peter,

Try like this

First load all tables using

Load

*

and then use DROP FIELDS to drop the fields which you do not required.

Regards,

Jagan.

[ahaahaaha]

Thanks for the useful information.

[Anonymous]

Thank you Jagan. This might be the solution I will end up using if I can't figure out a way to avoid having to load all of the fields.

There are two reasons I want to avoid loading all of the data from the files.

First off I only need a few of them in my application (~20 out of well over 100 fields).

Second reason is that the files are quite big and are starting to add up in numbers.

[ishanbhatt]

Hi Jagan,

Thank you for sharing this great trick. In My data, there are around 60 to 70 worksheets in one excel file and all names are in string format (ex..John, Mary, Yusuf). So, How can we load these sheets?

Many thanks in advanced.

[arjunganisetti]

Thank you so much, it's very helpful

[Anonymous]

Hi

I have been trying to figure out a way to only load new files by using this script. After a lot of trial and error I came up with the script below. This works for all files except for the last, i.e. it loads through all the files without adding any data but it does load data from the last file even though it should not.

the MaxsystemTime used below is derived from a timestamp on one of the sheets in the excel files, i..e it does not exist on all sheets which stops me from using it in the WHERE clause (I think....)

NewData:

LOAD

  '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0); 

FOR index = vStartSheetNumber TO vEndSheetNumber

Set ErrorMode = 0;

Concatenate(NewData)

LOAD

          *

FROM C:\Temp\Qlik\Data*.xlsx

(ooxml, embedded labels, table is [Datasheet$(index)])

WHERE Filetime() > '$(MaxSystemTime)';

NEXT

Any ideas or alternate ways of only loading new data while loading from Mlutiple sheets?

[Anonymous]

Hey,
What if i don't have ODBC connection ? How can i load all excel files present in a folder without ODBC connection ?

[ysj]

thank you

[Anonymous]

This is super helpful thanks you! How would i edit the code so that each sheet is saved as a different table in QLik?