Help with finding matches inside a complicated string

Astraithious

Hello everybody! I am new to this program but have been having an amazing time with it. I have finally hit a road block where my lack of programming knowledge has halted me but I figured since this community is so active I could find some help here.

The goal:   I want to be able to count how many times a string appears in a column, there may be multiple instances of it inside the same cell. I have 11 different ID that i would love to tally up from about 6400 entries.

Example Cell: [{"id":"52","type":"dp","character":"37852"}]

Example of multiple in a cell: [{"id":5,"type":"dp","character":"20560"},{"id":12,"type":"dp","character":"20560"}]

The useful information that I am seeking is the ID:XY portion but because they are in their own quotations they are throwing me off. I thought of trim but it wouldn't work as some cells are up to 2044 characters long. 

I also tried the filter and using the reg count function but I do not know enough of the programming side to allow me to sort through to get those matches.

End goal is to be able to tell how many IDs were from what time period but I can work on the time myself I'm sure.

I have included some sample data 

Thank you for any help that you can provide!

Example Data.csv

Federica_FF

HI @Astraithious

the text inside the cart field is json.

Open the csv as normal csv (increase the length in the input tool) and use a JSON Parse Tool (Developer tab) for the cart colum. You'll get all the values splitted to rows.

For example:

[{"id":5,"type":"dp","character":"20560"},{"id":12,"type":"dp","character":"20560"}]

0.id    5
0.type    dp
0.character    20560
1.id    12
1.type    dp
1.character    20560

The first column has a starting number, which is the rowID.

The string right after the number is the column name

That number is what you need to count.

In attachment a short workflow to parse json strings and convert data into a regular columnar table

New Workflow1.yxmd