{"id":23670,"date":"2020-05-02T23:46:49","date_gmt":"2020-05-02T22:46:49","guid":{"rendered":"https:\/\/jurnsearch.wordpress.com\/?p=23670"},"modified":"2020-05-02T23:46:49","modified_gmt":"2020-05-02T22:46:49","slug":"json-to-csv-with-windows-freeware","status":"publish","type":"post","link":"https:\/\/jurn.link\/jurnsearch\/index.php\/2020\/05\/02\/json-to-csv-with-windows-freeware\/","title":{"rendered":".JSON to .CSV with Windows freeware"},"content":{"rendered":"

Situation:<\/strong> You need to cleanly extract just the usernames from a .JSON file, and place each name on a new line. The result should look like…<\/p>\n

name1
\nname2
\nname3<\/em><\/p>\n

The value being extracted from the JSON could just as easily be email addresses, or map co-ordinates, or suchlike.<\/p>\n

Why you might do this:<\/strong> You can’t just do a simple “.JSON out, .JSON in”. For instance, let’s say you have a Web browser add-on that offers a blocklist function based on usernames. Perhaps it’s the deviantART-Filter. You want to port this 1,600 name blocklist of scum-and-villainy over to a similar browser add-on. Perhaps it’s the dA_ignore<\/a> UserScript. The old deviantART-Filter usefully exports a .JSON file of your blocked users. But…. the new dA_ignore can only import a pasted-in list of usernames, one per line. <\/p>\n

Solution 1:<\/strong> <\/p>\n

There is a working Regex for doing this, which only requires a recent copy of Notepad++ and a suitable .JSON file for the process… <\/p>\n

\"\"<\/a><\/p>\n

It’s been tested and works. The use of the * wildcard will enable the extraction of a list in the form…<\/p>\n

path_label”:”any_value_here<\/strong><\/p>\n

After this regex has processed the code you are thus left with a list that looks like…<\/p>\n

username”:”name1
\nusername”:”name2
\nusername”:”name3<\/p>\n

… and you need only to do a basic search\/replace to clear username”:”<\/strong> to obtained your cleaned list of the various unique usernames.<\/p>\n

In this use-case you go to your own DeviantArt Settings page, and paste in the new ported-over blocklist. <\/p>\n

\n

Solution 2:<\/strong> <\/p>\n

1. Open the .JSON with the genuine Windows freeware JSONedit<\/a>.<\/p>\n

2. Filter the JSON on field “username” by typing username<\/em> into the search box. In ‘List view’ you should now only see a list of the “username” fields and the adjacent data entry.<\/p>\n

3. Then go: top menu | ‘Tools’ | ‘Export as .CSV’.<\/p>\n

4. Add a file extension .CSV to the saved file if needed, and then open it with Excel. Select and copy out all the “usernames” column to a new Notepad++ file. <\/p>\n

5. In Notepad++, a quick search-replace will then remove the “” marks. You now have a clean portable list, with one username per line. <\/p>\n

\n

Solution 3:<\/strong> <\/p>\n

There are, of course, cloud services in Whereizitagain that may well offer to do this. But the above methods use Windows freeware and are thus more secure.<\/p>\n","protected":false},"excerpt":{"rendered":"

Situation: You need to cleanly extract just the usernames from a .JSON file, and place each name on a new …<\/p>\n

Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-23670","post","type-post","status-publish","format-standard","hentry","category-jurn-tips-and-tricks"],"_links":{"self":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts\/23670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/comments?post=23670"}],"version-history":[{"count":0,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/posts\/23670\/revisions"}],"wp:attachment":[{"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/media?parent=23670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/categories?post=23670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jurn.link\/jurnsearch\/index.php\/wp-json\/wp\/v2\/tags?post=23670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}