How to upload multiple excel files

[#1]

Hi,

I would like to upload a few excel sheets to make a bubble chart.

I dont know how to upload multiple sheets at a time, Could you please

tell me how to upload them manually or using a script. If there is a script,

please let me know where in the editor do i write/run this script.

I am pasting the screenshot of dashboard.

I would like to use two different excel files to make the empty bubble chart.

Also, I would like to refresh data monthly. How can i reflect the changes on the dashboard ?

Thanks for all your help in advance.

[amit_saini]

Harsh,

Mentioned below are few codes which may help you when you are dealing with excel sheet data into QlikView:

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\amits\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

[yduval75]

Hello,

You can use this :

for vSheet = 1 to 3

  DATA:

  LOAD * FROM [ExcelFile.xlsx] (ooxml, no labels, table is [DATA $(vSheet)]);

next;

[saumyashah90]

• InTable:

LOAD * INLINE [

SheetNo, SheetName

1, January

2, February

3,March

4,April

5,May

6,Chr_June

7,JULY

8,August

9,September

10, October

];

Then the loop:

For i = 0 to NoOfRows('InTable') - 1

LET vSheet = peek('SheetNo', $(i), 'InTable');

AllData:

LOAD *,

FROM ExcelSheetName.xlsx (biff, embedded labels, table is $(vSheet));

next i

[#5]

Hi amit,

Could you please tell me where to write and run following code in the tool ?;

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');

[MarcoWedel]

If you want to load several files instead of several sheets within the same file (like your thread titel suggests), then you could do so like:

LOAD *
FROM [<Path>\*.xlsx]
(ooxml, embedded labels, table is table1);

hope this helps

regards

Marco

[Anonymous]

you need to write or copy/paste this code in the script editor. Click Ctrl+E (File -> Edit Script). After you paste the code, Go to File -> Reload to execute the script and load the data. If all you are trying to do is load a bunch of excel files in a folder, simplest approach is to follow @Marco Wedel's reply.

[#8]

Hi marco and jsaridhi,

appreciate all your help.

when i click load data, it says data load failed.

,

[amit_saini]

Hi Harsh,

Open this attachment and press Ctr+E to see the script , further change you sheet path accordingly.

Thanks,

AS

[#10]

Hi amit,

I have loaded two different files called top20.xlsx and Ranking.xlsx which can be seen

on the left pannel. I copied the sheet path from that script.

what should be the sheet path if this is incorrect and the files are located in a folder called Sample on desktop ?

Thanks