topic Loading data from all the tabs of an excel in QlikView

Loading data from all the tabs of an excel

Mon, 29 Dec 2014 09:22:20 GMT

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?

Re: Loading data from all the tabs of an excel

francoiscave — Mon, 29 Dec 2014 09:27:21 GMT

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

Re: Loading data from all the tabs of an excel

dickelsa — Mon, 29 Dec 2014 09:40:22 GMT

See discussion below:

Loading multiple sheets

Regards,

Dick

Re: Loading data from all the tabs of an excel

amit_saini — Mon, 29 Dec 2014 09:49:58 GMT

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

Re: Loading data from all the tabs of an excel

flipside — Mon, 29 Dec 2014 10:09:10 GMT

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

Re: Loading data from all the tabs of an excel

its_anandrjs — Mon, 29 Dec 2014 10:24:52 GMT

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

Re: Loading data from all the tabs of an excel

prieper — Mon, 29 Dec 2014 10:43:52 GMT

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

Re: Loading data from all the tabs of an excel

its_anandrjs — Mon, 29 Dec 2014 11:57:55 GMT

Hi,

You can try Excel ODBC connection also for the same.

Regards

Anand

Re: Loading data from all the tabs of an excel

francoiscave — Mon, 29 Dec 2014 13:52:40 GMT

Hi Anand,

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

Have you a trick for that ?

François

Re: Loading data from all the tabs of an excel

flipside — Mon, 29 Dec 2014 14:17:01 GMT

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.

Re: Loading data from all the tabs of an excel

francoiscave — Mon, 29 Dec 2014 14:22:10 GMT

Hi Flipside,

To solve this issue, I've changed the variable :

Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));

But I have an issue just below when I load my Tab Name fields...

...

LOAD

'$(vSheet)' as [Tab Name],

From [$(vFile)] (ooxml, embedded labels, table is $(vSheet));

...

François

Re: Loading data from all the tabs of an excel

flipside — Mon, 29 Dec 2014 14:24:20 GMT

I was just about to add (because I found this out too) that you will need to wrap square brackets around the tab name because the quote character will throw an error, so using Anand's example ...

(ooxml, embedded labels, table is [ $(vSheet) ] );

flipside

Re: Loading data from all the tabs of an excel

its_anandrjs — Mon, 29 Dec 2014 14:37:37 GMT

Hi Francois,

You can try this expression also by create variable if the sheet names and where you get problem in the script.

SET vSheetNames = 'Sheet1', 'Sheet2','Sheet3','a','b','c';

for each vSheetName in $(vSheetNames)

Data:

LOAD *,

'$(vSheetName)' AS SheetName,

FileName() as FileName

FROM

[Mulitple Data.xlsx]

(ooxml, embedded labels, table is [$(vSheetName)]);

Regards

Anand

Re: Loading data from all the tabs of an excel

francoiscave — Mon, 29 Dec 2014 14:56:35 GMT

Anand,

I find your solution very good, but in French language, we have many words who contains simple apostrophe (like Chiffre d'affaires)...

Your solution doesn'st work in this case...

Do you have an (great) idea ?

I've attached some files if you want try something.

Thanks,

François

Re: Loading data from all the tabs of an excel

dickelsa — Tue, 30 Dec 2014 10:47:19 GMT

See discussion below:

Loading multiple sheets

Regards,

Dick

Re: Loading data from all the tabs of an excel

Tue, 07 Jul 2015 11:22:51 GMT

If this not work:

Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));

Try this:

LET vSheet = PurgeChar(Peek('TABLE_NAME', i, 'Sheets'),chr(39)

Work Ok for me.

Re: Loading data from all the tabs of an excel

francoiscave — Wed, 08 Jul 2015 12:21:26 GMT

Hi Lyudmila,

Thanks for your help !

Have fun with QV,

François