I want to copy excel file having multiple sheetsfrom blob storage and save it in single csv file

Question

i am having excel file in which there are multiple sheets and this file is getting placed everyday i want to copy the data from sheet 1 by skipping rows and in sheet 2 there in only value present but no column name, i want to add column name to that value in sheet 2 and save this data in single csv file in blob storage again, but thing is if data goes today and on next day the yesterdays data should be there in csv file it should be overwrite

Answer 1

Hi,

Thanks for reaching out to Microsoft Q&A.

Yes, its possible, if you can follow the below links, these are good implementations with simple codes. But as you might know, there are customizations needed to suit your needs.

http://surl.li/hzjmb

http://surl.li/hzjms

Please Upvote and Accept as answer if the reply was helpful, this will be benefitting the other community members who go through the same issue.

Answer 2

Hi Ashish Kumbhar ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

From the description of your question, it seems you are trying to iterate through multiple sheets within the excel and copy each sheet as a csv file. Now there are two other challenges that you have mentioned, one is to add the column header which is missing in the file and other is to append the data on previous day file. Please let me know if the understanding about your query has some gap.

For iterating over the excel sheet and dynamically getting the data into csv , you can use parameters on the sheet name in your source dataset and use until block to loop through the file and copy the data using copy activity within until. For more details on the implementation part, kindly refer this video: Iterate over excel file sheets dynamically and copy them as a CSV file using Azure Data Factory

Coming to adding the column header, you can do it by using the copied csv file as the source and enable 'first row as header' option for the sink dataset and in the mapping change the column name as per your desire.

Regarding the data append, it's not possible to append data in csv . You can go for tabular sink in that case.

Hope it helps. Kindly accept the answer if it was helpful. Thankyou.