Hi, I want to write a data from array variable to csv file in adf pipeline. Here is my pipeline As a result of until activity i have all the data stored to an array variable Now i want to store this data to csv file using copy activity. Can anyone help me out in this. @Subashri Vasudevan Thanks

Hi @Amar Agnihotri , Point your source data set to a file in blob or data lake. The file can be any file. With one or two columns and couple of rows (this is just a dummy file) Then add an additional column in copy source and point to your variable Data. (You may have to convert the array variable to string using join) @fr (Variables ('Data'),',') This will convert the whole array to a string separated by comma and assign the string value to the additional column. Then , Under mapping tab, remove the columns from dummy source file and just keep the additional column. Point the sink to a file or folder. That's all. Please give a try and let us know. Thanks.

Hey, The below URL provides detailed steps : stackoverflow.com/questions/60673220/transfer-the-output-of-set-variable-activity-into-a-json-file-azure-data-fact But one change would be to convert the array to string

Thanks for the reply. Actually it has been achieved

Want to write a data from array variable to csv file in adf

Accepted answer

Subashri Vasudevan 11,216 Reputation points

2022-06-28T05:41:42.41+00:00

Hi @Amar Agnihotri ,

Point your source data set to a file in blob or data lake. The file can be any file. With one or two columns and couple of rows (this is just a dummy file)

Then add an additional column in copy source and point to your variable Data. (You may have to convert the array variable to string using join)

@fr (Variables ('Data'),',')

This will convert the whole array to a string separated by comma and assign the string value to the additional column.

Then , Under mapping tab, remove the columns from dummy source file and just keep the additional column.

Point the sink to a file or folder.

That's all. Please give a try and let us know.

Thanks.
Please sign in to rate this answer.

1 person found this answer helpful.
Amar Agnihotri 926 Reputation points

2022-06-28T06:08:40.35+00:00

@Subashri Vasudevan

Hi,
I tried this but i am confused in mapping part.

input is my container for source dataset and output is for sink

inside input i have created this dummy file input.txt

Now my question is do we need the same file in output folder or it will be created while execution ?
Now in the pipeline here is my source dataset referring to the same input.txt file

and here is my sink dataset referring to the output folder only

I added this additional column in source dataset with dynamic code as value

Now i go to the mapping section and as per your suggestion i have to remove the columns from dummy source file and have to kept only additional column. But no column is showing here

I am stuck here right now and also one more concern is about the backslash which are present in the data variable . Will these backslash be written in the csv or it will be avoided ?

Amar Agnihotri 926 Reputation points

2022-06-28T06:13:46.207+00:00

@Subashri Vasudevan

When i clicked on import schema i got this window opened

Subashri Vasudevan 11,216 Reputation points

2022-06-28T06:14:42.85+00:00

Hi @Amar Agnihotri ,

Did you try to import schema in mapping tab? If not please import and then remove the unwanted columns.

Thanks

Amar Agnihotri 926 Reputation points

2022-06-28T06:18:23.663+00:00

I tried this but it imported no schema

see this

Subashri Vasudevan 11,216 Reputation points

2022-06-28T06:46:16.727+00:00

Hi @Amar Agnihotri ,

In the source dataset, import the schema and then import in the pipeline.

Thanks

Amar Agnihotri 926 Reputation points

2022-06-28T06:46:32.67+00:00

@Subashri Vasudevan @Nandan Hegde

Hi ,

I am facing this error while importing schema in mapping tab

This is my variable in additional columns

and when i import schema in mapping it should return some values but see here it is not returning any values but the error

Amar Agnihotri 926 Reputation points

2022-06-28T06:56:50.157+00:00

@Subashri Vasudevan

In the source dataset only the schema from input.txt dummy file is showing

Subashri Vasudevan 11,216 Reputation points

2022-06-28T08:06:48.307+00:00

Hi @Amar Agnihotri

Please assign a dummy string to the variable('Data')

And try importing schema.

Thanks

Amar Agnihotri 926 Reputation points

2022-06-28T09:32:01.567+00:00

@Subashri Vasudevan
sorry but didn't understand this dummy string. How to do this ? and for what ?. Please clear this

Amar Agnihotri 926 Reputation points

2022-06-28T09:35:16.857+00:00

@Subashri Vasudevan

I tried to do this but getting invalid

Subashri Vasudevan 11,216 Reputation points

2022-06-28T09:55:33.97+00:00

Hi,

While importing schema, it prompts you to provide some value to the additional column , just to decide the data type of the additional column.

That's why we need to assign some dummy string like test to the additional column in the window while you click import schema.

Hope it's clear now.

Subashri Vasudevan 11,216 Reputation points

2022-06-28T10:54:02.897+00:00

Hi,

Pls enclose the test string in single quote.

['test']

Try this and let us know.

Amar Agnihotri 926 Reputation points

2022-06-28T12:13:53.527+00:00

@Subashri Vasudevan

still showing invalid

I believe that might be due to array data type but we are providing a string but here we have already changed array to string

Subashri Vasudevan 11,216 Reputation points

2022-06-28T12:18:52.547+00:00

Hi @Amar Agnihotri

Have edited my response. You may try that way and let us know pls.

Thanks

Amar Agnihotri 926 Reputation points

2022-06-28T12:21:44.987+00:00

@Subashri Vasudevan

still not valid

Subashri Vasudevan 11,216 Reputation points

2022-06-28T12:29:13.74+00:00

You can try this then. Use a set variable activity and convert array to string using join
function and then use that string variable in additional column.

Amar Agnihotri 926 Reputation points

2022-06-28T12:46:20.693+00:00

@Subashri Vasudevan

Thanks it worked

but it copied all the backslashes also and csv file is containing data like this

What can we do to get data in proper format without backslashes.

The variable is holding this data that we are copying-

Subashri Vasudevan 11,216 Reputation points

2022-06-28T12:54:10.467+00:00

Hi,

you can try to use something like the below,

If "var" has the final text, you can replace slash as

replace(Variables('var'),'\','')

This you can do post conversion to string using join function.

Amar Agnihotri 926 Reputation points

2022-06-28T14:31:52.833+00:00

@Subashri Vasudevan

Hi,
Thanks for your support. Actually i don't think there will be an issue with backslash only but after resolving backslash we shall have to deal with the space character and new line character also. That would not be easy to deal with . So, i think if we could find a way to append that array of records to array variable inside until activity then we will have a single array of records and rather copying it to csv we can copy it as json in the storage . This will prevent us dealing with such abnormal data format issues.

What is your opinion on this?

Amar Agnihotri 926 Reputation points

2022-07-07T06:55:56.04+00:00

Thanks @Subashri Vasudevan
It's done now

Subashri Vasudevan 11,216 Reputation points

2022-07-07T13:20:22.217+00:00

Hi,

How did you achieve??

Did you replace special characters?

Amar Agnihotri 926 Reputation points

2022-07-07T13:35:19.447+00:00

Hi @Subashri Vasudevan

I created an azure function in C# and used that to convert output of append activity to a valid json string

Subashri Vasudevan 11,216 Reputation points

2022-07-07T14:04:14.217+00:00

That's cool. Thanks for your response Amar
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.