loading from different files within a directory

[egoziy]

Hi 
I trying to load data from xlsx files that are sitting in a SharePoint directory, but getting an error during the load
I am using 

for each file in filelist('lib://test_Space:Office_365_Sharepoint - Qlik/Shared Documents/testReports/*.xlsx')
    LOAD 
    wh_id,
    EXPORT_DATE,
    client_code
  FROM [$(file)] (ooxml, embedded labels);
next file

but I am getting an error  

The following error occurred:
Connector error: Failed on attempt 1 to GET. (-2147024894, System.IO.FileNotFoundException, File Not Found.)
The error occurred here:
for each file in filelist('lib://eGold_Space:Office_365_Sharepoint - Qlik/Shared Documents/eGoldReports/*.xlsx')

I'm only getting the error when trying to load wildcard (*.xlsx) but if I am using a full name of one of the files in the directory it works 

Thanks in advance

[paulcalvet]

Hello,

Wildcar * doesn't work with cloud storage, 

With Sharepoint, I think that you must use the Sharepoint meta data connector to get the list of the file in a table.

And then you loop over this table to load all files.

Here is a function you can use to concatenate all files in a folder (with same structure) in one table :

Sub Sharepoint_Concatenate (vConnectionMetadata, vConnection, vSubsite, vFolder, vFiletoload, vTabletoload, vParameter)

/*********************************************************** 

Example of values for parameters :

Call Sharepoint_Concatenate('Local (shared space):SharepointQlikMetadata','lib://Local (shared space):SharepointQlik/','/sites/is/teams/bi/','Documents/Qlik Sense/SAAS/','MyFile FCST','Mytable1','(ooxml, no labels, table is sheet1)');

vConnectionMetadata = Local (shared space):SharepointQlikMetadata

vSubsite = /sites/is/teams/bi

vFolder = Documents/Qlik Sense/SAAS/

vConnection = lib://Local (shared space):SharepointQlik/

vParameter = (ooxml, no labels, table is sheet1)

************************************************************/

set v_dummy = 0;

LIB CONNECT TO '$(vConnectionMetadata)';

ListObjects:

LOAD Name, 

id as [ListFiles.id], 

ServerRelativeUrl

where SubStringCount(Name, '$(vFiletoload)')>0;

SELECT

Name,

    id,

    ServerRelativeUrl

FROM ListFiles

WITH PROPERTIES (

subSite='$(vSubsite)',

folder='$(vSubsite)$(vFolder)',

maxResults=''

);

SET vVar = NoOfRows('$(vTabletoload)');

if (len($(vVar)) = 0) then

$(vTabletoload):

  LOAD * Inline [_dummy];

set v_dummy = 1;

End If

FOR Each vName in FieldValueList('Name')

    Concatenate($(vTabletoload))

    LOAD

        *

    FROM [$(vConnection)$(vFolder)/$(vName)]

    $(vParameter);

NEXT vName

if($(v_dummy)=1) then

drop Field _dummy;

    set v_dummy = 0;

end if

drop table ListObjects;

end Sub