I need to read rows from a csv file and assign them to an array in ADF

Question

I have a csv file which has data like following :

abc123

def345

ghi789

dhj987

I want to read this file from the Azure blob storage and assign these values to array variable in my pipline.

I can not use Foreach loop as i will have 20-22K records in a single file and loop will take alot of time to complete.

Answer 1

Hi Asati, Ankit (AS) ,

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

As per my understanding you are trying to convert multiple rows of csv file into single array and convert the records into items of the array using data factory pipelines. Please let me know if that is not the case.

As you have mentioned that you do not want to loop through the records in the pipeline which could be one of the approach to use 'Append variable activity' inside foreach activity to append each of the records into the array variable, however you want an alternative approach to achieve the requirement.

You can use dataflow transformations to achieve this requirement.

1. Add 'Derived column' transformation and create a new column , say 'ID' and give a static integer value to it (Ex: 1) so that each record will have Id=1 , to help us group by using that ID.
2. Now use 'Aggregate column' transformation and in group by tab , select 'ID' . In aggregate tab, select the column present in csv , say , 'data' and provide the expression as collect(data)
3. This will create an array containing all the records of the file
4. Add Sink transformation to move to to the destination location.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou