All sheets loading

[qlikview979]

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

next

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

[ali_hijazi]

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

[qlikview979]

Hi Experts help me on this

stalwar1‌vinieme12‌jagan‌loveisfail‌

Regards

Mahesh

[beck_bakytbek]

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

[vinieme12]

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

[vinieme12]

also see other results from google

https://www.google.com.sg/search?q=loop+thru+sheets+qlikview&oq=loop+thru+sheets+qlikview&aqs=chrome...

[qlikview979]

Hi all,

Thanks for response,

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

Regards

[vinieme12]

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

Next

drop table exceltables;