Extracting values from a serialized array without delimiters

[andrew_milonavic]

Hi,

I have a form on my website that I use in an nontraditional manner. Users return to update and save the form but it is never submitted.

The problem is the form saves the data in a serialized array without delimiters inside a single MySQL table column. See table image: https://imgur.com/a/tvnDN4v

So far I have not be able to query the values directly and I have tried exporting the table using all combinations of line separators, enclosed string and field separators to make the data usable.

Is there an excel formula (Preferably not a VBA, but if that's the only option than so be it.) that can extract the values into columns? If there is a SQL expert among the Excel users I'm open to a SQL query as well. Bottom line is I just need the values separated in columns, I can add column headers after the fact as they are not identifiable in the array. I can add an extra form field too that adds the users_id so associating the data with a specific users is a non issue as well.

Thoughts?

Here is the value string with dummy data:

[{"value":"Test-Data-1","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":11,"isUpdated":true},{"value":"Test-Data-2","clean":false,"visible":true,"invalid":false,"id":12,"isUpdated":true},{"value":"Test-Data-3","clean":false,"visible":true,"invalid":false,"id":24,"isUpdated":true},{"value":"Test-Data-4","clean":false,"visible":true,"invalid":false,"id":25,"isUpdated":true},{"value":"Test-Data-5","clean":false,"visible":true,"invalid":false,"id":13,"isUpdated":true},{"value":"Test-Data-6","clean":false,"visible":true,"invalid":false,"id":17,"isUpdated":true},{"value":"Test-Data-7","clean":false,"visible":true,"invalid":false,"id":18,"isUpdated":true},{"value":"Test-Data-8","clean":false,"visible":true,"invalid":false,"id":58,"isUpdated":true},{"value":"Test-Data-9","clean":false,"visible":true,"invalid":false,"id":59,"isUpdated":true},{"value":"Test-Data-10","clean":false,"visible":true,"invalid":false,"id":60,"isUpdated":true},{"value":"Test-Data-11","clean":false,"visible":true,"invalid":false,"id":61,"isUpdated":true},{"value":"Test-Data-12","clean":false,"visible":true,"invalid":false,"id":62,"isUpdated":true},{"value":"Test-Data-13","clean":false,"visible":true,"invalid":false,"id":63,"isUpdated":true},{"value":"Test-Data-14","clean":false,"visible":true,"invalid":false,"id":64,"isUpdated":true},{"value":"Test-Data-15","clean":false,"visible":true,"invalid":false,"id":19,"isUpdated":true},{"value":"Test-Data-16","clean":false,"visible":true,"invalid":false,"cellOrder":1,"id":20,"isUpdated":true},{"value":"Test-Data-17","clean":false,"visible":true,"invalid":false,"cellOrder":1,"id":23,"isUpdated":true},{"value":"Test-Data-18","clean":false,"visible":true,"invalid":false,"id":26,"isUpdated":true},{"value":"Test-Data-19","clean":false,"visible":true,"invalid":false,"cellOrder":3,"id":27,"isUpdated":true},{"value":"Test-Data-20","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":28,"isUpdated":true},{"value":"Test-Data-21","clean":false,"visible":true,"invalid":false,"id":30,"isUpdated":true},{"value":"Test-Data-22","clean":false,"visible":true,"invalid":false,"cellOrder":3,"id":31,"isUpdated":true},{"value":"Test-Data-23","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":29,"isUpdated":true},{"value":"Test-Data-24","clean":false,"visible":true,"invalid":false,"id":33,"isUpdated":true},{"value":"Test-Data-25","clean":false,"visible":true,"invalid":false,"cellOrder":3,"id":34,"isUpdated":true},{"value":"Test-Data-26","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":32,"isUpdated":true},{"value":"Test-Data-27","clean":false,"visible":true,"invalid":false,"cellOrder":1,"id":36,"isUpdated":true},{"value":"Test-Data-28","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":35,"isUpdated":true},{"value":"Test-Data-29","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":38,"isUpdated":true},{"value":"Test-Data-30","clean":false,"visible":true,"invalid":false,"id":37,"isUpdated":true},{"value":"Test-Data-31","clean":false,"visible":true,"invalid":false,"cellOrder":3,"id":40,"isUpdated":true},{"value":"Test-Data-32","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":39,"isUpdated":true},{"value":"Test-Data-33","clean":false,"visible":true,"invalid":false,"id":43,"isUpdated":true},{"value":"Test-Data-34","clean":false,"visible":true,"invalid":false,"cellOrder":1,"id":41,"isUpdated":true},{"value":"Test-Data-35","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":42,"isUpdated":true},{"value":"Test-Data-36","clean":false,"visible":true,"invalid":false,"id":44,"isUpdated":true},{"value":"Test-Data-37","clean":false,"visible":true,"invalid":false,"id":45,"isUpdated":true},{"value":"Test-Data-38","clean":false,"visible":true,"invalid":false,"id":46,"isUpdated":true},{"value":"Test-Data-39","clean":false,"visible":true,"invalid":false,"id":47,"isUpdated":true},{"value":"Test-Data-40","clean":false,"visible":true,"invalid":false,"id":48,"isUpdated":true},{"value":"Test-Data-41","clean":false,"visible":true,"invalid":false,"id":49,"isUpdated":true},{"value":"Test-Data-42","clean":false,"visible":true,"invalid":false,"id":50,"isUpdated":true},{"value":"Test-Data-43","clean":false,"visible":true,"invalid":false,"id":51,"isUpdated":true},{"value":"Test-Data-44","clean":false,"visible":true,"invalid":false,"id":52,"isUpdated":true},{"value":"Test-Data-45","clean":false,"visible":true,"invalid":false,"id":53,"isUpdated":true},{"value":"Test-Data-46","clean":false,"visible":true,"invalid":false,"id":54,"isUpdated":true},{"value":"Test-Data-47","clean":false,"visible":true,"invalid":false,"id":95,"isUpdated":true},{"value":"Test-Data-48","clean":false,"visible":true,"invalid":false,"default":"","textarea_rte":"","disable_rte_mobile":"","textarea_media":"","id":159,"isUpdated":true},{"value":"Test-Data-49","clean":false,"visible":true,"invalid":false,"default":"","textarea_rte":"","disable_rte_mobile":"","textarea_media":"","id":155,"isUpdated":true},{"value":"Test-Data-50","clean":false,"visible":true,"invalid":false,"default":"","custom_name_attribute":"","personally_identifiable":"","cellOrder":2,"id":71,"isUpdated":true},{"value":["Test-Data-51","Test-Data-52","Test-Data-53"],"clean":false,"visible":true,"invalid":false,"id":70,"selected":[],"isUpdated":true},{"value":"Test-Data-54","clean":false,"visible":true,"invalid":false,"default":"","custom_name_attribute":"","personally_identifiable":"","cellOrder":2,"id":74,"isUpdated":true},{"value":["Test-Data-55","Test-Data-56"],"clean":false,"visible":true,"invalid":false,"id":72,"selected":[],"isUpdated":true},{"value":"Test-Data-57","clean":false,"visible":true,"invalid":false,"default":"","custom_name_attribute":"","personally_identifiable":"","cellOrder":2,"id":78,"isUpdated":true},{"value":[" Test-Data-58"],"clean":false,"visible":true,"invalid":false,"id":76,"selected":[],"isUpdated":true}]

Thanks

Andrew

 

[tyija1995]

Hey,

Do you want each set of braces { } set in to a column? So if the string you sent is in A1, then A2 might be:

{"value":"Test-Data-1","clean":false,"visible":true,"invalid":false,"cellOrder":2,"id":11,"isUpdated":true}

etc? Then A3 will be the 2nd { } brace - then we can work on breaking it down further.

 

[andrew_milonavic]

Hey Tyija1995,

Thanks for the fast reply!

I'd just like the values, i.e Test-Data-1 in A1, Test-Data-2 in A2 etc etc, but if it needs several step to get there and helper columns that's fine.

I'm not sure if that answers your question?

Thanks

Andrew

 

[tyija1995]

I have used a few helper columns here for this.

With the original string in cell A1 try these formulae:

A2:

SEARCH("|¬",SUBSTITUTE($A$1,CHAR(123),"|¬",ROW()-1))

Drag that down to A56.

B2:

MID($A$1,A2,A3-A2-1)

Drag that down to B55.

Set B56 as:

=MID($A$1,A56,999)

Now in C2:

C2:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(B2,CHAR(58),REPT(" ",LEN(B2))),(2-1)*LEN(B2)+1,LEN(B2))),CHAR(44),REPT(" ",LEN(B2))),LEN(B2)),CHAR(34),""),CHAR(91),""),CHAR(93),"")

Drag that down to C56.

C2-C56 should then output "Test-Data-#"

Let me know if it works.

 

[andrew_milonavic]

Hey Tyija1995,

WOW, that works. It extracts the values!!!

Will it work the same if I scale horizontally? i.e So all the original string will be in A1 and the helper formulas and results in B1, B2, B3 etc etc.. Possible?

I really appreciate the help!!!

Thanks

Andrew

 

[tyija1995]

Hey Tyija1995,

WOW, that works. It extracts the values!!!

Will it work the same if I scale horizontally? i.e So all the original string will be in A1 and the helper formulas and results in B1, B2, B3 etc etc.. Possible?

I really appreciate the help!!!

Thanks

Andrew

Glad it worked!! I believe it is possible, but as I spanned 55 rows (2:56) then 55 columns will need to be spanned (B:BD) - is this ideal? I would think vertically would be easier. But I can take a look at setting it up horizontally if you want.

 

[andrew_milonavic]

Hey Tyija1995,

If possible horizontal would be best. I don't mind it spanning B:BD, or further if needed. I'm basically going to make this a calculator to extract the data and then move the return values to another excel file where I'll have the forms headers etc etc.

Thanks!!!!

Andrew

 

[tyija1995]

Hey Tyija1995,

If possible horizontal would be best. I don't mind it spanning B:BD, or further if needed. I'm basically going to make this a calculator to extract the data and then move the return values to another excel file where I'll have the forms headers etc etc.

Thanks!!!!

Andrew

Alright, I have used columns B:BD to do this (original string remains in cell A1 and is the only entrant in column A)

B1:

SEARCH("|¬",SUBSTITUTE($A$1,CHAR(123),"|¬",COLUMN()-1))

Drag across to BD1.

B2:

MID($A$1,B1,C1-B1-1)

Drag across to BC2.

BD2:

MID($A$1,BD1,999)

Finally, B3:

B3:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(B2,CHAR(58),REPT(" ",LEN(B2))),(2-1)*LEN(B2)+1,LEN(B2))),CHAR(44),REPT(" ",LEN(B2))),LEN(B2)),CHAR(34),""),CHAR(91),""),CHAR(93),"")

Drag that across to BD3.

Hope that solves it!

 

[andrew_milonavic]

Hey Tyija1995,

I'm really sorry, I meant all on one row. I got my brain jumbled when I said B1, B2, B3.. I meant B1, C1, D1 etc etc.. I understand it will scale further across. Is this possible?? I'm really sorry to waste your time.

Thanks

Andrew

 

[andrew_milonavic]

Hey Tyija1995,

I used your previous multi row formula and then just cut and paste to put it on a single line. Problem solved!!!

I really appreciate your help. It's perfect!!!

Thanks again

Andrew