topic Re: Export to Excel in SQL Endpoints / Warehouses is still not working. in Data Warehouse

Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Fri, 13 Sep 2024 19:01:48 GMT

Will the function to export a Query to Excel ever work? Been using Fabric for almost a year now and this has not once worked.

I am talking about this option here:

Even the label is currently bugged as it used to only say Analyze in Excel.

You are prompted to select the SELECT statement from the Query Editor, but the resulting Excel file is always empty. This has never worked since Fabric launched. I understand this is probably a bug and a ticket must be submitted, but I perhaps there is a workaround I am missing.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

lbendlin — Fri, 13 Sep 2024 23:29:06 GMT

Have you tried to connect to the SQL endpoint directly from Excel (via the Azure SQL database connector)?

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Sat, 14 Sep 2024 15:30:39 GMT

Yes. I am able to connect to the SQL Endpoint as an Azure SQL Database connection using vscode, DBeaver and other tools.

But given there is an intended option to extract directly a single quick ad-hoc query, I'd like to see if there is a way to get it working.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

FabianSchut — Sat, 14 Sep 2024 15:55:08 GMT

Hi JosueMolina,

I personally use Azure Data Studio to create some ad-hoc queries connected to the Fabric Warehouse/Lakehouse. Within Azure Data Studio, you have the ability to directly export this to Excel (and CSV, JSON, Markdown, XML or see it as a chart). I think this option can work for you too. You can install Azure Data Studio here:
https://learn.microsoft.com/en-us/azure-data-studio/download-azure-data-studio?view=sql-server-ver16&tabs=win-install%2Cwin-user-install%2Credhat-install%2Cwindows-uninstall%2Credhat-uninstall

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Sat, 14 Sep 2024 16:02:12 GMT

Hi! Thanks FabianSchut , but I am interested in understanding if there is a way to get the Analyze in Excel option working within the Warehouse itself. We've been resorting to an external connection to the Warehouse / Endpoint already to get the information we require at times. Given that we have a LARGE amount of Lakehouses/Endpoints/Warehouses, we must establish a new connection for each. Doing this for a one time query is inefficient.

Appreciate the help, but once again, I am interested in getting the button to work or getting an Excel file directly from the Fabric experience without resorting to SQL connections via another software.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

lbendlin — Sat, 14 Sep 2024 17:22:12 GMT

if there is a way to get the Analyze in Excel option working within the Warehouse itself.

It's a warehouse, not a cube. Analyze in Excel only works with cubes.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Sat, 14 Sep 2024 17:41:55 GMT

Hi. I believe there is a misunderstanding on what I am trying to do.

There is an option within the SQL Query editor inside a Fabric Data Warehouse and SQL Endpoint. Currently, the label is bugged and it's saying `AnalyzeInExcelASonPremWarning_OpenInExcel`.

I think it used to say `Download Excel File`. This is an option in the SQL Query editor UI once you have a Select Query. You can see the grayed out button in this image I pulled from Microsoft Learn. I probably should have used the old label that the button had. Here is the image:

This is an option intended to be used for a simple query. I'm not trying to do connect to the whole Warehouse / SQL Endpoint, just want to be able to get a query into an Excel file, so I can do some quick Exploratory analysis. I want to be able to get this button to work.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

lbendlin — Sat, 14 Sep 2024 17:46:07 GMT

"Download to Excel" is fundamentally different from "Analyze in Excel". Hopefully they get the terminology right some day.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Sat, 14 Sep 2024 18:10:02 GMT

Also, to add to this.

The button is not currently grayed out, just in that sample screenshot.

The button is clickable, but only if you have a SELECT query statement highlighted in the SQL Query editor (or a table in the Visual editor I believe, as well).

The problem is that after highlighting the SELECT statement and clicking on the Download Excel File button, or whatever label it has now, your browser will download an Excel file, but it will be completely empty.

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

Anonymous — Thu, 19 Sep 2024 01:54:30 GMT

Hi JosueMolina ,

Thanks for the reply from lbendlin / FabianSchut .

I did some testing and could not reproduce your error, the “AnalyzeInExcelASonPremWarning_OpenInExcel” option worked fine for me.

Below are my test steps:

1. After running the SQL statement in warehouse, select the SQL statement and click on “AnalyzeInExcelASonPremWarning_OpenInExcel”, an excel file will be downloaded.

2. Just open the excel is blank, need to enable editing.

3. Click enable content to configure the connection information, and sign in with the account that is logged in the power bi service.

4. This prompt appears, click connect on it.

5. It works fine.

Please check your steps again, if the problem still exists, can you please share your steps with me, preferably in the form of moving pictures.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

Anonymous — Tue, 24 Sep 2024 07:03:57 GMT

Hi JosueMolina ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

Re: Export to Excel in SQL Endpoints / Warehouses is still not working.

JosueMolina — Tue, 24 Sep 2024 14:23:08 GMT

Forgot to reply here. While this is not what I was looking for and I was unable to get my connection to work, this does seem to be the intended way of working with this option.