How to convert Excel file data saved in BLOB to CSV file for each sheet with synapse

Question

How to convert Excel file data saved in BLOB to CSV file for each sheet with synapse

NishimuraChinatsu-9854 756

How to convert Excel file data saved in BLOB to CSV file for each sheet with synapse
Plase let me know how to do it.

Is it created by the notebook activity of the synapse pipeline?
Can macro-enabled excel files be converted to csv with synapse?

Ideally, I want to save to blob (xlsm)-> csv conversion-> data lake storage GEN2

I used to work with local Python, but I would like to move to Azure synapse. I'm studying Azure, but it's a little difficult.

よろしくお願いいたします。

MartinJaffer-MSFT 26,236 Reputation points

2022-06-23T19:24:13.883+00:00
I have converted my comments to answer. If this solved your issue, please mark as accepted answer. Otherwise let me know how I may better assist you, @NishimuraChinatsu-9854 .

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

Accepted answer

0 additional answers

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-06-23T19:24:13.883+00:00

I have converted my comments to answer. If this solved your issue, please mark as accepted answer. Otherwise let me know how I may better assist you, @NishimuraChinatsu-9854 .

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

Answer 1

Hello @NishimuraChinatsu-9854 and welcome to Microsoft Q&A.

I see the ask is mainly about how to convert excel (xlsx) in blob storage to csv in Data Lake Gen 2, and some of the related specifics.

Yes, Synapse can change excel to csv.

Is it created by the notebook activity of the synapse pipeline?

Generally, no, but you could do it with notebook activity, it would be more work. There is a Databricks Notebook activity and Synapse Notebook activity. Both are used to run spark, which involves writing code. Probably not what you are looking for. The notebooks can run Pyspark, which is like running python on top of spark.

You would be better off using Copy Activity, with source dataset Azure Blob > Excel format, and sink dataset Azure Data Lake Gen 2 > Delimited Text format.

Can macro-enabled excel files be converted to csv with synapse?

This is a harder question. To be absolutely sure, I will have to make a macro enabled file and try it out. The documentation does not mention macros. I expect the "hard written" data will be transferred fine, but anything calculated at run time by macros will be ignored.

MartinJaffer-MSFT 26,236 Reputation points

2022-06-22T19:54:55.893+00:00

Sheets in Excel often contain different types of data. There is no guarantee they have the same format / schema. Because of this, our excel connectors do only one sheet at a time. You will need to find out how many sheets, and loop through them, doing a separate copy activity for each sheet.

Share via

How to convert Excel file data saved in BLOB to CSV file for each sheet with synapse

0 additional answers

Your answer