Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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