Importing json file where rows have missing columns

[#1]

Hi, 

I have downloaded my full streaming history from Spotify, which is a JSON file. When trying to import the file to JMP, using the CSV "Data with preview" import helper I have a few problems.

1. The first problem is that comma is used both as a delimiter between columns and inside some of the data strings. I solved this by opening the file in Notepad and replacing all "," with < as a unique delimiter.
2. The next thing that trips me is that not every line in the raw file has the same number of columns. For example, podcasts do not have albums, so for podcasts the "reason start" column end up in the album name column (see last line in my example below). Other lines may have certain columns missing, as this data goes back 5 years.

What I would like is for all podcast rows to be blank in "track name", "artist name" and "album name" columns while having the "episode name" and "show name" columns and vice versa all music rows to be blank for "episode name and "show name".

The JSON file has this structure (here I only show some of the columns):

{"ts":"2020-01-06 20:57:19 UTC","username":"xx","platform":"Android xxy","ms_played":"1702","master_metadata_track_name":"track1","master_metadata_album_artist_name":"artist1","master_metadata_album_album_name":"album1","reason_start":"trackdone","reason_end":"trackdone"}
{"ts":"2020-01-06 20:57:19 UTC","username":"xx","platform":"Android xxy","ms_played":"1702","master_metadata_track_name":"track2","master_metadata_album_artist_name":"artist2","master_metadata_album_album_name":"album2","reason_start":"trackdone","reason_end":"trackdone"}
{"ts":"2020-01-06 20:57:19 UTC","username":"xx","platform":"Android xxy","ms_played":"1702","episode_name":"pod1","episode_show_name":"show1","reason_start":"clickrow","reason_end":"logout"}

[#2 · Accepted solution]

JMP 15 imports your sample JSON correctly. Don't use the CSV importer, use the JSON importer. (You can use the JSON import wizard, or probably just drag-and-drop the .json file into JMP to get the default wizard choices.)

JMP 14 won't get it right unless you add square brackets at beginning and end and commas between rows to make a JSON array.