Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Anonymous
Not applicable
Author

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 - Creator
Partner - Creator

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