topic Multiple Excel file load and save each to qvd in QlikView

Multiple Excel file load and save each to qvd

didierodayo — Mon, 13 Feb 2017 00:12:51 GMT

Hello,

I would like to load THE FIRST SHEET OF multiple excel files from a network location and save each file to qvd with the source file name like filename.qvd.

the file names can differ.

for some reason the code below is not working

FOR EACH file in FileList('\\home\Files\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to 1
LET vFileName = FileName();

$(vFileName):
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is Sheet$(i)]);

STORE Table Into 'D:\EDMS\'$(vFileName)'.qvd';

Drop table $(vFileName);

NEXT i

Next

Thanks

Re: multiple Excel file load and save each to qvd

Anonymous — Mon, 13 Feb 2017 02:09:13 GMT

Hi Didier,

Maybe this post might be helpful: Loop through root directory (including subfolders) and list filenames of *.csv

Regards,

Re: Multiple Excel file load and save each to qvd

mayuresh_d — Mon, 13 Feb 2017 07:40:32 GMT

hi,

use following script

i hope this will work

for each vFile in FileList('..\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

FileDir() as Dir,
FileName() as File_Name

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

STORE Table Into 'D:\EDMS\'$(vFileName)'.qvd';
Drop table TableName;

next

Re: Multiple Excel file load and save each to qvd

didierodayo — Mon, 13 Feb 2017 08:49:42 GMT

Hi Mayuresh,

It is only saving the last file and without the file name. I added the file base name as shown below but it is the same issue.

I have attached the sample xlsx files.

for each vFile in FileList('D:\EDMS\*.xlsx')
let vName = FileBaseName();
TableName:
load
'$(vFile)' as SourceFile,

FileDir() as Dir,
FileName() as File_Name
from [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
STORE TableName Into 'D:\EDMS\'$(vName)'.qvd';
Drop table TableName;

next

Re: Multiple Excel file load and save each to qvd

didierodayo — Mon, 13 Feb 2017 21:26:14 GMT

//the steps below resolved the issue

//First connect to files location
For each vFile in FileList('D:\*.xlsx')
//If you wan qvds stored in different location then use Subfield to define the location
//otherwise just replace the extension
let vQVD = replace(SubField(vFile,'\',2), '.xlsx', '.qvd');
if alt(FileSize('$(vQVD)'), 0) = 0 then
NextFile:
LOAD
*
from [$(vFile)](ooxml, embedded labels, table is Sheet1);
STORE NextFile INTO $(vQVD) (qvd);
DROP TABLE NextFile;
end if
next

Re: Multiple Excel file load and save each to qvd

sudhakar_budde — Thu, 27 Dec 2018 14:06:35 GMT

Hi,

I wanted to store the qvds into a different location. How can I do that please?

If I wanted to edit the below line, then it is impacting vFile!!!

let vQVD = replace(SubField(vFile,'\',2), '.xlsx', '.qvd');

How can I do that please?

Thanks

Re: Multiple Excel file load and save each to qvd

Hann — Sat, 25 May 2019 18:47:18 GMT

Hi I'm have the same situation but in qlik sense.. Do you mind to share how to solve it in qlik sense scripting format.

Thanks

Re: Multiple Excel file load and save each to qvd

UnnatiJais — Fri, 10 Jul 2020 10:27:03 GMT

Hi Didierodayo,

I have gone through your post. I was trying to implement this solution in my problem also. So here is my issue : I have 3 log file (QVDs), these all file loads the records whenever any load runs. It runs 3 times a day for each stored procedure and stores in QVD files. I want these all data to be stored in one single qvd file named Historyloadfile for analysis purpose.

Approach: I am listing all the qvds and storing in master qvd (Historyloadfile).

Can you please help to get it resolve with appropriate approach.

Thanks and Regards,

Unnati Jaiswal