Is there any way of converting .xlsx file to .csv file format in Azure Blob Storage?

Question

I am trying to copy data from Excel(.xlsx) format stored in Azure Blob Storage to Azure SQL database using Azure Data Factory V2. As Azure Data Factory doesn't provide direct service to Excel files, excel file need to be converted to some other format easily readable by Azure Data Factory, for instance,.csv file. I went through some articles and found that Azure Logic App is one of the ways to convert the .xlsx files to .csv files and tried to implement this approach but couldn't succeed. I would like to know if Azure Logic app could be used for this task and if not, what could be the better implementation to achieve this requirement.

Any help/suggestions would be appreciated.

Thanks.

Answer 1

It's not feasible to do the conversion in place, since Azure Blob Storage is just object storage which treats all formats in the same way. Therefore, you must download your Excel blob to your local machine and convert it to CSV, then upload it back to Azure Blob Storage.

Answer 2

It is possible to save to csv from within excel: knowledgebase

Alternatively, you can use VBA to automate the process analysistabs as follows:

Sub vba_code_to_convert_excel_to_csv()
    Set wb = Workbooks.Open("C:\temp\testwb.xlsx")
    wb.SaveAs fileName:="C:\temp\testC.csv", FileFormat:=xlCSV, CreateBackup:=False
End Sub