topic Loading Multiple excels at a time from a folder and cresting QVds in QlikView

Loading Multiple excels at a time from a folder and cresting QVds

Anonymous — Mon, 06 Jun 2016 10:58:25 GMT

Hi All,

I want to load All excels at a time which are presented in a folder and want to generate individual Qvds for each excel file.

Please any one help me its an urgent requirement in my project.

Thanks in advance

Thanks,

Anusha K

Re: Loading Multiple excels at a time from a folder and cresting QVds

swuehl — Mon, 06 Jun 2016 11:10:22 GMT

You can use a FOR EACH vFile IN FILELIST(..) .. NEXT loop to iterate over all files (you can find the complete sample code in the HELP.

Then, inside the loop, do something like LOAD * FROM $(vFile) (qvd); STORE ...; DROP TABLE ...;

Re: Loading Multiple excels at a time from a folder and cresting QVds

Mon, 06 Jun 2016 11:16:44 GMT

Perhaps try the following:

// Define the Path

LET vExcelFilePath = 'D:\Test';
LET vQVDFilePath='D:\Test';

SUB CreateQVDFromAllExcelFiles(vPath)

FOR EACH vFileExtension IN 'xlsx'

FOR EACH vFile IN FILELIST(vPath & '\*.' & vFileExtension);

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
LOAD * INLINE [
junk ];

FOR i = 0 TO NOOFROWS('Temp')-1

LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

CONCATENATE(Data)
LOAD *,
FILEBASENAME() AS FileName,
'$(vSheetName)' AS Sheet_name
FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i

DROP TABLE Temp;
DROP FIELD junk;

NEXT vFile
NEXT vFileExtension

Set ErrorMode=0;
Drop Field A; // When there is blank sheet in excel file, field A is created
Set ErrorMode=1;

STORE Data into $(vQVDFilePath)\FullData.qvd;
DROP Table Data;

END SUB

CALL CreateQVDFromAllExcelFiles('$(vExcelFilePath)');

LET i = Null();