Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / Reading an Excel file ...
Finance forum
Unanswered

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

editSubscribe (0) ShareShare
ReportReport
Posted on by Rajesh Bongu 45

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.

Categories:
Development / Customization / SDK
  • ShereenO Profile Picture
    ShereenO 4 on at
    Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)
    hi, did u manage to get this working. Can i see the final code snippet please.thanks Shereen
  • ShereenO Profile Picture
    ShereenO 4 on at
    Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)
    hi Rajesh, did u manage to read a sharepoint file. please let me know. thanks. can u send me a snippet of the code. [e-mail address removed to protect it from spam bots]
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Reading an Excel file from a SharePoint using X++ in Microsoft Dynamics 365 for Finance and Operations (10.0.18)

    Hi Rajesh,

    Your uri is wrong since your are missing the site part. Should be something like this: xxxx.sharepoint.com/.../....

  • Rajesh Bongu Profile Picture
    Rajesh Bongu 45 on at
    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

  • Martin Dráb Profile Picture
    Martin Dráb 223,155 Super User on at
    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?

  • Rajesh Bongu Profile Picture
    Rajesh Bongu 45 on at
    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.

  • Martin Dráb Profile Picture
    Martin Dráb 223,155 Super User on at
    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?

  • Rajesh Bongu Profile Picture
    Rajesh Bongu 45 on at
    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. 

    1651.Capture.PNG

  • Martin Dráb Profile Picture
    Martin Dráb 223,155 Super User on at
    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.

  • Rajesh Bongu Profile Picture
    Rajesh Bongu 45 on at
    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.

Helpful resources

Quick Links

Take the Community feedback survey!

Answer this brief 15-question survey about your Community experience…

Demystifying Copilot: Service Edition with Sundar Raghavan

Sundar answers more questions about Copilot for Service...

Dynamics 365 Business Central vs Finance and SCM

Take a look at the key differences between Business Central and…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,361 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,155 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,140

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans