Refreshing Excel files which has Data Sources not supported by PowerBI.
Consider you have Excel file which has Data coming from SharePoint list or DB2 or Teradata or MySQL (which is not through PowerQuery) and you want to refresh this workbook in PowerBI, how will it work?
As per,
Supported data sources
Source |
Versions |
Provider |
|---|---|---|
SQL Server relational databases |
|
|
Oracle relational databases |
|
|
Power Query |
|
So if it is not PowerQuery it will not work.
Also few connections in PowerQuery also has few limitations like SharePoint List,
For SPO list online we support Anonymous authentication for refresh however it almost impossible to get anonymous access to SPO resources like list under team site.
So through normal way of PowerBI scheduled data refresh we will not be able to refresh such workbooks.
So how to do that?
This thoughts lead me to do some digging and I end up writing a code which will load excel workbooks from SharePoint Online (PowerBI document library) and refresh them locally and then push those excel files back to SharePoint Online document library.
I have tested this code and it works well.
Only glitch would be if you have connection which might not have credentials stored in them first time when we try to refresh it will ask for credentials and for subsequent runs it will not pop up anything.
First important thing would be assemblies we need to reference,
So you need to add 2nd, 3rd and 4th ones manually.
Next thing will be imports in code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Client;
using System.Security;
using System.IO;
using Microsoft.Office.Interop.Excel;
And finally code itself,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Client;
using System.Security;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace SPO_ReadOnlyColumns
{
class Program
{
static void Main(string[] args)
{
//Create Connection to SPO for fetching Excel files
ClientContext cont = new ClientContext("https://microsoft891.sharepoint.com/PowerBI");
SecureString Password = new SecureString();
string password = "*********";
foreach (char c in password.ToCharArray())
{
Password.AppendChar(c);
}
cont.Credentials = new SharePointOnlineCredentials("********@microsoft891.onmicrosoft.com", Password);
Web web = cont.Web;
cont.Load(web);
cont.ExecuteQuery();
//At this point we are connected to SPO
//Connect to Document Library which has Excel files which need to be refreshed , for me it was BI
var PowerBI= web.Lists.GetByTitle("BI");
var query = CamlQuery.CreateAllItemsQuery();
query.FolderServerRelativeUrl = string.Format("/{0}","PowerBI"); //This is relative URL for document library, My BI document library was under PowerBI folder
var items = PowerBI.GetItems(query);
cont.Load(items,ic=>ic.Include(i=> i.FileSystemObjectType,i=> i.File)); //Load only files for this document library
cont.ExecuteQuery();
//At this point we have fetched list of files into items variable
//Create object of excel application
Application aexcel = new Application();
aexcel.Visible = false;
aexcel.DisplayAlerts = false;
//At this point object of excel app is created
//Loop through each file and either refresh all or refresh conditionally
foreach (var Ex_f in items)
{
Microsoft.SharePoint.Client.File Ex_File =Ex_f.File;
if (Ex_File.Name == "RefreshedFromCode.xlsx")
{
try
{
Console.WriteLine("Loading and refreshing file " + Ex_File.Name);
FileInformation info = Microsoft.SharePoint.Client.File.OpenBinaryDirect(cont, Ex_File.ServerRelativeUrl);
Stream sr = info.Stream;
//Save local copy of file at C:\Temp
FileStream fs = System.IO.File.Create(@"C:\temp\" + Ex_File.Name);
sr.CopyTo(fs);
fs.Close();
//Local file created
//Create workbook object and refresh
Workbook wb = aexcel.Workbooks.Open(@"C:\temp\" + Ex_File.Name, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
wb.RefreshAll();
wb.Close(true);
//refresh completed and close workbook
Console.WriteLine("Refreshed file " + Ex_File.Name + " Successfully");
Console.WriteLine();
//Start uploading workbook file back to SPO
Console.WriteLine("Starting upload of file "+ Ex_File.Name);
FileStream fs_to_upload = System.IO.File.Open(@"C:\temp\" + Ex_File.Name,FileMode.Open);
Microsoft.SharePoint.Client.File.SaveBinaryDirect(cont,Ex_File.ServerRelativeUrl,fs_to_upload,true);
Console.WriteLine("Upload of file "+Ex_File.Name+" Succeeded; :)");
//Upload to SPO completed
}
catch (Exception ex)
{
Console.WriteLine("Refreshing or Uploading of file " + Ex_File.Name + " failed. Error: " + ex.Message);
}
finally
{
Console.ReadLine();
}
}
}
//At this point we ahve refreshed file and uploaded it back to SPO
}
}
}
I hope this helps few of you out there.
If any help needed or if something breaks in this code feel free to add comment here and I will try to have a look at it.
I have attached code which takes command line parameters for running from command line and scheduling, there are total 5 parameters
1) Site web app URL
2) Username
3) Password
4) Document library which has excel files to be refreshed
5) Relative path to document library from web app
Attached zip file is for 32 bit exe but if you have VS you can change that to 64 bit.
And That's all, i will sat bye at this point.
See you at some other time!!!
SPO_ReadOnlyColumns_X86 (5).zip
Comments
Anonymous
October 15, 2014
Thank you for the code, it helped. I thought originally instead of copying the file locally just use the mapped drive, but this solution looks to be simplier.Anonymous
February 04, 2015
Have there been any developments on this front? We just want to use Excel or Power Bi to graph SPO List dat and SPO and want a refresh to reload. I wonder why the restriction and what the thinking is.Anonymous
February 05, 2015
Hi UM, I'm no more with Microsoft so don't have any internal details!! ;) So far from search I did not find anything new so looks like no new stuff so far. I hope this feature and many more gets added soon. Thanks, DilkushAnonymous
February 17, 2015
The comment has been removedAnonymous
February 17, 2015
Hi Howard, By looking at screenshots looks like you are trying to refresh workbook using refresh button of excel web app. It will not work. Do schedule refresh and show me error when it fails. Also why to go through powerquery when we can create direct SQL connection from PowerPivot? Are you planning to use any specific PowerQuery only features? Also if direct SQL connection works for you then Odata feed will also be not needed. I would suggest create SQL Connection in PowerPivot and fetch needed data. Copy connection string from connection manager advance editor. Use that connection string to create data source in PowerBI admin center. Create schedule refresh and see whether it works or not. If doesn't work email me because then discuss would little lengthier and comments might not be best way. Thanks, Dilkush