Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikwiz123
Contributor

Read Excels from a folder and generate QVDs

I have files in a folder C:\Source Excels

File-Jan-2018

File-Feb-2018

File-Mar-2018

File-Apr-2018

File-May-2018

File-Jun-2018

File-Jul-2018

File-Aug-2018

File-Sep-2018

File-Oct-2018

File-Nov-2018

File-Dec-2018

 

I want QlikView script to read all those files from the folder and create QVDs with the same names as the Excels (File-Jan-2018.qvd, File-Feb-2018.qvd, File-Mar-2018.qvd....)

I would like to create the script in such a way that I don't have to write all the file names in the script; instead pull all the .xlsx files in the folder and create QVDs with the same names, sort of Dynamically.

Labels (3)
5 Replies
Somasundaram
Contributor III

Re: Read Excels from a folder and generate QVDs

let vDataFolder = 'C:\Users\Axd\Sample';

 

for each vFile in filelist('$(vDataFolder)*.xlsx')

 

  OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

 

  Tables:

  sqltables;

 

  let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

 

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

  Data:

  LOAD *

// '$(sheetName)' as Sheet

  FROM [$(vFile)]

  (ooxml, no labels, table is [$(vSheetName)]);

Store Data into [$(vSheetName)].qvd(qvd);

  next

   DROP TABLE Tables;

next


-Somasundaram
qlikwiz123
Contributor

Re: Read Excels from a folder and generate QVDs

Hi,

 

Not really sure why you need to create OLEDB connection and then the below lines :

Tables:

sqltables;

 

But this code runs in a second and ends. No errors, no output

Partner
Partner

Re: Read Excels from a folder and generate QVDs

try modify below and try

set vRoot = '.';


FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
Temp:	
LOAD *
		FROM
		[$(File)]
		(ooxml, no labels, table is data);
let qvdname= replace(File,'xlsx','qvd');
		
store Temp into $(qvdname);

drop table Temp; 	
next File
Partner
Partner

Re: Read Excels from a folder and generate QVDs

updated script with the store qvd as well.
please note that in above code the excel tab name has to be same 'data'
Partner
Partner

Re: Read Excels from a folder and generate QVDs

Any luck?