Skip to main content
To homepage To homepage
Solved

Connect to Excel files

E
Contributor
Forum|alt.badge.img
  • ebay
  • Contributor
  • 26 replies

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?

Best answer by fwagner

​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)

View original
Did this topic help you find an answer to your question?

6 replies

fwagner
Employee
Forum|alt.badge.img+4
  • fwagner
  • Employee
  • 33 replies
  • March 24, 2023

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
Contributor
Forum|alt.badge.img
  • ebay
  • Author
  • Contributor
  • 26 replies
  • March 24, 2023

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1017 replies
  • March 24, 2023

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
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • 649 replies
  • March 24, 2023

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
Problem Solver
Forum|alt.badge.img+4
  • rvgfox
  • Problem Solver
  • 222 replies
  • March 24, 2023

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

How can I setup it?

fwagner
Employee
Forum|alt.badge.img+4
  • fwagner
  • Employee
  • 33 replies
  • Answer
  • March 25, 2023

​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


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings