Convert excel file to csv in synapse

CuteeeeRabbit 0
Viewed 56

When converting an excel file to csv in synapse pipeline or dataflow, I need to put the values of certain cells in excel in an additional column.

I was able to convert an excel file to csv, but I can't figure out how to read the values in a particular cell and add them as a column.

What I would like to achieve is as follows. Excel sample file

I want to add "outlet_code" as a column in the "C2" cell of the Excel file. csv file

azure-data-flow csv azure-synapse excel
1 Answers

you can try the below logic:

  1. 1st use a copy activity to copy the content of the Excel file from A1 to c2 into CSV (assuming the place of the code remains the same)

enter image description here

Excel Source dataset : enter image description here

  1. Leverage Lookup Activity to get the code value Lookup should map to the CSV file generated above

enter image description here

  1. Create another Copy activity to actually copy your excel content into CSV with the current setting you have and a below logic to add an additional column : Add additional column in copy activity using Azure Data Factory

enter image description here

Nandan0
Related