Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How to get the list of excels automatically from an particular folder to automate the process.
Thanks,
Raviteja.
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
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;
next
DROP Table Temp_tables;
NEXT vFile;
//Store the table in QVD
Store Journal into ..\Data Sources\Journal.qvd (qvd);
Thanks
Refer the following link, http://qlikviewmaven.blogspot.in/2008/09/loading-all-of-files-from-folder.html Regards. Siva
Thanks to all..Let me check
Thanks to all..Let me check