question

BillapatiNaveen-2631 avatar image
0 Votes"
BillapatiNaveen-2631 asked ShaikMaheer-MSFT commented

Extract Individual objects from a column in ADF

Hello Everybody,

Does anyone know how to extract the individual objects from a single column using ADF dataflows or azure functions? In below picture you can see multiple objects such as Patching, EAM_ID, Owner..... in a single line. I want their corresponding values to be in a separate column and Patching, EAM_ID, Owner as column names.

I want it to be like this

Owner EAM_ID Patching
name1 111111 gghdgd
name2 22222 gfhgfgh

Note: those are neither JSON objects nor array's

Thanks in advance
Naveen

132491-inkedcapture-li.jpg


azure-data-factory
inkedcapture-li.jpg (817.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BillapatiNaveen-2631,

Following up to see is below answer helps you? If yes, Please consider click on Accept Answer. Accepted answers helps community as well. Thank you.

0 Votes 0 ·

Hi @BillapatiNaveen-2631,

Just checking to see is below answer helps you? If yes, Please consider click on Accept Answer. Accepted answers helps community as well. Thank you.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @BillapatiNaveen-2631 ,

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

You can achieve this using Parse transformation. First you need to append { and } to your data at beginning and ending to convert it as json string using derived column transformation and then use parse transformation to parse data.

Please check below detailed example.

Step1: Source transformation

132775-source.gif

Step2: Derived Column transformation to add { and } to your data at starting and ending.
expression used: '{' + Tags + '}'

132689-derivedcol.gif

Step3: Parse Transformation to parse data
Output column type expression used: (Patching as string,EAM_ID as string,Owner as string)

132796-parsetrans.gif

Step4: Select Transformation to select only required columns

132776-select.gif

Finally you can add Sink transformation to load transformed data in to Target system.

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


source.gif (291.1 KiB)
derivedcol.gif (435.4 KiB)
parsetrans.gif (467.5 KiB)
select.gif (356.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @ShaikMaheer-MSFT,

Thank you very much for you detailed explanation. That was very helpul.

But when I try to execute it, it is throwing the following error at parse step.


Regards,
Naveen132690-derived-column.png132750-parse1.png132788-parse2.png


0 Votes 0 ·
derived-column.png (36.5 KiB)
parse1.png (41.3 KiB)
parse2.png (31.4 KiB)

Hello @ShaikMaheer-MSFT,

Thank you very much for your detailed explanation

When I tried to execute it, it is showing me the following error at Parse step.


Kind Regards,
Naveen132823-derived-column.png132824-parse1.png132831-parse2.png


0 Votes 0 ·
derived-column.png (36.5 KiB)
parse1.png (41.3 KiB)
parse2.png (31.4 KiB)
ShaikMaheer-MSFT avatar image ShaikMaheer-MSFT BillapatiNaveen-2631 ·

Hi @BillapatiNaveen-2631 ,

Thank you for follow-up ask. Below are few observations from your reply which looks little suspicious to me. Kindly review them in comparison with my answer.

Inside derived column transformation
132900-image.png

Inside Parse Transformation
132936-image.png

  • Could you please perform data preview at each step and see results are getting cooked at each level

  • Your screenshot doesn't have all the details of implementations, projects, data previews. Hence its hard to guess what is going wrong.

  • I request you to check the answer detailly to understand it better and then try to apply in your answer.

Please refer below links to know about parse transformation.
- https://docs.microsoft.com/en-us/azure/data-factory/data-flow-parse
- https://www.youtube.com/watch?v=42OAjOj3rXE

Please consider accepting answer by click on 130616-image.png button. Accepted answers helps community as well.

0 Votes 0 ·
image.png (115.4 KiB)
image.png (205.9 KiB)