Vlookup or equivalent on table cell with complex array from MySQL Database extract

[mrdeebee]

Hello,

I have extracted a table from a MySQL database with 300 entries in the following columns;

title

intro_text

extra_fields

<tbody>
</tbody>

The "extra_fields" column contains an array of data in the following format:

[{"id":"6","value":"Dr Daniel Farmer"},{"id":"9","value":"00:12:01"},{"id":"2","value":"BI4Cneck"},{"id":"3","value":""},{"id":"5","value":"2"},{"id":"7","value":"23"},{"id":"8","value":"1"},{"id":"11","value":"01\/08\/14"},{"id":"12","value":"3"},{"id":"13","value":"1"},{"id":"14","value":""},{"id":"15","value":["","http:\/\/","new"]}]

Not all entries in the table contain every extra_field ID. Some may only contain:

[{"id":"6","value":"Dr Jane Chalmers"},{"id":"9","value":"00:12:07"},{"id":"2","value":"1oxQVEiw"},{"id":"3","value":""},{"id":"5","value":"2"},{"id":"7","value":"23"},{"id":"8","value":"6"},{"id":"11","value":"03\/06\/10"},{"id":"12","value":"1"},{"id":"13","value":"2"}]

Each ID value corresponds to an extra_field as follows;

ID	Name
2	Media Id
3	Playlist Id
5	CPD Category
6	Presenter
7	Content Category
8	Activity Type
9	Duration
11	Date Published
12	Player
13	Aspect Ratio
14	Comments
15	Links to Further References
16	Thumbnail

<tbody>
</tbody>

I am attempting to create a new table which has the following columns only;

Title

Intro Text

Presenter (ID=6)

Content Category (ID=7)

Activity Type (ID=8)

Duration (ID=9)

Date Published (ID=11)

<tbody>
</tbody>

In the past, I have simply cleaned up the extra_fields column by removing characters such as "{:] etc and using text to columns to create individual columns for each extra field but this is laborious and I know there is an easier way but I don't have the necessary Excel skills to work it out.

If anybody could suggest a smart way to do this I would greatly appreciate it. If you need further information please let me know.

Thanks
mrdeebee

 

[azumi]

explain with example file excel

 

[mrdeebee]

Hi Azumi, here is a sample of my spreadsheet;


Excel 2016 (Mac) 64 bit

	A​	B​	C​	D​
1​	title	catid	introtext	extra_fields
2​	Dental Photography	40​	There should be a camera in every dental practice that will consistently provide you with excellent clinical views! This session will discuss the pros and cons of equipment selection in dental photography with a review of equipment priced from $500 to $ 2,500. Retractors and mirrors are as important as the right camera system and should provide you with the best images at the highest comfort level for the patient. Since cameras change constantly, find out which systems are recommended in 2012 and which camera will work best for your dental practice.	[{"id":"6","value":"Rita Bauer"},{"id":"9","value":"00:25:57"},{"id":"2","value":"XeFIDdl1"},{"id":"3","value":""},{"id":"5","value":"1"},{"id":"7","value":"12"},{"id":"8","value":"6"},{"id":"11","value":"02\/10\/2012"},{"id":"12","value":"1"},{"id":"13","value":"1"},{"id":"14","value":""},{"id":"15","value":["","http:\/\/","new"]}]
3​	Surgical Crown Lengthening to Enhance Anterior Aesthetics	32​	Dr Kevin Todes performs a surgical procedure that demonstrates clinical crown lengthening of the teeth in the anterior maxilla. The patient presents with altered passive eruption where only partial crown exposure was noted. The objective was to carry out both soft and hard tissue re-contouring to allow space for the biologic width and also to achieve a stable uniform symmetrical gingival architecture.	[{"id":"6","value":"Dr Kevin Todes"},{"id":"9","value":"00:31:01"},{"id":"2","value":"NZGLa6Zf"},{"id":"3","value":""},{"id":"5","value":"1"},{"id":"7","value":"1"},{"id":"8","value":"6"},{"id":"11","value":"01\/08\/2012"},{"id":"12","value":"1"},{"id":"13","value":"2"}]
4​	Worn Dentition Composite Restoration	33​	This presentation describes techniques which can be utilised for the construction of silicone keys and templates which may be used as an aid in the restorative management of patients with anterior tooth wear. The stages in laboratory construction and subsequent clinical application are discussed, and a number of clinical cases are also presented to demonstrate the clinical application of the technique as a viable option for treating patients with a worn and broken down dentition.	[{"id":"6","value":"Professor Ian Meyers"},{"id":"9","value":"00:36:30"},{"id":"2","value":"4eCrEYfv"},{"id":"3","value":""},{"id":"5","value":"1"},{"id":"7","value":"8"},{"id":"8","value":"6"},{"id":"11","value":"01\/07\/2012"},{"id":"12","value":"1"},{"id":"13","value":"2"}]
5​	Longer Term Interim Restorations	36​	There are often occasions when our patients require more than routine simple restorative care whilst at the same time not be able to undergo these more complex indirect procedures either owing to financial constraints or other preliminary treatment being required. This video demonstrates a methodology that will provide a reliable longer term interim solution to these situations. You will see how extensive build ups can be performed utilising direct placed restoratives in a relatively simple fashion requiring only basic knowledge and skill.	[{"id":"6","value":"Dr Phillip Zoldan"},{"id":"9","value":"00:15:54"},{"id":"2","value":"6drXzKjE"},{"id":"3","value":""},{"id":"5","value":"1"},{"id":"7","value":"12"},{"id":"8","value":"6"},{"id":"11","value":"26\/09\/10"},{"id":"12","value":"1"},{"id":"13","value":"2"}]
6​	Making a Good Impression	33​	In this video Dr Hanlin guides us through taking an impression for a partial veneer preparation using a PVS material on a molar tooth.	[{"id":"6","value":"Dr Suzanne Hanlin"},{"id":"9","value":"00:05:35"},{"id":"2","value":"5TsKefGd"},{"id":"3","value":""},{"id":"5","value":"1"},{"id":"7","value":"4"},{"id":"8","value":"6"},{"id":"11","value":"27\/07\/10"},{"id":"12","value":"1"},{"id":"13","value":"2"},{"id":"14","value":""},{"id":"15","value":["","http:\/\/","new"]}]

<tbody>
</tbody>

Sheet: Media Items

<tbody>
</tbody>

Excel 2016 (Mac) 64 bit

	A​	B​
1​	id	name
2​	32​	General Articles
3​	33​	Videos
4​	35​	Media Library
5​	36​	Series
6​	39​	Audio
7​	40​	Lectures

<tbody>
</tbody>

Sheet: Media Categories

<tbody>
</tbody>

Excel 2016 (Mac) 64 bit

	A​	B​
1​	id	name
2​	2​	Media Id
3​	3​	Playlist Id
4​	5​	CPD Category
5​	6​	Presenter
6​	7​	Content Category
7​	8​	Activity Type
8​	9​	Duration
9​	11​	Date Published
10​	12​	Player
11​	13​	Aspect Ratio
12​	14​	Comments
13​	15​	Links to Further References
14​	16​	Thumbnail

<tbody>
</tbody>

Sheet: Media Extra Fields

<tbody>
</tbody>

Excel 2016 (Mac) 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Title​	Category​	Intro text​	Presenter​	Content Category​	Activity Type​	Duration​	Date Published​
2​	Dental Photography	Lectures	There should be a camera in every dental practice that will consistently provide you with excellent clinical views! This session will discuss the pros and cons of equipment selection in dental photography with a review of equipment priced from $500 to $ 2,500. Retractors and mirrors are as important as the right camera system and should provide you with the best images at the highest comfort level for the patient. Since cameras change constantly, find out which systems are recommended in 2012 and which camera will work best for your dental practice.

<tbody>
</tbody>

Sheet: Output Worksheet

<tbody>
</tbody>

Output Worksheet has the following formulas;

Excel 2016 (Mac) 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Title​	Category​	Intro text​	Presenter​	Content Category​	Activity Type​	Duration​	Date Published​
2​	=Table_ExternalData_1[@title]	=VLOOKUP(Table_ExternalData_1[@catid],Table_ExternalData_13[[#All],[id]:[name]],2)	=Table_ExternalData_1[@introtext]

<tbody>
</tbody>

Sheet: Output Worksheet

<tbody>
</tbody>

Does this help?

 

[mrdeebee]

Hi azumi, I have posted screenshots using The Forum Tools add-in for Mac. Thanks for your help.

 

[azumi]

Hope this works, just follow this link
https://simpan.ugm.ac.id/s/KcwZPAxe4Wwcq4J

cheers
Azumi

 

[mrdeebee]

Wo! That's brilliant, thank you so much. Those formulas are crazy but obviously work. I'll give it a shot today and let you know how I go. Thanks again, much appreciated.