Using 'for..each..next' doesn't seem to work with excel files

[#1]

I have files with file name convention as - YYYYMMDD_Salesfiles.xls.Now I load files depending on the time period that user wants to see the data for as,

for each File in filelist ('.\datasource\Security Files\*_TrdgFile.csv')

IF mid('$(File)',len('$(File)')-20,8) >=$(frm_dt) and mid('$(File)',len('$(File)')-20,8) <=$(to_dt) then
Sales_tab:
load
.........
from $(File);
end if

next File;

This works fine with CSV files but not with excel files.I had to convert excel files to csvs. Anyone loaded the data using excel files?

Any help is appreciated.

[#2]

Hi Raj

The way i solved it was by adding this after your FROM statement.

FROM

$(File) ($(File) (

$(File) (

biff, embedded labels, table is [Sheet1$]) ;

Replace "Sheet1" with the name of the sheet from which you want to load your data.

It seems like QlikView prefer that we define the sheet in the statement.

/Martin

[#3]

I have no issues with XLS doing the loop.

Simplest method is to import the spreadsheet using the wizard, then build the loop aroundit