Load multiple excel files of different formats (.xls,.xlsx) from a share point folder and concatenate create a single qvd

[Anuhyak1]

I have multiple excel files of different formats (.xls,.xlsx) in a share point folder.

I have to read only FileName=saba among also those files and concatenate them and create a qvd.

I am getting unsupported bad file format and internal server error issues. Please help with the script.

AllFiles:
LOAD
UniqueId,
Name,
date#(SubField(Name,'_',2),'DDMMMYYYY') as NewsDate,
title,
id
FROM [lib://$(vLib)]
(qvx);

List2:
NoConcatenate
LOAD
*
Resident AllFiles
Where SubField(Name,'_',-2) = 'Saba' AND SubField(Name,'.',2)='xlsx' ;

For q = 0 to NoOfRows('List2')-1
Let vFile1 = Peek('Name',$(q),'List2');
Let vUniqueFileId1 = Peek('UniqueId',$(q),'List2');
Trace $(vFile1);


Saba :
Concatenate
LOAD *
FROM
[lib://$(vLib)]
(URL is [$(vUrl)$(vUniqueFileId1)$(vAppconstant)],ooxml, embedded labels, table is [$(vsheetName)$]);
Store Saba$(vCheck) into '$(vEXTQVDRead)Sablaxlsx.qvd' (qvd);
NEXT

[MartW]

I would say that you should try and split up the xlsx files and the xls files.

if they got a similar schema, then you can start with a dummy table then only load the xlsx files concatenate those to the dummy table. after that do an other resident load and a loop with only the xls files..

with the concatenate function it is important to specify where to conatenate to. if you don't specify it then it will concatenate to the last table.

[Anuhyak1]

They have different Field Names in those xlsx files. Could you help me with the script.