![Pull Pull](/uploads/1/2/5/4/125493277/106108279.png)
This tutorial explains how to index tables on specific websites and extract real time data into an Excel spreadsheet.
Hi, I have a requirement - Have to read the excel data say e.g. col A,B,C data once the excel is added in the document library. Pull the contents(data) of the excel, which is uploaded in the doc libary and then store the fetched datas into the SQL table. May I please know the possible solution to get the data of excel uploaded in the Document Library and the best approach to go with. Is there any object model code to read the excel content(data)?
Awaiting the response! Please help me out. Thanks, Niranjan. Hi Just would like to know if the OpenXML Api will support reading the excel Yes, this will support. I have to write the code in event receiver. If you are writing event receiver then use object model code to read excel.
Here is some few threads with code for your ref: Hope it could help Hemendra: 'Yesterday is just a memory,Tomorrow we may never see' Whenever you see a reply and if you think is helpful, click ' Vote As Helpful'! And whenever you see a reply being an answer to the question of the thread, click ' Mark As Answer Please feel free to unmark answer if does not resolves your problem. Hi Hemendra, Thanks for the reply. I am trying with the OLEDB connection way of accessing excel content, but I understand that it cannot read the file directly from SharePoint Document library. Rather we have to download it to the temporary loaction(file system) and then read the content/data from excel file. Any other good approach to go for reading the excel content directly from SharePoint Library and then stroing the same content in SQL DB Table I have used Interop.Excel dll and I could read the excel data correctly, now I need to store the result obtained to the SQL table.
Load Excel Files from SharePoint This is a quick tutorial on how to load Excel files from a SharePoint page. SharePoint is a nice landing place for your data because it can be connected to the PowerBI.com service and thus can be used to schedule refreshes of data within your company (if you already have a SharePoint o365 account).
This tutorial will be a slightly different than my previous tutorials as I don’t have a publicly available SharePoint site that can be used to connect to. So you will have to slightly adapt what I’m presenting to you to fit your particular SharePoint needs. First you must start off with a SharePoint with a document library that includes an Excel file. Sharepoint Location The document library is titled SampleDocs, and the file we want to bring into PowerBI is called SampleData.
Clicking on the Home in the left navigation will take you to the home location of the SharePoint site. Copy down the HTML site address from your browser of this location it should look similar to the following: Site Name/SitePages/Home.aspx Open up PowerBI Desktop and on the home ribbon click Get Data. Highlight the SharePoint Folder and click Connect to continue. SharePoint Folder Connection Upon clicking connect you will be presented with another screen asking for the SharePoint folder location. In the URL window you will add the SharePoint site that we identified above. However, it is important to note that you don’t need the entire web address.
Rather PowerBI only needs the specific site name, thus all that needs to be inserted into the URL field is highlighted below in Red. Site Name/SitePages/Home.aspx The ending “Sitepages/Home.aspx” can be removed.
Enter Shortened Site URL Clicking ok will present a authentication screen. Depending on your company or SharePoint authentication you will need to enter the credentials to log into the SharePoint Site. You may have to try a couple different connection methods until you are able to properly connect to the SharePoint site. In my example I had to select Organization Account then click the Sign in.
I signed in with my credentials given me via my I.T. Also, I had to use the drop down to select the proper level to apply the settings. I used the same address as listed above: Site Name/ User Sign In Page After signing in click Connect to proceed. PowerBI Desktop will then load all the files from the SharePoint site in a preview window.
Click Edit to modify the query. Query Editor View We can now see our SampleData File and the folder path. Each document library will be a separate folder path, thus if you have multiple document libraries then you will have all the files in those different folder paths. Next click the double down arrows to load the excel file. Load File Power BI Desktop will then go to the SharePoint site and download the information inside your excel file. For my data I have all the information retained in a table within my excel document.
The table name is call MyDataTable. Thus, clicking on the Table link in the MyDataTable row I will be able to open all the data within this table. Load Table of Data from Excel File Finally the data is loaded from the excel table.
Click Close & Apply on the Home ribbon to load the data into PowerBI. Note: It is always important to check your columns and verify that your data types are correct. Highlight each column and make sure you select the proper Data Type for each column. Data Type can be found on the Home ribbon.
Final Load Data Thanks for visiting. Make sure you stop by again for more great tutorials. I have seen this error multiple times. Here are a couple of ideas to help you with the credentials error.
First, you will want to make sure that your SharePoint is part of the O365 service. SharePoint can be implemented at companies only on premise. When the SharePoint installation is only on premise there isn’t away to connect to PowerBI.com the service via a refresh. If your company is running SharePoint you will need to make sure your running the latest version of SharePoint if it is hosted on O365. Second, when you connect to SharePoint in the desktop there will be multiple credentials selections.
Depending on how your organization is set up you may need to try different combinations of credentials to allow you to connect to SharePoint. When I connect to my installation of O365 SharePoint I use the credentials for “Organizational account”. Also, there is a drop down that selects the online location to apply the settings.
When I sign in I use the most detailed credentials such as ( site name). Last, if you are having a refresh issue on PowerBI.com on your loaded dashboard you can re-enter the credentials for that dataset. After you have logged into powerbi.com click the gear icon on the right side of the screen. Select the Settings button from the drop down list. Then select Datasets which are listed at the top of the window. On the right side of the screen select the dataset which is having credential issues.
Then under the drop down click Edit Credentials, and re-enter your SharePoint credentials. Hi Mike, I am actually having the same issue.
I’ve tried in two accounts: a OneDrive for Business trial account (with O365 not activated) and in a corporate O365 with OneDrive for Business (don’t know how is the integration between O365 and OneDrive/Sharepoint). In both cases I face the error of the data source refresh error. The data source is called ShrepointListOnPremise and it seems that the problem is happening to many people these days. The issue has been reported here:.
Anyway if you find a workaround for this, would be much aprreciated! Regards, Fran. Thanks for the feedback.
I’m sorry your having some serious issues with the connection piece. In my organization I am not the O365 administrator thus, when I ran into connectivity issues I had to engage my I.T. Group which in turn had to work with Microsoft. I believe there is a setting in either the administrator part of PowerBI.com or on SharePoint that allows the two services to talk to each other.
One way of testing this is to log into PowerBI.com and try to connect to the SharePoint Team Site. This may help you out. If you are unable to connect to your SharePoint team site through the PowerBI service then there is most likely a setting not turned on.
You would need to engage your I.T. Administrators to trouble shoot this.
If you are able to connect to the SharePoint team site, then the credentials that your using to log into the SharePoint site are most likely not correct. You would need to try different log-ins such as Organizational. Hope that helps. I was having this exact same issue when i first started trying to load data from SharePoint. Here are a couple of things to check. You will most likely need to be an admin of the site.
I have had issues with users trying to connect without having a higher level of access to the site. The SharePoint I am trying to connect to is within the O365 system, therefore there maybe issues your having which is due to a on premise install of SharePoint. Make sure your cutting down your SharePoint address enough.
Start at the home page for the full address. My site home address is something like, then I needed to cut down this address to the following for it to work, Hope those trouble shooting options help. Hi Mike – I’m trying to load a pbix file and it seems like it might not be supported. The ultimate requirement is to use sharepoint (or alternative) to store pbix files for version control. Using desktop or service (to ultimately create/publish apps), connect to the pbix on sharepoint (using get data) and load the file. Then as we make changes to the pbix in sharepoint, have it automatically update in desktop or the service using a refresh. If that doesn’t work we could reload the file.
I tried your instructions above but for a pbix file but it looks like it’s not supported when i click the double arrows in the editor. Any suggestions on how to accomplish what I’m looking for. If you have suggestions other that sharepoint, I’m all ears.
I hope this makes sense ?. Super good question, I was just working on this exact issue today. Then I found this article.
It’s not super clear, but I think this feature is available now. Here are the rough steps. Log into the PowerBI.com service 2. Create a new workspace (it takes about 30 mins) for the files option to show up. Then click the ellipsis next to the newly created workspace, this should let you see a item called Files. Click the files option, then a sharepoint page should open up allowing you to add files 5.
Place your PBIX file in the sharepoint library 6. Go back to PowerBI.com then open up the workspace 7. Click get data 8. Then click on files 9.
You should see a new option for one drive with your workspace name 10. Your PBIX file that you uploaded to the sharepoint workspace should be there. Then you can click the PBIX file there and load it to PowerBI.com. Good luck, I hope that helps. One thing you will need to make sure is that you are using SharePoint online. Power BI.com the service needs to connect to the Microsoft offered SharePoint Sites.
If your SharePoint sites are locally hosted within your company then the PowerBI.com service will not be able to connect. An alternative approach would be to put the files you need to access in PowerBI.com directly into the attached workspace of the PowerBI work group. Here is an article that shows you how to access this SharePoint site. The data will only change when the report is refreshed. You can schedule up to 8 refreshes for each dataset on PowerBI.com If you schedule a refresh, and the excel data changed, then the change will be loaded into the data model. It does not harm things to have the schedule refresh conducting a refresh if the excel data hasn’t change.
In this way you can connect your data to SharePoint online, set up the dataset to refresh and then modify the excel files until your heart is content. When the Power BI data model is refreshed it will automatically pick up the changes. If you need to trigger this on demand you can click the ellipsis on the dataset and select refresh now. This will manually force the refresh to happen. When you do this, it does not count against your scheduled refresh limit per day of 8. An alternative approach would be to not use Sharepoint but use Azure Blob storage to access the files. I have use Azure blog storage a couple of times but not a ton.
This might speed up some of your performance issues. One thing I have noticed is if the data you are collecting from sharepoint requires a lot of transformations the data loading process can get slow, as you have described. When I have had issues in the data transformations in the Query Editor, I have had to build more robust data collection items. Thus, I have created a process to load data into a SQL server then do the transformations using views, and finally pull the data through to Power BI.