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

[Anonymous]

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

[swuehl]

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 ...;

[#3]

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();