Load multiple sheets from multiple excel files from Sharepoint

[MT4T]

Hello,

I can connect to sharepoint via metadata connector to get "filelist"

But what to do in case i have files in different subfolders (some wildcard)?

/sites/05_BUSINESSPLANNING/Shared Documents/06_Price List/03_SAS'

Contains subfolders: "2021", "2022", "2023" and every subfolder contains another subfolder related to Quarters.

At the end I have similar tables with matching columns within 5 sheets i every excel file.

Is there any option to do this LOAD ? 

LIB CONNECT TO 'DA_CONNECTORS:SHP_metadata';
List_of_SASPRICELIST_FILES:
LOAD UniqueId as [ListFiles.UniqueId],
Name as [ListFiles.Name],
title as [ListFiles.title],
id as [ListFiles.id],
updated as [ListFiles.updated],
Author_Entry as [ListFiles.Author_Entry],
CheckedOutByUser_Entry as [ListFiles.CheckedOutByUser_Entry],
EffectiveInformationRightsManagementSettings_Entry as [ListFiles.EffectiveInformationRightsManagementSettings_Entry],
InformationRightsManagementSettings_Entry as [ListFiles.InformationRightsManagementSettings_Entry],
ListItemAllFields_Entry as [ListFiles.ListItemAllFields_Entry],
LockedByUser_Entry as [ListFiles.LockedByUser_Entry],
ModifiedBy_Entry as [ListFiles.ModifiedBy_Entry],
Properties_Entry as [ListFiles.Properties_Entry],
VersionEvents_Feed as [ListFiles.VersionEvents_Feed],
Versions_Feed as [ListFiles.Versions_Feed],
CheckInComment as [ListFiles.CheckInComment],
CheckOutType as [ListFiles.CheckOutType],
ContentTag as [ListFiles.ContentTag],
CustomizedPageStatus as [ListFiles.CustomizedPageStatus],
ETag as [ListFiles.ETag],
Exists as [ListFiles.Exists],
IrmEnabled as [ListFiles.IrmEnabled],
Length as [ListFiles.Length],
Level as [ListFiles.Level],
LinkingUri as [ListFiles.LinkingUri],
LinkingUrl as [ListFiles.LinkingUrl],
MajorVersion as [ListFiles.MajorVersion],
MinorVersion as [ListFiles.MinorVersion],
ServerRelativeUrl as [ListFiles.ServerRelativeUrl],
TimeCreated as [ListFiles.TimeCreated],
TimeLastModified as [ListFiles.TimeLastModified],
UIVersion as [ListFiles.UIVersion],
UIVersionLabel as [ListFiles.UIVersionLabel];

SELECT UniqueId,
Name,
title,
id,
updated,
Author_Entry,
CheckedOutByUser_Entry,
EffectiveInformationRightsManagementSettings_Entry,
InformationRightsManagementSettings_Entry,
ListItemAllFields_Entry,
LockedByUser_Entry,
ModifiedBy_Entry,
Properties_Entry,
VersionEvents_Feed,
Versions_Feed,
CheckInComment,
CheckOutType,
ContentTag,
CustomizedPageStatus,
ETag,
Exists,
IrmEnabled,
Length,
Level,
LinkingUri,
LinkingUrl,
MajorVersion,
MinorVersion,
ServerRelativeUrl,
TimeCreated,
TimeLastModified,
UIVersion,
UIVersionLabel
FROM ListFiles
WITH PROPERTIES (
subSite='sites/05_BUSINESSPLANNING',
folder='/sites/05_BUSINESSPLANNING/Shared Documents/06_Price List/03_SAS',
maxResults=''
);

[Clever_Anjos]

The best way is having two connections.  One using the Metadata connector to retrieve the list of all files and the second using the sharepoint connector to load the data itself

[MT4T]

I have two connectors. But the issue is probably here metadata connector see no file within this subfolder only another subfolders.

Load script fails at the beggining.

This metod work well with multiple files within same subfolder but i cannot fix it for multiple subfolders.

subsite='sites/05_BUSINESSPLANNING',
folder='/sites/05_BUSINESSPLANNING/Shared Documents/06_Price List/03_SAS',
maxResults=''

SASPRICELIST:
NoConcatenate Load Null() as BU AutoGenerate 0;

for i = 0 to NoOfRows('List_of_SASPRICELIST_FILES')-1
let vfilename =replace(Peek('ListFiles.ServerRelativeUrl','$(i)','List_of_SASPRICELIST_FILES'),'/sites/05_BUSINESSPLANNING/','');
trace filename = $(vfilename);
//Concatenate (SPSI)
SASPRICELIST:

LOAD
REPLACE(REPLACE('$(vfilename)','Shared Documents/06_Price List/03_SAS',''),'.xlsm','') as SASPRICELISTFileName,
*
FROM [lib://DA_CONNECTORS:05_BUSINESSPLANNING/$(vfilename)]
(ooxml, embedded labels, header is 6 lines, table is MWO);
next

Drop table List_of_SASPRICELIST_FILES;

DROP FIELD BU;

[Clever_Anjos]

Did you use the ListFolders and then ListFiles for eah folder?

[MT4T]

I created listfolders connector right now. But i don understand how do i use it both at once. 

My sample file has this path:

https://smpl1234.sharepoint.com/sites/05_BUSINESSPLANNING/Shared%20Documents/06_Price%20List/03_SAS/...

Can u provide some sample modification of this link with use listfolders and list files ?

with only listfiles I created this: " let vfilename =replace(Peek('ListFiles.ServerRelativeUrl','$(i)','List_of_SASPRICELIST_FILES'),'/sites/05_BUSINESSPLANNING/','');" 

LIB CONNECT TO 'Data Analysis:DA_4T_Sharepoint_Metadata';

LOAD Name as [ListFolders.Name],
ServerRelativeUrl as [ListFolders.ServerRelativeUrl],
UniqueId as [ListFolders.UniqueId],
id as [ListFolders.id],
title as [ListFolders.title],
updated as [ListFolders.updated],
Files_Feed as [ListFolders.Files_Feed],
ListItemAllFields_Entry as [ListFolders.ListItemAllFields_Entry],
ParentFolder_Entry as [ListFolders.ParentFolder_Entry],
Properties_Entry as [ListFolders.Properties_Entry],
StorageMetrics_Entry as [ListFolders.StorageMetrics_Entry],
Folders_Feed as [ListFolders.Folders_Feed],
Exists as [ListFolders.Exists],
IsWOPIEnabled as [ListFolders.IsWOPIEnabled],
ItemCount as [ListFolders.ItemCount],
ProgID as [ListFolders.ProgID],
TimeCreated as [ListFolders.TimeCreated],
TimeLastModified as [ListFolders.TimeLastModified],
WelcomePage as [ListFolders.WelcomePage];

SELECT Name,
ServerRelativeUrl,
UniqueId,
id,
title,
updated,
Files_Feed,
ListItemAllFields_Entry,
ParentFolder_Entry,
Properties_Entry,
StorageMetrics_Entry,
Folders_Feed,
Exists,
IsWOPIEnabled,
ItemCount,
ProgID,
TimeCreated,
TimeLastModified,
WelcomePage
FROM ListFolders
WITH PROPERTIES (
subSite='/sites/05_BUSINESSPLANNING/',
folder='/sites/05_BUSINESSPLANNING/Shared Documents/06_Price List/',
maxResults=''
);

[Clever_Anjos · Accepted solution]

It would be something like this to get a list of all files that are under 

/folder/subfolder

LIB CONNECT TO 'Office_365_Sharepoint_Metadata';
BaseUrl='sites/05_BUSINESSPLANNING';

[1st LVL]:
LOAD
ServerRelativeUrl as [TopFolders]
Where
WildMatch(Name,'202*')
and ItemCount > 0
;

SELECT Name,
ServerRelativeUrl,
ItemCount
FROM ListFolders
WITH PROPERTIES (
subSite='$(BaseUrl)',
folder='$(BaseUrl)/Shared Documents/06_Price%20List/03_SAS', // Adjust this!
maxResults=''
);

For i = 1 to FieldValueCount('TopFolders')
folder = FieldValue('TopFolders',i);
trace $(folder);

[2nd LVL]:
LOAD
'$(folder)' as [ParentFolder],
ServerRelativeUrl as [SubFoldersURL]
Where ItemCount > 0 ;
SELECT Name,
ServerRelativeUrl,
ItemCount
FROM ListFolders
WITH PROPERTIES (
subSite='$(BaseUrl)',
folder='$(folder)',
maxResults=''
);
Next
Drop Table [1st LVL];

for i = 1 to FieldValueCount('SubFoldersURL')
folder = FieldValue('SubFoldersURL',i);
Files:
Load
UniqueId,
title,
ServerRelativeUrl as [FileURL];
SELECT
UniqueId,
Name,
title,
ServerRelativeUrl
FROM ListFiles
WITH PROPERTIES (
subSite='$(BaseUrl)',
folder='$(folder)',
maxResults=''
);
Next;
Drop Table [2nd LVL];