How to load csv file from Sharepoint folder ?

[harsh]

Hello all,

I 'm new to Power BI, I need to upload data from csv file located in sharepoint folder.

This is the complete connection(not posting exact link) however it says invalid url(coz of .aspx inside it), please provide url root

https://microsoft.sharepoint.com/teams/CECRMSP/**************************/Forms/AllItems.aspx?id=%2Fteams%2FCECRMSP%2FShared%20with%20Microsoft%2FTraining%20Items

I tried this one "https://microsoft.sharepoint.com/teams/CECRMSP" it works however my folder and csv are located inside the sub folders how to get there ?

Please share how to do connections for sharepoint.

And my second question is if I upload new csv file in that location how it will get refreshed please guide me step by step

[v-ljerr-msft]

Hi @harsh,

---

I tried this one "https://microsoft.sharepoint.com/teams/CECRMSP" it works however my folder and csv are located inside the sub folders how to get there ?

---

Are you not able to load any files from sub folders when using the root URL mentioned above? I have tested it on my side. I can load all files(including all files within sub folders) using the root URL like yours. 

In addition, following is another solution that using the Web connector mentioned here. Please go and check if it works.

1. Click Get Data-> Web
2. Provide the full path to your Excel file on SharePoint
3. Select authentication as Organisation and enter your O365 user id/password if needed
4. You should then see a dialog box where you pick the tables you want to pull in

Regards

[harsh]

Hi JerryLi,

I 'm not looking to get the name of the document names in tables as in your case, I 'm trying to open csv file to load data into the tables however I 'm not able to do that since its not able to link the document which I have already mentioned in my question and the other link you gave using web it does not work too, you can check the comments from other people saying "invalid link" and so on same error I 'm getting even after signing correctly to my organisational account.

[v-ljerr-msft · Accepted solution]

Hi @harsh,

---

I 'm not looking to get the name of the document names in tables as in your case, I 'm trying to open csv file to load data into the tables 

---

Indeed, I knew exactly what your requirement is. I posted those photos above just wanted to let you know that I can load all files(including all files within sub folders) using the root URL like yours. We can then load data from csv file into tables in Query Editor. Following is the table I got from my "2016-02-05 Feb.csv" file on SharePoint.

Here is an article about how to loading Excel/CSV Files from Sharepoint in Power BI Desktop for your reference.

Regards

[harsh]

Hi @v-ljerr-msft,

Sorry for replying late, I was on vacation.

Thanks for helping, I followed the steps and it worked for .csv file, is it possible to schedule a refresh ? I was trying to schedule however it gives below error, even thought the credentials are correct. I guess we don't need to install gateway as it is sharepoint site. It would be great if you help me on this.

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

Activity ID:	2a7aa914-b1d4-4027-8744-b3aaae38b008
Request ID:	a97ef2fc-75f9-8709-2748-b932ed665dcd
Status code:	200
Time:	Wed Nov 09 2016 13:47:18 GMT+0530 (India Standard Time)
Version:	13.0.1700.581

[v-ljerr-msft]

Hi @harsh,

Yes, we can schedule a refresh for SharePoint Folder without installing the Gateway. I'm doing this currently and it works all fine for me.

Is it possible you are using different authentication methods for SharePoint credentials on Power BI Desktop and Power BI Service which may cause this issue. Please go and check if it is the case.

Regards

[harsh]

Hi @v-ljerr-msft,

I have "undefined" as 3rd authentication mode instead of OAuth2 ?

[v-ljerr-msft]

Hi @harsh,

That's a little weird. I have never seen this before. I'm not familiar with the authentication mechanism of SharePoint. Is it possible for you to use Window authentication for SharePoint Folder on both Power BI Desktop and the service in this scenario?

Regards

[harsh]

Yeah thats strange, could you please let me know how to make windows authentication in Power bi desktop ?

[v-ljerr-msft]

Hi @harsh,

I just tested it on my side, and I wasn't able to using windows authentication with my SharePoint account in Power BI Desktop either. As I'm not familiar with the authentication mechanism of SharePoint, I would suggest you also go to SharePoint forums for better assistance.

Regards

[Greg_Deckler]

Are you trying to use the SharePoint connector or the CSV file connector?  Should use a CSV connector and easiest way to get link is to navigate to the document in question in SharePoint in a browser, right click and say "Get a link". It should end up looking something like:

https://tenant.sharepoint.com/Documents/Photos/Clippers%20Game%20081616/IMG_20160816_201217206.jpg

This is a link to a document buried 2 folders deep.

[harsh]

Hi Smoupre,

Thanks for your reply, its working Power BI online, however I was trying using Power Bi desktop and I selected csv connector didn't work, not sure whether its supported on not.

This is the complete link of my csv file:

https://microsoft.sharepoint.com/***************/_layouts/15/guestaccess.aspx?guestaccesstoken=Muclyy1iAqyveLeef8aahna6iEkuou43f6SmVkE8e8k%3d&docid=2_15e88281ae5004bad849a3e5bede2a91e&rev=1

It seems it reads till guestaccess.aspx rest it avoids.

The below figure shows some unwanter document showing.