topic Re: All sheets loading in QlikView

All sheets loading

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi experts,

I have one excel Sales_Data in this excel sheets like 1,2,3,4,5,...........31 for date wise.

Note:- all sheets column names are same

for ex:-In excel i have sales from 1st june to 14th june .i want to load all sheets data dynamically,

if i go for reload tomorrow i want data from 1st june to 15th june. How can i achieve this?

I know this process

for each vname in filelist(1,2,3,4,5,6,7,8,9,10..........31)

T1:

load

ID,

Name,

Sales

From

......... $(vname));

in this way if i have 3 sheets like 1,2,3 ,i have to write manually 1,2,3 in for loop

But

Note:- I don't want to change script manually in this requirement .How can i achieve this?

Regards

Mahesh

Re: All sheets loading

ali_hijazi — Wed, 14 Jun 2017 11:06:50 GMT

the following script loops through all excel files in a specified directory

you need to modify the line in bold but if all excel sheets have the same sheet names then just replace Sheet1 with the name you have

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'xls'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD *, '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (ooxml, embedded labels, table is Sheet1);

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

Re: All sheets loading

qlikview979 — Wed, 14 Jun 2017 11:08:15 GMT

Hi Experts help me on this

stalwar1‌vinieme12‌jagan‌loveisfail‌

Regards

Mahesh

Re: All sheets loading

beck_bakytbek — Wed, 14 Jun 2017 11:19:06 GMT

Load the sample data - How to manage the QlikView data model - Part 1 - YouTube

Re: All sheets loading

vinieme12 — Wed, 14 Jun 2017 11:24:01 GMT

something like below, connect to excel using and ODBC connection first

ODBC CONNECT32 TO [Excel Files;DBQ=fullpath\abcd.xlsx];

LET vfile = 'fullpath\abcd.xlsx';
exceltables:
SQLTABLES;
DISCONNECT;

For i = 0 To NoOfRows('exceltables')-1

Let zSheet = purgechar(purgechar(Peek('TABLE_NAME', i, 'exceltables'),chr(36)),chr(39)); //Remove $ and ' from sheetname string

FACT:
LOAD *,
'$(zSheet)' as FromSheet
FROM
[fullpath\abcd.xlsx]
(ooxml, embedded labels, header is XX lines, table is [$(zSheet)]);

Next

Re: All sheets loading

vinieme12 — Wed, 14 Jun 2017 11:27:27 GMT

also see other results from google

https://www.google.com.sg/search?q=loop+thru+sheets+qlikview&oq=loop+thru+sheets+qlikview&aqs=chrome..69i57j0l5.5039j0j7&sourceid=chrome&ie=UTF-8

Re: All sheets loading

qlikview979 — Wed, 14 Jun 2017 11:30:38 GMT

Hi all,

Thanks for response,

I am not able to achieve this.can any one try with my attached Excel file.

Regards

Re: All sheets loading

vinieme12 — Wed, 14 Jun 2017 11:36:38 GMT

try below, edit the file paths

ODBC CONNECT32 TO [Excel Files;DBQ=xxxxxx\Data 2014.xlsx];

LET vfile = 'xxxxxx\Data 2014.xlsx';

exceltables:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('exceltables')-1

Let zSheet = purgechar(purgechar(Peek('TABLE_NAME', i, 'exceltables'),chr(36)),chr(39)); //Remove $ and ' from sheetname string

FACT:

LOAD *,

'$(zSheet)' as FromSheet

FROM

[xxxxxx\Data 2014.xlsx]

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

drop table exceltables;