topic Re: Edx to convert Excel to QVD in QlikView

Edx to convert Excel to QVD

Fri, 15 May 2015 13:46:47 GMT

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 — Fri, 15 May 2015 13:57:45 GMT

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

Clever_Anjos — Fri, 15 May 2015 14:08:03 GMT

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

Sat, 16 May 2015 01:13:55 GMT

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

Sat, 16 May 2015 01:14:26 GMT

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

mgaseron — Sat, 16 May 2015 05:00:04 GMT

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

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