topic Re: How to load multiple excel sheets at a time in qlikview? in QlikView

How to load multiple excel sheets at a time in qlikview?

d_koti24 — Fri, 07 Nov 2014 03:29:25 GMT

Hi,

How to load multiple excel sheets at a time in qlikview?

regards,

koti

Re: How to load multiple excel sheets at a time in qlikview?

Fri, 07 Nov 2014 03:50:35 GMT

pls explain little bit more

u want load multiple sheetes(sheet1,sheet2) or multiple excel sheets

Re: How to load multiple excel sheets at a time in qlikview?

ecolomer — Fri, 07 Nov 2014 03:54:26 GMT

You can upload multiple excel files or multiple sheets for an excel files. Both is possible in the script.

Here you have an example for files '.qv'

DIRECTORY C:\Users\KonoGes\Documents;

Set Root='C:\Users\KonoGes\Documents';

for each Ext in 'qvw', 'qva', 'qvo', 'qvs'

for each Dir in dirlist ('$(Root)'&'\*')

for each File in filelist (Dir&'\*.' &Ext)

Load

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime,

Subfield('$(File)', '\', 1+SubStringCount('$(File)', '\')) as Fichero,

left('$(File)', index('$(File)', '\', -1)) as Directorio

autogenerate 1;

next File

next Dir

next Ext

Re: How to load multiple excel sheets at a time in qlikview?

Fri, 07 Nov 2014 03:56:01 GMT

hi koti,

in script editor while establishing a connection with the data base there is a button called select, on selecting it you have to specify the data source name and then connect it, if the connection is established successfully then you can view all the sheets in the excel as tables then add each then click ok.

regards

vivek

Re: How to load multiple excel sheets at a time in qlikview?

Fri, 07 Nov 2014 04:07:52 GMT

In case you have date or any other keyword appended with the file names,You can use wildcard (*) to reload similar name files like below:

LOAD

FROM

[Mozambique Raw Data*.xlsx]

Hope this was helpful.

Thanks,

Singh

Re: How to load multiple excel sheets at a time in qlikview?

Fri, 07 Nov 2014 04:08:52 GMT

hi enrique,

can u elaborate more , '$(file)' do we have to give the directory or name of the file

regards

vivek

Re: How to load multiple excel sheets at a time in qlikview?

ecolomer — Fri, 07 Nov 2014 05:42:22 GMT

Yes

$(File) is the File in Dir Directory with extension = 'qvw' or 'qva' or 'qvo' or 'qvs'

Re: How to load multiple excel sheets at a time in qlikview?

djsampat — Fri, 07 Nov 2014 06:26:18 GMT

Hi,

To keep it simple,

Just create a tab with all your excel sheets and even concatenate them, if you like or just leave them as it is and the column names that are the same will be linked.

e.g.

SuburbArea:

LOAD

Rep,

Area,

Suburb,

PhoneNumber

FROM

[\\QlikView Documents\SalesRepAreas.xlsx

(ooxml, embedded labels, table is Summary);

InvoiceHeader:

LOAD

Year,

Rep,

Site,

Budget

FROM

[\\QlikView Documents\SalesRepBudget.xlsx]

(ooxml, embedded labels, table is Sheet1);

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv