extract text

[mike4]

Hi Everyone,

I am new to the community and hope you can help.

I have data that looks like this, each associated with an ID number and a variable number of items.

“[{"Activity":"Conferencing","Calendar":"09-19-2023","Start Time":"11:45 AM","End Time":"01:30 PM","Teacher Look For":"","Student Look For":"","Coach Look For":"Worked on LT/SC for Unit 3 from the standards.","Cycle For Results":"Step #1 (Collect and Analyze Multiple Data Sources)\nStep #2 (Determine Essential Standards Learning Targets and Success Criteria)\nStep #3 (Collborate on Designing Common Assessment and Instruction)"},{"Activity":"Modeling Lesson","Calendar":"09-14-2023","Start Time":"09:10 AM","End Time":"03:00 PM","Teacher Look For":"Redirecting students\nActively walking around room\nEffective Questioning\nNo pencil/pen, Student Look For: Engaging in conversation\nOn task\nUsing tech appropriately","Student Look For":"","Coach Look For":"","Cycle For Results":"Step #4 (Deliver Tier 1 Standards-Based and Standards-Aligned Instruction)"},{"Activity":"Understanding Content Standards","Calendar":"09-19-2023","Start Time":"11:40 AM","End Time":"01:15 PM","Teacher Look For":"We went through the LT/SC previously. Used these and the standards, fundamentals from DOE to create CFA/CUA. Trained teacher on SchoolNet usage, navigation, etc. CFA was completed.","Student Look For":"","Coach Look For":"","Cycle For Results":"Step #2 (Determine Essential Standards Learning Targets and Success Criteria)\nStep #3 (Collborate on Designing Common Assessment and Instruction)"},{"Activity":"Instructional Planning","Calendar":"09-26-2023","Start Time":"11:15 AM","End Time":"12:15 PM","Teacher Look For":"Work on lesson plans for Week 10 & 11. Be very routine in outline. Have students working/learning bell to bell. Pull away from lecturing (teacher centered to student centered)","Student Look For":"","Coach Look For":"Outlined a broken-down lesson plan routine to be intentional about what the students are doing and to work it in bell to bell.","Cycle For Results":"Step #3 (Collborate on Designing Common Assessment and Instruction)"}]”

How can I parse this to have all the Activities in a column, dates in a column, etc associated with the given example’s ID number?

I’d really appreciate any help you all can provide!

Thanks.

[Daniel_Weikert]

Hi @mike4
this is json data
So you first make sure the column type is json then you use the json path node and extract what you need

and finally you ungroup the data

br

[mlauber71]

@mike4 welcome to the KNIME forum. This does look like some JSON or XML file. You will have to import this in a proper format and then define Paths (XPath) to extract the data.

Maybe you can provide a full example that would keep the quotation marks and characters in their original setting.

[mike4]

Thank you, both. I did try to work with it some, but perhaps I am making this more difficult then it is.

Here is an example in the original that I am working with.

I am still having trouble…

{
“cfname”: “Configurable List”,
“name”: “typeA45”,
“order”: “36”,
“selectedField”: “533946093c1ad0c45d000070”,
“static”: “No”,
“text”: “Learning Activities”,
“type”: “control_widget”,
“answer”: “[{"Activity":"Conferencing","Calendar":"09-19-2023","Start Time":"11:45 AM","End Time":"01:30 PM","Teacher Look For":"","Student Look For":"","Coach Look For":"Worked on LT/SC for Unit 3 from the standards.","Cycle For Results":"Step #1 (Collect and Analyze Multiple Data Sources)\nStep #2 (Determine Essential Standards Learning Targets and Success Criteria)\nStep #3 (Collborate on Designing Common Assessment and Instruction)"},{"Activity":"Modeling Lesson","Calendar":"09-14-2023","Start Time":"09:10 AM","End Time":"03:00 PM","Teacher Look For":"Redirecting students\nActively walking around room\nEffective Questioning\nNo pencil/pen, Student Look For: Engaging in conversation\nOn task\nUsing tech appropriately","Student Look For":"","Coach Look For":"","Cycle For Results":"Step #4 (Deliver Tier 1 Standards-Based and Standards-Aligned Instruction)"},{"Activity":"Understanding Content Standards","Calendar":"09-19-2023","Start Time":"11:40 AM","End Time":"01:15 PM","Teacher Look For":"We went through the LT/SC previously. Used these and the standards, fundamentals from DOE to create CFA/CUA. Trained teacher on SchoolNet usage, navigation, etc. CFA was completed.","Student Look For":"","Coach Look For":"","Cycle For Results":"Step #2 (Determine Essential Standards Learning Targets and Success Criteria)\nStep #3 (Collborate on Designing Common Assessment and Instruction)"},{"Activity":"Instructional Planning","Calendar":"09-26-2023","Start Time":"11:15 AM","End Time":"12:15 PM","Teacher Look For":"Work on lesson plans for Week 10 & 11. Be very routine in outline. Have students working/learning bell to bell. Pull away from lecturing (teacher centered to student centered)","Student Look For":"","Coach Look For":"Outlined a broken-down lesson plan routine to be intentional about what the students are doing and to work it in bell to bell.","Cycle For Results":"Step #3 (Collborate on Designing Common Assessment and Instruction)"}]”
}

Daniel - when I tried your solution I get empty lists.

Any help you can provide is greatly appreciated!

[bruno29a]

Hi @mike4

@mike4

Daniel - when I tried your solution I get empty lists.

Well, that’s probably because what @Daniel_Weikert worked with, was the data that you provided originally. That data that you provided did not give the real picture, and actually he had to guess that it was json.

The new data that you provided gives a clear and definite picture (and why did you not give this data from the beginning?), but is different. Now, you need for first reference “answer”, and then apply what @Daniel_Weikert provided on the result.

[mike4]

Thank you, Bruno. To answer your question, I had spent hours making this more difficult then it had to be and I had left it in that form when I made the original post.

I appreciate both of you for helping!

Michael