Excel: Get Data From a File Stored in a SharePoint Folder

Importing data into Excel from a CSV or Excel file stored in a SharePoint Library or OneDrive for Business is a common task and there's more than one way to do it. Most people do it using the From SharePoint Folder option under Get Data however the method I’m going to show you in this video is much quicker.
✅ Want more FREE training?
Why not check out my other tutorials and/or subscribe to my newsletter, both of which can be done at theexceltrainer.co.uk
🙋‍♂️ Let's connect on social
Twitter: / theexceltrainer
Facebook: / theexceltrainer
LinkedIn: / thomasmik
#excel #sharepoint #import

[@avishek559]

Awesome.. sweet and simple. Thank you Mike!

[@Zaporemmm]

Finally a clear and concise explanation! Who could have known that you need to get a link (path) from such a place...

[@catarina_carapeta]

AWE-SOME!! Thank you! That's much easier!!

[@user-zw4pm7vx4w]

Thanks! I’ve been looking for an easier way to do this. Your explanation was straightforward and easy to understand!

[@MikeThomas67]

@user-zw4pm7vx4w Glad I could help

[@EyeOnTheTV]

WHOA . My expectation was very low but this was actually very help and clear and concise. And most importantly it worked!!

[@MikeThomas67]

@EyeOnTheTV Happy to have helped :-)

[@manishkotecha9476]

Thanks really helpful

[@saad_hussain94]

You sir... are the GOAT!

[@MikeThomas67]

Thank you!

[@NancyMancarious]

This is a life saver!

[@MikeThomas67]

Glad I could help

[@patriciaantunes1718]

Thank you!

[@GabrielSantos-ui2qi]

Really helpful! Thank you!

[@jugalbalaji4825]

Thank you so much, was trying to open a sharepoint workbook using VBA, and getting the path like this worked finally. The link from the SHARE option does not work at all.

[@MikeThomas67]

You are welcome

[@udaykiranmaddi203]

Can you help on how to achieve this with excel on mac and xlsx on sharepoint?

[@MikeThomas67]

@udaykiranmaddi203 I don't think you can.If it's an Excel or CSV file (as per the demo in the video) that you want to load into Excel from SharePoint I think you would need to download it and import the local copy. I appreciate that's a few extra steps

[@AmlanMannagcad09103]

Now what to do if there are data from multiple files in sharepoint and you need to merge those datas into one excel folder.

[@MikeThomas67]

@AmlanMannagcad09103 Once you've imported all the files (one at a time using the method in the video) you can create an Append Query using the Query Editor. An alternative method is not to import them one at a time using Data > From Web but to use Data > From File > From SharePoint Folder (this is not available in all versions of Excel)

[@Bluemanacrab4REAL]

The connection is deleted in .csv format when the file is closed. Is there a way around this?

[@rowieforms]

Cool Mike, but what if the file itself is getting replaced (with a different filename), an I want pick up the file in the folder itself? possible?

[@MikeThomas67]

@rowieformaran15 The only way I can think of doing that is with a Parameter Query which is a little advanced but also right now Power Query on Excel for Mac doesn't support import from SharePoint Folder. It MIGHT work if you sync'd the contents of the SharePoint folder/library to your local drive and used Get Data from Excel Workbook

[@enricbasketball]

I have the access denies because of my credentials...but is the same account... am I missing something in my sharepoint folder to give acces to "me"?

[@MikeThomas67]

@enricbasketball I often find I have to sign-in even though I think I'm already signed in via 365. Try "Organisational Account" and sign in as a different user but use your usual 365 credentials

[@user-mt7wv6ii4n]

is there any way that I can get data from another SharePoint (EXCLE file ) and to transferee this data to another file on another file that placed in SharePoint also ,(not in the computer desktop)

[@MikeThomas67]

@user-mt7wv6ii4n So you want to import data into an Excel file hosted in SharePoint from another Excel file hosted in SharePoint? This is only possible if you open the file that you want to import the data into in Excel for Desktop. Excel for Web doesn't support importing data. The file can still be hosted in SharePoint. it just can't be opened in Excel Online

[@pitpiboonbangjan8640]

How MS Access: Get Data From a File excel Stored in a SharePoint Folder

[@davidnelson8495]

How do you do this with mac excel?

[@MikeThomas67]

@davidnelson8495 Assuming it's an Excel file from SharePoint, grab the URL to the SharePoint file in the same way I showed in the video. In Excel for Mac open the Query Editor, start a blank query, switch to the Advanced Editor and paste the following code: let Source = Excel.Workbook(Web.Contents("path_to_the_file_including_the_filename"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data] in Sheet1_Sheet