Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

(0) Share

Report

Posted on by Rajesh Bongu

Hi All,

We have a requirement to import an excel file from SharePoint to D365 Finops. The Excel file needs to be read as is from SharePoint and requires further updates and validations before the data in the excel file is inserted to the data entity through code.

So, the following method is created to read the file from share point and convert it to a Stream.

The logic i am using to read the file from SharePoint is below , The line fileResults = SharePointHelper::GetFiles(proxy, FolderPath, '') returns a result but the line file = SharePointHelper::GetFileContents(proxy, uri); returns a null value for the File and hence the line System.IO.Stream fileStream = file.content fails.

The service account used (ExternalId) to read the file from SharePoint has the site level permissions and no MFA configured.

Could you please let me know If i am missing something or Is there a better way to read the SharePoint excel file in this scenario.

public System.IO.MemoryStream getFileStreamFromSharepoint()
{
#file
#define.xlsx(".xlsx")
#define.https("https://")
#define.decodeUrl("/_api/Web/GetFileByServerRelativePath(decodedurl='/")
ISharePointProxy proxy;
System.Uri uri;
System.Uri NavigateUri;
FileResults fileResults;
FileContents file;

fileName = filename + #xlsx;
uri = new System.Uri(#https + host + #decodeUrl + site + "/" + folderPath + "/" + fileName + "')");
proxy = (proxy && proxy.Config.TargetHost == host && proxy.Config.Site == site) ?
proxy :
SharePointHelper::createProxy(host, site, externalId);

fileResults = SharePointHelper::GetFiles(proxy, FolderPath, ''); // The file result is returned successfully
file = SharePointHelper::GetFileContents(proxy, uri); //This line returns a null value and no exception or error

if(file)
{
System.IO.Stream fileStream = file.content;
System.IO.MemoryStream ms = new System.IO.MemoryStream();
fileStream.copyTo(ms);
return ms;
}
else
{
return null;
}

Thank you.

I have the same question (0)

All responses (13)

Answers (0)

Joris dG 17,775 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

It's not an answer to your question, sorry, but please consider not using macros.

docs.microsoft.com/.../xpp-variables-data-types

Was this reply helpful? Yes No
André Arnaud de Cal... 299,386 Super User 2025 Season 2 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Hi Rajesh,

Have you considered using Azure Logic Apps, Power Automate or the Data management framework instead of writing own x++ logic?

Was this reply helpful? Yes No
Rajesh Bongu 45 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Thanks @joris for highlighting this. I will certainly not use macros in the live version of this code.

Was this reply helpful? Yes No
Rajesh Bongu 45 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Hey @andre. Thank you.

I tried using power automate to pull the file from SharePoint. But as i mentioned the entity we are trying to use is not exposed publicly and the power automate gives me a list of public entities in D365FO to connect to.

I tried creating a Service class and service method to accept the file as a stream assuming the power automate will allow me to select the Service by using the external name and pass the file. But I couldn't access the service class from the Power automate. So, I am not sure If using power automate is a way.

If the data entity is publicly exposed, i could have used Logic apps in combination with the recurring integration to read the file and create the records in D365FO.

Is there a way i could use to pass the Excel file as a stream to my service class method using the Logic apps or power automate or in any other way without writing any code to read the SharePoint file?

Thanks,

Rajesh Bongu.

Was this reply helpful? Yes No
Martin Dráb 236,324 Most Valuable Professional on at

Like (0)

Report
RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Please always use Insert > Insert Code (in the rich-formatting view) to paste source. It preserves line indentation, which makes code much easier to read.

For example:

public System.IO.MemoryStream getFileStreamFromSharepoint() { #File #define.https("https://") #define.decodeUrl("/_api/Web/GetFileByServerRelativePath(decodedurl='/") fileName = filename #xlsx; System.Uri uri = new System.Uri(#https host #decodeUrl site "/" folderPath "/" fileName "')"); //I throw away the part assuming that proxy is populated, because it can't ever be - we're initializing it here. ISharePointProxy proxy = SharePointHelper::createProxy(host, site, externalId); FileResults fileResults = SharePointHelper::GetFiles(proxy, FolderPath, ''); // The file result is returned successfully FileContents file = SharePointHelper::GetFileContents(proxy, uri); //This line returns a null value and no exception or error if(file) { System.IO.Stream fileStream = file.content; System.IO.MemoryStream ms = new System.IO.MemoryStream(); fileStream.copyTo(ms); return ms; } else { return null; } }

I made a few minor changes to your code, when I was fixing line indentation.

I'm assuming that the SharePointHelper class is from Microsoft.Dynamics.Platform.Integration.SharePoint namespace. If so, notice that your can pass true as the third argument and the method should start throwing exceptions on failure. It might tell you more about what's going on.

Was this reply helpful? Yes No
Rajesh Bongu 45 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Thanks @Martin. I will insert code using rich formatting going forward.

Yes the SharePoint helper class is using Microsoft.Dynamics.Platform.Integration.SharePoint.

I added the third argument and the following exception is thrown. The File exists in the same location with the same name and can be browsed through a URL.

Was this reply helpful? Yes No
Martin Dráb 236,324 Most Valuable Professional on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Did you try it with the value copied from the 'uri' variable? If you just reviewed the address, maybe you overlooked a minor difference.

Also, are you testing access in the context of the same user?

Was this reply helpful? Yes No
Rajesh Bongu 45 on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Thanks Martin.

When I copied the value from the URI variable and paste it the browser, i get a 400 bad request

(This error (HTTP 400 Bad Request) means that Internet Explorer was able to connect to the web server, but the webpage could not be found because of a problem with the address)

Yes, the same user in D365FO is used for testing and the user has site level permissions in SharePoint but not the root level permissions.

Was this reply helpful? Yes No
Martin Dráb 236,324 Most Valuable Professional on at

Like (0)

Report

RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

Doesn't it mean that the URL is incorrect?

Was this reply helpful? Yes No
Rajesh Bongu 45 on at

Like (0)

Report
RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

No @martin.

If i exclude "_api/Web/GetFileByServerRelativePath(decodedurl='" and browse the remaining URL , the file gets downloaded.

https://xxxx.sharepoint.com/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/D365_UAT/Shared Documents/Import/26-05-2021.xlsx') -> pasting this URL in the browser gives a 404 file not found https://xxxx.sharepoint.com/sites/D365_UAT/Shared Documents/Import/26-05-2021.xlsx -> pasting this URL in the browser downloads the file

Was this reply helpful? Yes No