topic Re: Convert all the excels in a folder to qvds in App Development

Convert all the excels in a folder to qvds

Swarup_MOA — Mon, 31 Jul 2023 17:52:13 GMT

Need a recursively convert excels to qvds

Re: Convert all the excels in a folder to qvds

swarup_malli — Mon, 31 Jul 2023 17:59:57 GMT

// Step 1: Set the main folder path
SET vMainFolderPath = 'C:\Your\Folder\Path\';

// Step 2: Load and process Excel files
FOR EACH FileExtension in 'xlsx', 'xls'
FOR EACH FoundFile in FileList('$(vMainFolderPath)\*.$(FileExtension)')
LOAD *
FROM $(FoundFile)
(ooxml, embedded labels, table is [Sheet1]);

// Step 3: Convert and store the data as QVD
LET vQvdFileName = Replace(Replace('$(FoundFile)', '.xlsx', '.qvd'), '.xls', '.qvd');
STORE * INTO $(vQvdFileName) (QVD);
NEXT
NEXT

Re: Convert all the excels in a folder to qvds

Swarup_MOA — Mon, 31 Jul 2023 18:36:46 GMT

this worked for me

FOR EACH file in filelist('$(vSourceQvd)\*.xlsx') ;
TRACE '$(file )';
tmp:
LOAD *
FROM $(file)
(ooxml, embedded labels, table is Sheet1);

let QVDPath = left('$(file)', len('$(file)')-4) & 'qvd';

Store * from tmp into '$(QVDPath)' (qvd);
drop Table tmp;

NEXT file

Re: Convert all the excels in a folder to qvds

swarup_malli — Fri, 08 Sep 2023 18:45:50 GMT

sum({<Employment_Date_Master={"$(vMaxSelectedEmploymentDate)"}>*
<MONEY_SOURCE={'A'},[Employment Date]={"<=$(vEmployeeDateMax)"},REHIRE_DT={" "}> *
(<MONEY_SOURCE={'A'},TERM_DT={">=$(vEmployeeDateMin)"}>+<TERM_DT={" "}>)
+
((<MONEY_SOURCE={'A'},REHIRE_DT={"<=$(vEmployeeDateMax)"},Termination_DT-={" "}>)
*
(<MONEY_SOURCE={'A'},REHIRE_DT-={" "}>))
} PART_MARKET_VALUE)