Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Getting list of Excels from particular folder

Hi All,

  

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

  Thanks,

   Raviteja.

5 Replies
michaelk
Creator
Creator

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

parthiband
Partner
Partner

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

Siva_Sankar
Master II
Master II

Not applicable
Author

Thanks to all..Let me check

Not applicable
Author

Thanks to all..Let me check