To homepage To homepage
Solved

Connect to Excel files

  • 23 March 2023
  • 6 replies
  • 252 views
E
Rank
Badge

I'm following the steps explained on this page https://support.timextender.com/data-sources-112/connect-to-excel-files-with-excel-online-628#Connect+with+the+Excel+Online+CData+provider, but I couldnt get it. I used Microsoft SharePoint Excel Provider.


I have a 48 Excel file. I uploaded them to my company's onedrive. I then gave OAuth to Cdata. But I took this error. 

‘You must specify an Excel file: Set the FILE property to an .xlsx file.’

 

The file url address where the excel files are located like this:

https://companyname-my.sharepoint.com/:f:/g/personal/xxx_companyname_onmicrosoft_com/xxxxxxxxxxxxxxxxxxxx

 

  1. Now I have written this above address in the 'URL' section under 'Authentication'. Is this true? Or should I specify in the 'Folder' and 'File' sections in the 'Connection' section?
  2. Which Auth Scheme should I choose?
icon

Best answer by fwagner 25 March 2023, 13:23

View original

6 replies

fwagner
Rank
Userlevel 2
Badge +3

Hi @ebay,

Thank you for your question!

Have you tried going with the regular excel connector? As per the docs at https://cdn.cdata.com/help/RXH/ado/pg_connection.htm there’s a specialized URI scheme to connect to OneDrive: onedrive://remotePath/file.xlsx 

Additional explanations on how to authenticate using OAuth at https://cdn.cdata.com/help/RXH/ado/pg_connectiononedrive.htm

However, the data source is always pointing to one particular excel file, not to a folder as you can do e.g. with the CSV provider - since you mention 48 separate files that would mean 48 data sources.

Is there any way you can get the data in another file format, such as csv, parquet, …?

E
Rank
Badge

Hi @fwagner 

Thanks for the answer. ​​​​​​Yes, unfortunately these solutions are only for an excel file. There are separate sheets in each Excel file. So the cost will increase even more. I guess I'll have to convert them all to csv files.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Erdem

If the Excel files are located in sharepoint and you want to connect to them in one data source you can do so with the Excel Online data source. You found my guide I see.

The thing is you will need to set up an app to gain access, this can be difficult.

I suppose this is where you got stuck. This is something to look into an app is the most important thing when working with non SQL Microsoft data sources.

rory.smith
Rank
Userlevel 5
Badge +7

If your Excel files on OneDrive are hosted via SharePoint, you may be able to use the SharePoint Online way of using the CData connector as this can handle a folder: https://cdn.cdata.com/help/RXH/ado/pg_connectionsharepointonline.htm

 

This also authenticates via OAuth. I believe the SOAP variant no longer works / will no longer work after all the basic authentication shutdown activities Microsoft is rolling out.

rvgfox
Rank
Userlevel 3
Badge +1

@Thomas Lind Is it possible to connect to the excel files stored in an Azure storage accoun (container)?

How can I setup it?

fwagner
Rank
Userlevel 2
Badge +3

​Hi @ebay,
I was just able to successfully test this.
Here are the steps you need to take:

  • safe Excel files to OneDrive
  • Create an application registration on portal.azure.com as explained in 

 

  • The app registration needs these delegated permissions: Files.Read, Files.Read.All, Files.Read.Selected, Sites.Read.All, offline_access
    • set callback url http://local host:33333 for the app
    • create an app secret
  • create a Excel Online data source on app.timextender.com with these parameters:
    • Auth Scheme: AzureAD
    • Initiate OAuth: GETANDREFRESH
    • Callback URL: http://local host:33333
    • OAuth Client Id: <your app client id>
    • OAuth Client Secret: <your app client secret>
    • OAuth Grant Type: CODE
    • (no need to specify Drive or SharepointURL if you’re using your personal OneDrive)
  • map the data source to your ODX instance
  • in TimeXtender desktop add the data source
  • edit the data source, select “Authorize OAuth”, grant permissions in the browser window that pops up

     

  • verify with “Test connection” that OAuth worked
  • Now you can list workbooks and sheets within, and transfer data

     

 

(please note that “local host” should of course not have a space when you insert it as callback url, I needed to mis-spell it here due to filtering reasons)

Reply


Terms and ConditionsCookie settings