Edx to convert Excel to QVD

[#1]

Hi Team,

We have our files placed on a folder which contains all the excel files.

I would like to generate a QVD file by loading all the excel files and then load to my QVW.

Do we need to use EDX to perform this automatically? Please suggest

Thanks,

sai

[petter]

QMSEDX Enhanced

Is the fastest and simplest approach to trigger a reload from the command line.

[Clever_Anjos]

All of your Excel files have the same structure? Sheet names and Columns are the same?

[#4]

My task is to pick the excel file and convert to a qvd file automatically whenever a file is placed.

[#5]

Thanks so much petter, will let you know if it works for me

[mgaseron · Accepted solution]

No, EDX is not needed. Below is a potential solution for Excel files with one worksheet (or tab). Good luck!

STEP ONE: Set variables

SET vSourceFolderPath = '<insert folder path location for Excel files>';

//Note the asterik below is used to identify any file that begins with Excel_filename_

SET vFileName = '<Insert Excel_filename_*.xlsx>';

LET vTargetFolderPath = '<insert folder path location for QVD>';

STEP TWO: Create a base QVD file

Do this manually by just running the below code using either one of the files already provided or a shell file with just the column headers.

Excel_To_QVD:

LOAD filename() as SourceFileName, * FROM [$(vSourceFolderPath)<Insert Initial Filename>] (ooxml, embedded labels, table is <sheetname>);

STORE Excel_To_QVD INTO [$(vTargetFolderPath)\Excel_To_QVD.qvd] (qvd);

STEP THREE: Append Excel files and re-generate QVD Automatically

Excel_To_QVD:

LOAD * FROM [$(vTargetFolderPath)\Excel_To_QVD.qvd] (qvd);

FOR each vFile in filelist('$(vSourceFolderPath)$(vFileName)')

          Concatenate (Excel_To_QVD)

          LOAD filename() as SourceFileName, * FROM [$(vFile)] (ooxml, embedded labels, table is <sheetname>);

NEXT

STORE Excel_To_QVD INTO [$(vTargetFolderPath)\Excel_To_QVD.qvd] (qvd);