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]

Glad it worked for you!! Have a good day