Question

Is it possible to read from and write to Excel file in SharePoint?

  • 17 October 2018
  • 8 replies
  • 53 views
aaron
Rank
Badge +8

I am looking to read from an Excel file in SharePoint, do some analysis, and write back (update) that same Excel file in Sharepoint. How would I do so? It appears I can only read from/write to SharePoint lists, not actual files. The FME help is pretty vague. There is a blog post that describes how one company migrated millions of documents to SharePoint so there must be a way to upload the files. However, I can't find any "how to" documentation. Can anyone point me in the right direction? Thanks!

Aaron

8 replies

david_r
Rank
Userlevel 4

Unfortunately you cannot upload files to SharePoint just yet, unless you do it manually using the ShaprePoint API.

Please consider voting for the idea here:

https://knowledge.safe.com/content/idea/23503/sharepoint-file-uploader.html

R
Badge
Hi Aaron

 

 

What version of FME are you using?

 

 

I worked for Barnsley until last week - that case study was ours. At the time (FME 2016 I think) there was no native support for reading and writing to SharePoint, but I have a hunch it was added in FME 2018 or 2018.1 - at least I managed to write an Excel file to SharePoint just before I left - SharePoint 2013 on premise - by specifying the URL of the document library folder.

 

 

A couple of limitations - the document was left 'checked out' and it only committed once - overwrite failed. Sadly I don't have access to an environment I can re-test this with now :(

 

 

The alternative is to use HTTPCaller to use the SharePoint web services to save the document - you can also specify permissions, check in and so on with the web service. If you need help with that, @revesz is your man, and I'm sure he'll be along soon now I've tagged him!

 

 

Cheers,

 

Riley

 

R
Badge
And here is the link to the last time I asked Andras to explain this :)

 

SOAP request to SharePoint

 

 

aaron
Rank
Badge +8
Hi Aaron

 

 

What version of FME are you using?

 

 

I worked for Barnsley until last week - that case study was ours. At the time (FME 2016 I think) there was no native support for reading and writing to SharePoint, but I have a hunch it was added in FME 2018 or 2018.1 - at least I managed to write an Excel file to SharePoint just before I left - SharePoint 2013 on premise - by specifying the URL of the document library folder.

 

 

A couple of limitations - the document was left 'checked out' and it only committed once - overwrite failed. Sadly I don't have access to an environment I can re-test this with now :(

 

 

The alternative is to use HTTPCaller to use the SharePoint web services to save the document - you can also specify permissions, check in and so on with the web service. If you need help with that, @revesz is your man, and I'm sure he'll be along soon now I've tagged him!

 

 

Cheers,

 

Riley

 

Huh, I used an HTTPCaller to upload (POST) the Excel file and got an http response of 200 but the file isn't there. Any ideas? (Yes, I cleared my cache before reloading the page.)

 

 

R
Badge
Huh, I used an HTTPCaller to upload (POST) the Excel file and got an http response of 200 but the file isn't there. Any ideas? (Yes, I cleared my cache before reloading the page.)

 

 

Are you running FME under the same login as you access SharePoint? And did you 'check in' the document?

 

 

If not, documents in the SharePoint UI only show to other users once they've been checked in, which would explain the behaviour you're seeing.

 

 

If not that, I'm not sure what else to try, sorry :(

 

 

aaron
Rank
Badge +8
Huh, I used an HTTPCaller to upload (POST) the Excel file and got an http response of 200 but the file isn't there. Any ideas? (Yes, I cleared my cache before reloading the page.)

 

 

 

Yep, running under same login and doc is checked in. I realized I'm only getting a 200 http response but not a 201, which would indicate the POST succeeded. Not sure why and nothing to go on in the logs that I can see.
R
Badge
Huh, I used an HTTPCaller to upload (POST) the Excel file and got an http response of 200 but the file isn't there. Any ideas? (Yes, I cleared my cache before reloading the page.)

 

 

Sorry then @aaron I'm not sure what to try next. Did you try it with 2018.1 though - would be interested to know if that worked for you?

 

 

aaron
Rank
Badge +8
Sorry then @aaron I'm not sure what to try next. Did you try it with 2018.1 though - would be interested to know if that worked for you?

 

 

Yes, I was using 2018.1 (build 18528). Maybe I had a parameter wrong or the settings on our SharePoint site won't allow it. I'll certainly chime back in if I discover something.

 

 

Reply


Community Stats

28,707
Posts
109,478
Replies
36,552
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.