Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading data from all the tabs of an excel

Hi ,

There is a folder which contains multiple excel files. Each excel file has data in varying number of tabs. e.g. Excel File A has data in 4 tabs, Excel file B has data in 12 tabs. The number of excel files present in that folder would also vary from time to time.

How do i load data from all these excels with varying number of tabs?

16 Replies
francoiscave
Partner - Creator III
Partner - Creator III

Hi Asma,

See the link below :

http://community.qlik.com/message/651303#651303

It will be useful to solve your problem.

Have fun with QV,

François

dickelsa
Creator
Creator

See discussion below:

Loading multiple sheets

Regards,

Dick

amit_saini
Master III
Master III

Hi Asma,

Try this:

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;

for a=1 to 3

LOAD employee

FROM

Looping\constructs1.xlsx // this is the excel sheet name

(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a

Next

Code to Access all sheets in a excel work book and convert to QVD

For a=1 to 4

Directory1:

LOAD employee

FROM

Looping\constructs1.xlsx

(ooxml, embedded labels, table is Sheet$(a));

//STORE Directory INTO C:\Users\chaitanyas\Desktop\Looping\Directory$(a).QVD;

Next

STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;

Drop Table Directory1;

Directory;

LOAD employee

FROM

Looping\Directory.QVD

(qvd);

Thanks,
AS

flipside
Partner - Specialist II
Partner - Specialist II

The simplest way is to use an ODBC connection as long as you have the Microsoft Excel drivers on the pc or server just set up a User DSN then call it with the following code ...

ODBC CONNECT32 TO [Excel Files;DBQ={path to Excel file}];
ExcelSheets:
Load *;
sqltables;
disconnect;

You then have the sheets listed as TABLE_NAME under TABLE_TYPE = 'SYSTEM TABLE'. (You may need to strip off the last dollar-sign from the sheet name when looping through to load the data).

flipside

its_anandrjs

Hi Asma,

You can try this simpler way to load various sheets from the given Excel sheet.

For Each vFile in FileList('MulitpleDataExcel.xlsx')
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
LOAD '$(vSheet)' as [Tab Name], *
From [$(vFile)]
(
ooxml, embedded labels, table is $(vSheet));
Next;
Next;

Regards

Anand

prieper
Master II
Master II

You may access the Excel-file with an ODBC-connection.

Applying the command SQL-Table should deliver you a list of available sheets and their names, which then can be picked up in a loop for adressing/loading.

HTH Peter

its_anandrjs

Hi,

You can try Excel ODBC connection also for the same.

Regards

Anand

francoiscave
Partner - Creator III
Partner - Creator III

Hi Anand,

It doesn't work if the tab name contains a '...

Have you a trick for that ?

François

flipside
Partner - Specialist II
Partner - Specialist II

A single apostrophe looks like it is substituted by 2x consecutive single apostrophes in the sqltables and a period (full stop char) is replaced by the # char. You would need to change these back in the load scripts.