topic Re: How to upload multiple excel files in QlikView

How to upload multiple excel files

Tue, 16 Dec 2014 12:03:35 GMT

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.

Re: How to upload multiple excel files

amit_saini — Tue, 16 Dec 2014 12:13:08 GMT

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,

Re: How to upload multiple excel files

yduval75 — Tue, 16 Dec 2014 12:13:34 GMT

Hello,

You can use this :

for vSheet = 1 to 3

DATA:

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

next;

Re: How to upload multiple excel files

saumyashah90 — Tue, 16 Dec 2014 12:27:26 GMT

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

Re: How to upload multiple excel files

Tue, 16 Dec 2014 12:40:41 GMT

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

Re: How to upload multiple excel files

MarcoWedel — Tue, 16 Dec 2014 16:40:14 GMT

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

Re: How to upload multiple excel files

Anonymous — Tue, 16 Dec 2014 16:47:00 GMT

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.

Re: How to upload multiple excel files

Wed, 17 Dec 2014 05:46:29 GMT

Hi marco and jsaridhi,

appreciate all your help.

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

Re: How to upload multiple excel files

amit_saini — Wed, 17 Dec 2014 05:57:25 GMT

Hi Harsh,

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

Thanks,

Re: How to upload multiple excel files

Wed, 17 Dec 2014 06:13:52 GMT

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

Re: How to upload multiple excel files

amit_saini — Wed, 17 Dec 2014 06:21:47 GMT

Harsh,

It should be like this :

SUB DoDir (Root)

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

Timesheet:

LOAD

num([Emp. Code], '#####') as [Emp. Code],

Date(Date, 'MM/DD/YYYY') as [Date Coded],

Monthname(Date) as Month,

Year(Date) as Year,

Quartername(Date) as Quarter,

'Week'&Ceil(Day(Date)/7) as Week,

//to get week as Week1, Week2, Week3, Week4 and Week5 in a month

//Week(Date) as Week,

IF(NOT ISNULL(Date), Day(Date)&'-'&weekDay(Date)&'-'&MonthName(Date), NULL()) as Day,

//used null function to remove null value from date field

subfield( Weekname(Date), '/', 2) as WeekNumber

//this will give the week number of weeks in month.

FROM

[$(File)] (ooxml, embedded labels, table is Timesheet, filters(

Remove(Row, Pos(Top, 1))

)) ;

NEXT File

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

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('C:\Users\asaini\Desktop\Sample');

If still this is not working than share your two excel sheets .

Thanks,

Re: How to upload multiple excel files

Wed, 17 Dec 2014 07:19:00 GMT

Hi Amit,

It ran successfully, but the data isn't uploaded.

Is there any way I can share the excel sheets ? I cant find any link here.

Re: How to upload multiple excel files

amit_saini — Wed, 17 Dec 2014 07:24:37 GMT

Brother you need to call you table fields in load statement , Timesheet i shared with you as an example.

Thanks,

Re: How to upload multiple excel files

Wed, 17 Dec 2014 10:26:11 GMT

Hi Amit,

Really thankful for your guidance and help.@

I still am not able to figure out everything that is required to be done.

I dont know this scripting and I need to get a job done.

Could you give me the final script that i need to write to upload 3 excel files saved in a folder called Sample on

desktop. It would be really helpful if i run a script that works and try to understand in what all places i made mistake.

File names are 37.xlsx , top 20.xlsx , Rating.xlsx.

Their column IDs are :

37.xlsx

Project

Store_ID

ISD_ID

StoreSales

Rating_201411

top 20.xlsx

promotor_id

rating

project_id

year_id

month_id

ranking

Ranking.xlsx

promotor_id

rating

project_id

year_id

month_id

ranking

Re: How to upload multiple excel files

amit_saini — Wed, 17 Dec 2014 11:37:36 GMT

Try this:

SUB DoDir (Root)

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

Timesheet:

LOAD

promotor_id

rating

project_id

year_id

month_id

ranking

FROM

[$(File)] (ooxml, embedded labels, table is Timesheet, filters(

Remove(Row, Pos(Top, 1))

)) ;

NEXT File

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

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('C:\Users\asaini\Desktop\Sample');

Thanks,
AS