Copy Activity throwing error - "Only '.xls' and '.xlsx' format is supported in reading excel file"

Question

Hi Champs, I'm totally clueless with a problem. Please help. Problem: I need to download a file from API and store it in Azure SQL. We know file is in excel format but is getting downloaded without extension. example: actual file is "ABC.xlsx" but is getting downloaded as "ABC" I'm using a Copy activity with HTTP linked service and Excel dataset. I can preview the data as well. I can import mapping. But when I'm executing the pipeline with Azure SQL as sink, I'm getting below error

I can't use Blob for initial staging. How I can do this? Please help. This is bit urgent. Thanking you in advance. Regards, Partha

Answer 1

Hi, After your file is downloaded, it will not be easy to convert the file to .xlsx in ADF without using custom programming. This is due to the fact that, ADF has not got a .xlsx based sink.

Instead of using .xlsx based dataset, you can use a delimited text file in the source, which will work even when not mentioning the extension.

But if you want to store the file as .xlsx for further usage, only option i see is to use Az. Functions or Databricks Notebook to convert the extension less file to .xlsx file using Pandas\C#.Otherwise, if you are able to manage to download the file name with extension from API, it should be straight forward to use copy activity to load to SQL

Please let us know for further questions/ doubts on this. Thanks

Answer 2

Hi Pratim Das, Partha C , I understand that you are facing an issue while trying to download an excel file from an API and store it in Azure SQL, but the file is getting downloaded without an extension. Error says "Only '.xls' and '.xlsx' format is supported in reading excel file". Could you please clarify why you cannot use Blob for initial staging?

You could try to load the file into ADLS first and then use ADLS gen2 connector with excel format and sink as SQL table.

Hope it helps. Kindly let us know how it goes. Thankyou