Extracting a list of values from JSON file to Excel or a text file

Question

I want to extract usernames from a JSON data file.

 [{"username": "Cobra", "user_id": 146231486, "event_type": 2,
"title": null, "class_id": 4211, "war_state" : null,
"superpower_expire_date": 1441178060.0, "role": 3, "event_state": 2,
"avatar_id": 4211, "avatar_type" : 2, "recent_gifts": []},
{"username": "Divineshadow", "user_id": 1622533959, "event_type": 2,
"title" : null, "class_id": 1887, "war_state": null,
"superpower_expire_date": null, "role": 2, "event_state" : 2,
"avatar_id": 1887, "avatar_type": 2, "recent_gifts": []}, {"username":
"-TheLastTrojan_", "user_id" : 1387569472, "event_type": 2, "title":
null, "class_id": 1887, "war_state": null, "superpower_expire_date" :
1440106625.0, "role": 1, "event_state": 3, "avatar_id": 1887,
"avatar_type": 2, "recent_gifts": [] }, {"username": "-TheLostHero-",
"user_id": 246900216, "event_type": 2, "title": null, "class_id": 1887,
"war_state": null, "superpower_expire_date": null, "role": 3,
"event_state": 2, "avatar_id": 1887,  "avatar_type": 2,
"recent_gifts": []}, {"username": "_The-Divineshadows-Minion_",
"user_id": 347494612 , "event_type": 2, "title": null, "class_id":
3382, "war_state": null, "superpower_expire_date": null , "role": 3,
"event_state": 2, "avatar_id": 3382, "avatar_type": 2, "recent_gifts": []}]

I want to pick all the usernames in the order in which they appear in the JSON data file and compile it in a column of Excel sheet or text file. The output file should look like this:

1. Cobra
2. Divineshadow
3. -TheLostHero-
4. _The-Divineshadows-Minion_

Any help with how I can manage to do achieve my desired output file?

Solution

You could do this in notepad++ with search replace (or anything that has fairly good search replace). In this case, choose the regular expression search mode:

Search: .+?username": "(.+?)".+?\}

Replace: \1\n

This puts the usernames on a line by themselves, which you could then drop into Excel to put a line number at the beginning if you wanted, or use the TexFX notepad++ plugin to add them.

Comments

[Paul]

Which field is the date for chronological order?

[WR20]

The username which appears first will be the first username extracted to the desired output file and so on. It will pick username from the start of raw data file text to bottom

[200_success]

Any reason why -TheLastTrojan_ should be excluded from the output?

[WR20]

no. i must have have missed that

[WR20]

I use Mac. Is there a mac compatible alternative for notepad++?

[DdW]

That only formats it into CSV, it does not filter/query the json for specific fields.