Multiple Excel file load and save each to qvd

[didierodayo]

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

[Anonymous]

Hi Didier,

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

Regards,

[mayuresh_d]

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

[didierodayo]

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

[didierodayo · Accepted solution]

//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

[sudhakar_budde]

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 

SB

[Hann]

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

[UnnatiJais]

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