Is there any automated way of exporting Azure SQL database table data to Excel?

Question

I am trying to automatically export the Azure SQL database table data to Excel sheets.I tried to achieve it with Azure Data Factory but couldn't succeed as Azure data factory doesn't have direct support for Excel. I found in some documentation where it was mentioned that SQL database should be exported as text file first. Following that documentation, i exported the SQL database data as CSV file in Azure Blob Storage using Azure Data Factory and couldn't proceed further. Is there any way to convert that CSV in Azure Blob to Excel in automated way? Are there any better alternatives for the overall process?

Answer 1

Exporting data from SSMS to Excel using Copy-Paste from DataGrid or exporting to .csv will cause the loss of the data types, which again, will cost you additional work when importing these data into Excel (import as text).

I have developed SSMSBoost add-in for SSMS, which allows to copy-paste data to Excel using native Excel clipboard format, preserving all data types.

Additionally, you can use SSMSBoost to create ".dqy" query file from your current SQL Script and open it in excel (in this case Excel will use provided connection information and SQL text to execute the query directly against your database). I hope this helps.

Answer 2

You can have an Azure Function Activity in your Azure Data Factory pipeline and chain it to your Copy Activity. By chaining the activities, you are making sure that the Azure Function Activity is invoked only once the csv file is written successfully.

In the Azure Function, you can use a language of your choice to write code to convert the csv file to xls.

There are a bunch of libraries that you can use to convert csv to xls. Some of them are below :

• Simple Excel for Javascript
• Some ideas to do it in Java
• Python

Hope this helps.

Answer 3

I didn't find the way to convert that CSV file in Azure Blob to Excel in automated way. But I find that there is tool FileSculptor can help you convert csv file to Excel automatically with scheduled tasks.

Main Benefits:

• Convert between file formats CSV, XML, XLS and XLSX
• Support for spreadsheets from Excel 97 to Excel 2019
• Support for international character sets (unicode)
• Select and reorder fields
• Create calculated fields
• Create an icon on the desktop to convert files with one click
• Automatically convert files using scheduled tasks
• Additional utility to convert files from DOS command prompt or .BAT files.

For more details, please reference this tutorial: Convert Between CSV, XML, XLS and XLSX Files.

And about how to export Azure SQL database to a csv file, this tutorial How to export SQL table to Excel gives you two exampes:

1. Export SQL table to Excel using Sql to Excel Utility. Perhaps the simplest way to export SQL table to Excel is using Sql to Excel utility that actually creates a CSV file that can be opened with Excel. It doesn’t require installation and everything you need to do is to connect to your database, select a database and tables you want to export.

2. Export SQL table to Excel using SSMS.

I did't find the way which automatically export the Azure SQL database table data to Excel sheets. Azure SQL database doesn't support SQL Server agent.

Hope this helps.