Loading data from all the tabs of an excel

[#1]

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?

[francoiscave]

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]

See discussion below:

Loading multiple sheets

Regards,

Dick

[amit_saini]

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]

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]

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]

Hi Anand,

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

Have you a trick for that ?

François

[flipside]

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.