data flow - search and replace value - .json data

Question

data flow - search and replace value - .json data

arkiboys 9,706

Hello,
I am receiving .json files and I process them in dataflow1.
How is it possible, to do a search and replace inside the.json file.
For example, if I find the work novs}, I would like to replace with nod}
Thank you

Accepted answer

0 additional answers

Your answer

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @arkiboys ,

Thank you for posting query in Microsoft Q&A Platform.

As per my understanding you would like to replace data inside json file using dataflows. Kindly correct me if I am wrong.

Here your intention is randomly to replace data inside json file. So that means here we should consider taking entire json file as single column data and then use derived column transformation with replace() function in it to do our job.

To achieve this, we should consider using csv type dataset on top of our json file with no delimiters for rows and columns. Kindly check below images for better idea.

Dataset for json file with DelimitedText as formatter.

use above dataset as source transformation and then use derived transformation to replace desired values.

Hope this helps. Please let us know if any further queries. Thank you.

------------

Please consider hitting Accept Answer. Accepted answers helps community as well.

arkiboys 9,706 Reputation points

2022-03-21T14:38:08.793+00:00

Hello,
I see.

I have created the csv dataset as you suggested.
indataset preview, all json data is under column Prop_0

In data preview of dataflow source all json data is under Column_1. In other columns i.e. column_2, column_3...column_7 they are NULL
Then I added derived column so that it searched for this text and replaces it with:

"quantities":{"planned":"444","uom":"rtrt"},"locations"...

TO

"quantities":[{"planned":"52","uom":"MT"}],"locations"...

basically I added this into expression but it is not right.
replace('"quantities":{', '"quantities":[{')

It looks like the replace has not worked.
Do you see anything wrong with the replace?

in-case it makes any difference, I am running a big .json file.
In derived column, from the columns drop down, I selected Column_1
Note that there are over 1000 columns in dropdown.

Thank you
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-03-21T16:01:52.113+00:00
Hi @arkiboys ,

You should get only one column as we are trying to read entier json as single column and single row. Kindly make sure your dataset has below configurations correctly.

In dataset, mention Column delimiter as No delimiter & Row delimiter as No delimiter

Once you settings done, go to schema tab and import schema. It will get only one column.

In the replace function you should pass your column name as first parameter and then string substring to fine and then substring to replace. In your replace() function I dont see you are passing column name. Kindly check and let me know how it goes. Thank you.
arkiboys 9,706 Reputation points

2022-03-21T16:18:37.217+00:00

Hi,
Yes, dataset issue was schema related.
The sink .json is the remaining issue.
In gen2 when I see the newly created .json it looks like it has Column_1 as well as a-lot of \
This is not right. How can I have the file as a .json file without column_1 and backslashes
arkiboys 9,706 Reputation points

2022-03-21T18:03:22.667+00:00

Hi @ShaikMaheer-MSFT
I am setting this question to answered as it helped me to replace the text inside .json.
I still have issue with saving the new file in sink with the same json style as original... For this I will raise another questions
Thank you
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-03-22T11:13:08.05+00:00

Sure. Thank you.

Share via

data flow - search and replace value - .json data

0 additional answers

Your answer