How to Excel file(.xlsm) transform on Azure Synapse Analytics.

Question

Hi, I wanna transform Excel file(.xlsm) from Blob → Dedicated SQL Pool.

I tried to create the pipeline but there are some problems.

1.Using "Power Automate" to upload Excel file(.xlsm) from Outlook365 to Blob storage automatically and that process succeeded.
In Blob storage, however the Excel file is garbled. Can I encode it in Azure Synapse Analytics??

2.I wanna transform Excel file(.xlsm) into CSV file using Python script, can I do that process on Azure Synapse Analytics??

Answer 1

Hello @Kakehi Shunya (筧 隼弥) ,

Thanks for the question and using MS Q&A platform.

You can use pandas and openpyxl to convert Excel to CSV in Azure Synapse Analytics.

Note: openpyxl is a library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.It was born from lack of existing library to read/write natively from Python the Office Open XML format.

The below code snippet helps to convert Excel to CSV:

# importe required libraries  
import openpyxl  
import csv  
import pandas as pd  
  
# open given workbook  
# and store in excel object  
excel = openpyxl.load_workbook("Test.xlsx")  
  
# select the active sheet  
sheet = excel.active  
  
# writer object is created  
col = csv.writer(open("tt.csv",  
					'w',  
					newline=""))  
  
# writing the data in csv file  
for r in sheet.rows:  
	# row by row write  
	# operation is perform  
	col.writerow([cell.value for cell in r])  
  
# read the csv file and  
# convert into dataframe object  
df = pd.DataFrame(pd.read_csv("tt.csv"))  
  
# show the dataframe  
df  

For more details, refer to Convert Excel to CSV in Python.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators