I want to retrieve Excel data with synapse notebook activity

Question

In synapse notebook activity, I want to use python to extract only sheets 2-4 in Excel and convert them to CSV files respectively.

I was able to extract only one specific sheet and convert it to a csv file, but I cannot output multiple sheets to a csv file respectively.

Since the Excel file is a macro-enabled file, you cannot use the macro-enabled file unless it is a notebook activity.
For your convenience, you cannot change a macro file to a regular file. So, I would like to know the python code that can convert multiple sheets of Excel into csv files in notebook activity.

I'm sorry that I have not studied enough and it is difficult to understand in the translated English.

Regards

Answer 1

Hello @NishimuraChinatsu-9854 ,
Thanks for the question and using MS Q&A platform.

As I understand, you want to convert .xlsm -> csv. You want to do this in Python in Synapse notebook. You only want to do some pages of excel.

Python by itself does not read excel. So you need a library. Synapse comes with pandas library. We are interested in its read_excel. We also need DataFrame.to_csv.

Code could look something like

import pandas  
path = "abfs[s]://file_system_name@account_name.dfs.core.windows.net/file_path/"  
df = pandas.read_excel(path+"myFile.xlsm", [1,2] )  
for frame in df:  
    frame.to_csv(path+frame.name)  

If you do not want to use pandas, I did a search for a library which can do xlsm. pyexcel You will need to do installation to use this library.

Specifically, alter code example for extracting a sheet from a book.

Code could look something like

from pyexcel.cookbook import extract_a_sheet_from_a_book  

source_filepath = "//data//mybook.xlsm"  
sheet_names_to_extract = ["Sheet 2", "special sheet number 3"]  

for sheetname in sheet_names_to_extract:  
    extract_a_sheet_from_a_book(source_filepath, sheetname, "out_"+sheetname+".csv")  

Code not tested.

Please do let me if you have any queries.

Thanks
Martin

---

• 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