To homepage To homepage
Solved

Loading Excel files from OneLake

  • 22 December 2023
  • 11 replies
  • 236 views
erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi,

I’m trying to load an Excel file from a OneLake. As OneLake is a rebranded version of Data Lake Gen2, my assumption is that it should be possible to use existing CData features to load data.

I have a working setup where we use CData’s Excel provider (version 23.0.8585.0) to load an Excel sheet from a Data Lake Gen2

 

I explicitly configured the connection type as ADLS Gen2. Actually I was hoping that this configuration would work after changing the [Azure Storage Account] and [URI] to the OneLake parameters, but unfortunately that doesn’t work

 

Error message:

 

 

Looking into the logs, I see that CData tries to connect to *.dfs.core.windows.net. However, the uri of OneLake is *.onelake.dfs.fabric.microsoft.com. Manually entering the abfss:// uri did not work here

 

Is it correct that CData is not yet compatible with OneLake, even though techniques under the hood are similar? Documentation on https://cdn.cdata.com/help/RXJ/ado/default.htm does not mention it (yet).

Did anyone manage to load data from OneLake yet?

In addition: it’s not a matter of rights/RBAC. We’re using a service principal which can access the OneLake files. I tested this with azcopy.

Best,

Erik

NB: Just investigating a use case scenario here, we’re not actively using OneLake yet

icon

Best answer by Christian Hauggaard 12 January 2024, 09:47

View original

11 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @erik.van.mastrigt 

Are you using the OneLake file explorer? If so, please see an example of a URI below

URI=C:/Users/abc/OneLake - Microsoft/onelaketest/Test12.Lakehouse/Files/account__c.xlsx

 

 

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Christian Hauggaard ,

Forgot to mention, but we looked into that option. However, OneLake File Explorer is not compatible with Windows Server 2019; only Windows 10/11 are supported. TX is running on a Windows Server 2019 machine.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

@erik.van.mastrigt I reached out to Cdata and they replied that this is unfortunately not possible at the moment without using the OneLake File Explorer. Do you have any follow up questions?

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

@Christian Hauggaard Nope, thanks. Would be nice if CData supports this in future releases. For now we will use azcopy to make a local copy first.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @erik.van.mastrigt 

Did you read my guide?

I specify how you set up the path, the path applied in the image looks incorrect. Specifically the marked part.

You also likely need to set the Auth Schem to AzureAD. The AzureServicePrincipal gives errors some times and the AzureAD still works when you apply OAUTH.

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Thomas Lind 

Yes I did, tried multiple variations of URL. Point is that the CData connector will always look for a Data Lake url, and is not familiar with One Lake urls.

AzureServicePrincipal worked fine for Data Lake, but not for One Lake. Switching to OAUTH only returned errors.

Thomas Lind
Rank
Userlevel 6
Badge +5

You are not in the newest edition, if OneLake is as new as you say, it may be necessary to use  build 23.0.8750.0

Also I did not mean either.

It must still be a storage account you have the excel file in right.

 

  1. Storage account name
  2. Tenant, so it knows what company it is located in.
  3. Connection type, container name, folder and file location

 

Thomas Lind
Rank
Userlevel 6
Badge +5

If this does not work, there likely are no way using OneLake with this specific method.

I mean you will not be able to use the abfss://, azureblob:// and azurefile:// options and will have to find a URL that returns this file.

If this doesn’t exist or do not work, there is  no way to use this to connect to the files.

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Tried several options, but none of them work. I leave it for now as it is not a big deal at the customer. Using azcopy is pretty easy and keeps the local files in sync with OneLake. 

Thanks for your help though!

Thomas Lind
Rank
Userlevel 6
Badge +5

Christian found this link about shortcuts.

https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts

Maybe that can be used to point at the files. It does seem to require some setup and in the end it also suggests that onelake is a layer on top of the normal data lake gen2 which you can then pass through to connect to the files.

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Thomas Lind ,

I looked into this suggestion, but the article describes how to make a shortcut in OneLake, so one can read files from the Data Lake. Point is that we cannot read from OneLake at all with CData. Reading from a Data Lake works fine.

Think we just have to wait until CData supports reading from OneLake.

Reply


Terms and ConditionsCookie settings