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.

[jagan]

Hi,

Change the variables as you required

LET vStartSheetNumber = 2011;

LET vEndSheetNumber = 2020;

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

[jim_chan]

Hello Jagan,

just checked - my sheetname is cohort2011, cohort2013, cohort2013,cohort2015.

and the "index" seems to be following with sequence , i mean it as to be 2011,2012,2013,2014,2015.

but my sheetnames dont follow name + sequence(1,2,3,4,5).

[jagan]

Check this link to dynamically read the sheets

Load all Excel files and all sheets in a folder

Regards,

Jagan.

[jim_chan]

cant access, it says private group.

[jagan]

The below scripts helps us in loading all Excel files and all sheets in a Folder into Qlikview.   Just change the file pathvFilePath variable to use this script.

LET vFilePath = 'C:\';

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables;  // Loads all sheet names in the Excel file.

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1  // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT index

DROP TABLE SheetNames;

NEXT

[robert99]

Thanks. Very helpful

[Anil_Babu_Samineni]

Perfect,

I like this page...

Thanks

[#9]

@jagan solution is great; the sheets can be named whatever you want (i.e. they don't need to be in a series of ____0, ____1, ____2, etc)

My XLS file has one sheet named:

2012NAICS

It has no named-ranges (I can't see any)

But SQLTables command produced multiple tables:

'2012NAICS$'

'2012NAICS$'FilterDatabase

'2012NAICS$'Z_6C5796B7_04DB_4DC1_9DEF_15E62E1DEF19_#wvu#FilterDa

'2012NAICS$'Z_41DA7D5F_0161_4E56_8D7C_13281858B77D_#wvu#FilterDa

'2012NAICS$'Z_729E5A99_966B_4D59_8B8C_495F77F73FD2_#wvu#FilterDa

'2012NAICS$'Z_9523EDDC_437C_4DED_B5C0_4998E5F5CB61_#wvu#FilterDa

'2012NAICS$'Z_A21E16D6_B64D_4631_B4C2_2F24B18E9C15_#wvu#FilterDa

Only the first one works (the rest would fail)

And the first one does require the trailing "$" (i.e. don't purge CHR(36) )

Are my issues just a quirk of using XLS instead of XLSX?

[jeckstein]

Hello all,

In place of using

vStarSheetNumber=1

vEndSheetNumber=20

is there a way to use a range of dates (in the format MM.DD.YY).

My sheets are labeled as the day they pull a report.

Any help would be appreciated.

-Jim

[beck_bakytbek]

thanks for your sharing, very useful in dealing with Excel-tables