topic Getting list of Excels from particular folder in QlikView

Getting list of Excels from particular folder

Wed, 17 Jul 2013 04:58:23 GMT

Hi All,

How to get the list of excels automatically from an particular folder to automate the process.

Thanks,

Raviteja.

Re: Getting list of Excels from particular folder

Anonymous — Wed, 17 Jul 2013 05:28:08 GMT

Hi Raviteja,

try to use this code.

SUB GenerateList (Root)

FOR Each Ext in 'xls', 'xlsx'

FOR Each File in filelist (Root&' \*.' & Extension)

LOAD

'$(File)' as Name,

autogenerate 1;

NEXT File

NEXT Ext

FOR Each Dir in dirlist (Root&' \*' )

call GenerateList (Verzeichnis)

NEXT Dir

ENDSUB

CALL GenerateList ('C:')

For larger number of files use this solution http://community.qlik.com/message/361425#361425

which is much more rapid than that one above.

Michael

Re: Getting list of Excels from particular folder

parthiband — Wed, 17 Jul 2013 05:28:19 GMT

Hi Raviteja,

First of all u should set the path of the folder from where u have to retrieve ur excel files.

Let vDataFolder= '..\Data Sources\';

Now u can use the code which pulls data from each and every excel.

Below is the sample format

// enumerate files
FOR EACH vFile in filelist('$(vDataFolder)YouFile*.xlsx');
let vFileName = SubField(vFile,'.',1);
let vFileName = mid(vFileName, index(vFileName, '\', -1) + 1);

//Connection to Excel Files
OLEDB CONNECT32 To [Excel Files;DBQ=$(vFile)];

//Read & Store the Sheets in Buffer
Temp_tables:
sqltables;

for iSheet = 0 to NoOfRows('Temp_tables')-1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_tables');
Let vSheetName=Replace(vSheetName,'$','');
Let vSheetName=Replace(vSheetName,Chr(39),'');
LET vYear=Num#(SubField(vFileName,'_',2));
Let vCountry = if(vSheetName='India','1001','1002');

IndiaJournal:
CrossTable(Months2, Data, 3)
LOAD [GL Account],
     MIS,
     [India MIS],
     [Period 1],
     [Period 2],
     [Period 3]
FROM
[$(vFile)]
(ooxml, embedded labels, table is [$(vSheetName)]);

Journal:
Load *
Resident IndiaJournal;

Drop table IndiaJournal;

DROP Table Temp_tables;

NEXT vFile;

//Store the table in QVD
Store Journal into ..\Data Sources\Journal.qvd (qvd);

Thanks

Re: Getting list of Excels from particular folder

Siva_Sankar — Wed, 17 Jul 2013 05:33:03 GMT

Refer the following link, http://qlikviewmaven.blogspot.in/2008/09/loading-all-of-files-from-folder.html Regards. Siva

Re: Getting list of Excels from particular folder

Wed, 17 Jul 2013 17:23:33 GMT

Thanks to all..Let me check

Re: Getting list of Excels from particular folder

Wed, 17 Jul 2013 17:23:43 GMT

Thanks to all..Let me check