Azure Synapse Analytics or Data Factory: How encode Excel Files?

Question

I'm looking to convert Excel Files(.xlms) to CSV.
In blob, however, Excel File is garbled. So I cannot convert Excel file.

In this state, I cannot select worksheet when create pipeline.
error message: work sheet is not exist. is displayed.

I have some question.

Q1: In Synapse, how encode Excel files?
Q2: Can I convert Excel to csv using Python without encoding?

I am Japanese, so I am sorry for poor English.
Any help would be greatly appreciated.

Thank you.

Answer 1

Hello @Kakehi Shunya (筧 隼弥) ,

Thanks for the ask and using MS Q&A portal.

As per my understanding, you are trying to copy data from a particular sheet in excel file and write to a csv file. Please correct me if my understanding is wrong.

You will not be able to look at the content of excel file from azure portal.

Please try the below steps to implement a copy activity to do the job for you.

1. Create a pipeline in data factory or synapse
2. Add a copy activity and under the source, create a data set for Excel file and point to the excel file in the Blob\Data Lake storage.
3. Once you have pointed to your excel file, you will be able to choose the sheet from the dropdown.
4. In the Sink, add one more data set to copy the data from source to a csv file.

Please note that if your source is .xlsm, its not supported yet by the excel connector.

Kindly refer to this documentation for more details.

Please revert in case you have further queries

Thank you