{"id":3280,"date":"2024-09-26T09:49:43","date_gmt":"2024-09-26T17:49:43","guid":{"rendered":"http:\/\/pididu.com\/wordpress\/?p=3280"},"modified":"2024-09-26T10:00:21","modified_gmt":"2024-09-26T18:00:21","slug":"convert-json-database-file-to-excel","status":"publish","type":"post","link":"http:\/\/pididu.com\/wordpress\/blog\/convert-json-database-file-to-excel\/","title":{"rendered":"Convert JSON Database File to Excel"},"content":{"rendered":"\n

This python script is a better solution than my previous post<\/a>. It was generated with a prompt to Microsoft’s version of chatGPT, something like “Write a python program to translate a unicode JSON file of arbitrary size into comma-separated values.” I did a little manual fix-up on the script that was generated, and got:<\/p>\n\n\n\n

#!\/usr\/bin\/python3\nimport json\nimport csv\n\nwith open('answers.json') as f:\n    data = json.load(f)\n\ncsv_file = open('answers.csv', 'w')\n\ncsv_writer = csv.writer(csv_file)\n\ncount = 0\n\nfor item in data:\n    if count == 0:\n        header = item.keys()\n        csv_writer.writerow(header)\n        count += 1\n\n    csv_writer.writerow(item.values())\n\ncsv_file.close()\n<\/code><\/pre>\n\n\n\n
The first line in the script is just to tell Linux to use python3. If you have Windows, that isn’t necessary.<\/p>\n\n\n\n
My input file was answers.json, and output was answers.csv . You would change these to suit, or if you have a lot of files, maybe want to prompt for the names.<\/p>\n\n\n\n
The program assumes that the first record of the json is a header with column names.<\/p>\n\n\n\n
Unlike the previous conversion website that I used, multi-byte unicode characters like emojis were handled well, for instance:<\/p>\n\n\n\n
Q: \ud83d\ude02\u201e are you a rePUPlican \ud83d\udc36 or a demoCAT \ud83d\udc31?<\/p>\n\n\n\n
A: indePANDAnt \ud83d\udc3c<\/p>\n\n\n\n
The output file is comma-separated values (csv), which can easily be read by Excel, or Openoffice Calc.<\/p>\n\n\n\n
One problem was that my Yahoo Answers database originally came from the web (html), so contained strings like<\/p>\n\n\n\n
I can&#39;t believe it &lt;3<\/mark><\/code><\/p>\n\n\n\n
which should be<\/p>\n\n\n\n
I can't believe it <3<\/mark><\/code><\/p>\n\n\n\n
As the ampersand-hash sequence was just ordinary characters in the JSON, it passed right through that way, and I had to do a global search and replace on all such things. I just did a search on &<\/mark><\/code> in all cells, then when I found a pattern like can&#39;t<\/mark><\/code> did a global replace of &#39;<\/mark><\/code> with '<\/mark><\/code> (apostrophe).  Not all of the finds were of the form &# <\/mark><\/code>– there were a few like &quot;<\/mark><\/code> and &amp;<\/mark><\/code> .<\/p>\n\n\n\n
There’s probably some Excel script that does the translation of such things, but it didn’t take long to do it manually.<\/p>\n","protected":false},"excerpt":{"rendered":"
This python script is a better solution than my previous post. It was generated with a prompt to Microsoft’s version of chatGPT, something like “Write a python program to translate a unicode JSON file of arbitrary size into comma-separated values.” I did a little manual fix-up on the script that was generated, and got: The … Continue reading Convert JSON Database File to Excel<\/span> →<\/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":[166],"tags":[360,390,391,358,361,359],"_links":{"self":[{"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/posts\/3280"}],"collection":[{"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/comments?post=3280"}],"version-history":[{"count":0,"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/posts\/3280\/revisions"}],"wp:attachment":[{"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/media?parent=3280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/categories?post=3280"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/pididu.com\/wordpress\/wp-json\/wp\/v2\/tags?post=3280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}