Hello,
I have extracted a table from a MySQL database with 300 entries in the following columns;
<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;
<tbody>
</tbody>
I am attempting to create a new table which has the following columns only;
<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
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
Last edited: