topic Re: How to load multiple Excel Files into Qlikview with For Loop in QlikView

How to load multiple Excel Files into Qlikview with For Loop

madhubabum — Thu, 17 Sep 2015 18:33:50 GMT

Hi All

--> I Have One Folder : D:\ExcelFiles (Here I am Having Multiple Excel Files with Multiple Sheets)

--> I want to load all Excel Files into Qlikview

Note : By Using "For Loop"

--> How Can SET ErrorMode ?

--> While Loading an error in Particular File "Generate A Log File"

How we Can Achieve this one ??

Thanks

Madhu

Re: How to load multiple Excel Files into Qlikview with For Loop

sudeepkm — Thu, 17 Sep 2015 19:47:08 GMT

let vDataFolder = '..\ExcelFiles\';

Set ErrorMode=0;

// Check if file exists in the folder

if(FileSize('$(vDataFolder)*.xls')>0) then

trace "files exist";

// Loop through each file under ExcelFiles folder

for each vFiles in filelist('$(vDataFolder)*.xls')

dat:

LOAD FileName() as filename, FileTime() as filetime, *

FROM [$(vFiles)](biff, no labels, table is @1) ; // you can give the sheet name here

ELSE

trace "no files exist";

END if;

Re: How to load multiple Excel Files into Qlikview with For Loop

madhubabum — Thu, 17 Sep 2015 20:55:09 GMT

HI Sudeep Mahapatra

Thanks For your Reply,

--> In our Excel file Folder "one Excel file does not have an Header (Field Names)"

--> How Can we set Error Mode , If an Error Occur ?

--> How can we Generate a log File for Errors only ? (With out using "Generate Log File ")

I am Attaching Screen shots as follows :

Note : For Missing_Data.xlsx file , Field names are missing ?

Sample Excel Files :

How can we "Concatenate Previous two tables" ?

-->How can we generate a log file for this missing fields table ?

Thanks a lot

Madhu

Re: How to load multiple Excel Files into Qlikview with For Loop

sudeepkm — Fri, 18 Sep 2015 12:41:03 GMT

Please see my answer below (italic)...

--> In our Excel file Folder "one Excel file does not have an Header (Field Names)"

For files that does not have header info as expected you need to design your code to handle such error scenarios and then report it.

--> How Can we set Error Mode , If an Error Occur ?

Normally QlikView stops executing script whenever an error occurs but if you set the error mode to 0 then it continues to run the script by providing you an opportunity to handle the error.

--> How can we Generate a log File for Errors only ? (With out using "Generate Log File ")

You may create a table with all your error details. Please see QlikView help for "Error" and you can find more info on Error variables such as ErrorMode, ScriptError, ScriptErrorDetails etc.

-->How can we generate a log file for this missing fields table ?

Please take a look at this file handling utility here.A file checker utility for file based data sources