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,

https://support.office.live.com/Article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e?ui=en-US&rs=en-US&ad=US

Supported data sources

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 2^nd, 3^rd and 4^th 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