Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Load Multiple excel sheets using For loop

cancel
Showing results for 
Search instead for 
Did you mean: 
jagan
Luminary Alumni
Luminary Alumni

Load Multiple excel sheets using For loop

Last Update:

Jul 30, 2013 3:58:58 AM

Updated By:

jagan

Created date:

Jul 30, 2013 3:58:58 AM

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.

Labels (1)
Comments
jagan
Luminary Alumni
Luminary Alumni

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
Specialist
Specialist

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).

0 Likes
jagan
Luminary Alumni
Luminary Alumni

Check this link to dynamically read the sheets

Load all Excel files and all sheets in a folder

Regards,

Jagan.

jim_chan
Specialist
Specialist

cant access, it says private group.

0 Likes
jagan
Luminary Alumni
Luminary Alumni

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

Thanks. Very helpful

0 Likes
Anil_Babu_Samineni

Perfect,

I like this page...

Thanks

0 Likes
Not applicable

@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?

0 Likes
jeckstein
Partner - Creator
Partner - Creator

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

0 Likes
beck_bakytbek
Master
Master

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

0 Likes
Version history
Last update:
‎2013-07-30 03:58 AM
Updated by: