Read Excels from a folder and generate QVDs

[qlikwiz123]

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.

[Somasundaram]

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

[qlikwiz123]

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

[dplr-rn]

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

[dplr-rn]

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

[dplr-rn]

Any luck?