Parsing Key Value pairs from inconsistent and incomplete data

[excel_lente]

Hey all,

I have a CSV file with the following data in rows:

[TABLE="width: 867"]
<tbody>[TR]
[TD]{'gender': 'male', 'age': '35', 'hometown': 'Chicago', 'car': 'Civic', 'occupation': 'actor', 'salary': '80000'}[/TD]
[/TR]
[TR]
[TD]{'gender': 'male', 'age': '32', 'hometown': 'New York', 'occupation': 'banker', 'salary': '130000'}[/TD]
[/TR]
[TR]
[TD]{'gender': 'female', 'age': '33', 'hometown': 'Seattle', 'occupation': 'developer'}[/TD]
[/TR]
[TR]
[TD]{'age': '27', 'hometown': 'Detroit', 'car': 'Mustang', 'salary': '75000'}

As you can see, there are recurring attributes (i.e. Gender, Age, Hometown, Car, Occupation, Salary), but this data is complete for every record, making it difficult / impossible to simply separate via Column-To-Test techniques.

Is there a way to use Excel to parse this data into columns and rows?

I'd like to use the attributes as column headers, so that when a "value" is missing, it would simply be a blank cell:

[TABLE="width: 498"]
<tbody>[TR]
[TD]gender[/TD]
[TD]age[/TD]
[TD]hometown[/TD]
[TD]car[/TD]
[TD]occupation[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD]Male[/TD]
[TD]35[/TD]
[TD]Chicago[/TD]
[TD]Civic[/TD]
[TD]Actor[/TD]
[TD]80000[/TD]
[/TR]
[TR]
[TD]Male[/TD]
[TD]32[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD]Banker[/TD]
[TD]130000[/TD]
[/TR]
[TR]
[TD]Female[/TD]
[TD]33[/TD]
[TD]Seattle[/TD]
[TD][/TD]
[TD]Developer[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance![/TD]
[/TR]
</tbody>[/TABLE]

excel_lente

Also posted
https://www.excelforum.com/excel-ge...rs-from-inconsistent-and-incomplete-data.html