Hi,
I am trying to convert json data of Skype chats into an Excel sheet.
I used a python script to convert json data into an Excel file.
import pandas as pd
df = pd.read_json(‘file_data.json’)
df.to_excel(‘output2.xlsx’, index=False)
The cells in one of the columns in the created Excel sheet contain a lot of dictionaries.
How can this sheet be changed so that the data is displayed in diffent columns with headers?
Possibly, this is a two stage process. Python has native support for JSON; you can use the JSON module, which you’ve not mentioned and as such I’m unsure if you’re aware of it.
Then use pandas or maybe openpyxl to handle the Excel side of things.
Clearly, you’ll need some Python code to glue to two together, but I can’t see that being a huge deal.
So i guess i have done the first stage by converting the data into an Excel sheet. Now i need to change the data so that the data is displayed in diffent columns with headers. Maybe someone has some code that can do this?
Any help is much appreciated!
You say that…
… which (to me) means that you’ve not processed the JSON file to such a degree that it’s fit for a spreadsheet.
If I were doing this, I’d maybe use a csv file as a intermediate:
read the JSON → process to a comma separated data array → write the csv file → open the file with Excel
There could be some functionality that comes with pandas which would take care of some of that, but as I’m not a pandas user, I’d write native Python code.
Do you have a sample JSON file that you could post up? Nothing too large, just enough for a POC.
Thank you for the info! How can i upload a file in my post?
You could post a link to Dropbox, or Google drive, or use an anon file share, or simply post a small representation, like this:
{
"employee": {
"name": "John",
"age": 30,
"city": "New York"
}
}
… in the same way as you’d post any code block.
I took a part of the whole file and i cut it off at a random position in the text…
{“userId”:“8:smy1811”,“exportDate”:“2022-10-12T12:56”,“conversations”:[{“id”:“ALL”,“displayName”:null,“version”:1551786727701.0,“properties”:{“conversationblocked”:false,“lastimreceivedtime”:null,“consumptionhorizon”:null,“conversationstatus”:null},“threadProperties”:null,“MessageList”:},{“id”:“19:2892f8666686467fc54eb436637ef@thrd.skype”,“displayName”:“Actiomp;2WayTrag&Alit”,“version”:1665571520800.0,“properties”:{“conversationblocked”:false,“lastimreceivedtime”:“2022-10-12T10:45:20.685Z”,“consumptionhorizon”:“1665497955749;1665497989513;17717906478532424568”,“conversationstatus”:null},“threadProperties”:{“membercount”:4,“members”:“["8:live:jm.bnos","8:sam811","8:live:.cid.a3879d8c79e38818","82ay"]”,“topic”:“Actionmp;2WayTrg&Allt”,“picture”:null,“description”:null,“guidelines”:null,“consumptionhorizons”:{“version”:“1665571169561”,“consumptionhorizons”:[{“id”:“8:aay”,“consumptionhorizon”:“1665497955749;1665497955786;17717906478532424568”},{“id”:“8:live:jm.bes”,“consumptionhorizon”:“1663750001983;1663751033425;9543659273932686399”},{“id”:“8:live:.cid.a3879e38818”,“consumptionhorizon”:“1665571520800;1665571520945;5897054919840405720”},{“id”:“8:sam1”,“consumptionhorizon”:“1665497955749;1665497989513;17717906478532424568”}]}},“MessageList”:[{“id”:“166520800”,“displayName”:“Emia ków ACON S.A.”,“originalarrivaltime”:“2022-10-12T10:45:20.685Z”,“messagetype”:“RichText”,“version”:1665571520800.0,“content”:“we will have 6 not 5 of To\n”,“conversationid”:“19:2892686467e9c54eb436637ef@thread.ske”,“from”:“8:live:.cid.a3879e38818”,“properties”:null,“amsreferences”:null},{“id”:“166502409”,“displayName”:“Emi ówTION S.A.”,“originalarrivaltime”:“2022-10-12T10:45:02.409Z”,“messagetype”:“RichText”,“version”:1665571502409.0,“content”:“”,“conversationid”:“19:2666686467e99fc54eb436637ef@thrkype”,“from”:“8:live:.cid.a3879d8c79e38818”,“properties”:{“isserveregenerated”:“True”},“amsreferences”:null},{“id”:“166445872”,“displayName”:“Emia Ilw ACTN S.A.”,“originalarrivaltime”:“2022-10-12T10:44:05.767Z”,“messagetype”:“RichText”,“version”:1665571502408.0,“content”:“”,“conversationid”:“19:28f8666686467e99fc54e36637ef@thre.skype”,“from”:“8:live:.cid.a38799e38818”,“properties”:{“deletetime”:“1665571502408”},“amsreferences”:null},{“id”:“166571476359”,“displayName”:“Emia Ilw ACTN S.A.”,“originalarrivaltime”:“2022-10-12T10:44:36.247Z”,“messagetype”:“RichText”,“version”:1665571476359.0,“content”:“Do You want more?”,“conversationid”:“19:2892f8666686467e99fc54eb4337ef@thread.ske”,“from”:“8:live:.cid.a3879c79e38818”,“properties”:null,“amsreferences”:null},{“id”:“16655433824”,“displayName”:“Elia ków AION S.A.”,“originalarrivaltime”:“2022-10-12T10:43:53.693Z”,“messagetype”:“RichText”,“version”:1665571433824.0,“content”:"hello Gentelmen <ss type="smile">:) “,“conversationid”:“19:2892f8666686467e994eb436637ef@thread.skype”,“from”:“8:live:.cid.a3879d838818”,“properties”:nulontent”:“I will prepare my PO, okay ?”,“conversationid”:“19:2892f8666686467ec54eb436637ef@thread.skype”,“from”:“8:aay”,“properties”:null,“amsreferences”:null},{“id”:“1665497349”,“displayName”:“2ay g BV, rud Vsaar.”,“originalarrivaltime”:“2022-10-11T14:09:30.662Z”,“messagetype”:“RichText”,“version”:1665497370849.0,“content”:"yes sure ",“conversationid”:“19:2892f8666467e99fc54eb436637ef@thread.skpe”,“from”:“8:2way”,“properties”:null,“amsreferences”:null},{“id”:“16654973738”,“displayName”:“Emia Iw TION S.A.”,“originalarrivaltime”:“2022-10-11T14:09:13.631Z”,“messagetype”:“RichText”,“version”:1665497353738.0,“content”:“17 850 eu”,"convers
You’ll maybe want to start with a simpler session, so that you can get a handle on a complete JSON file output from said session.
I’m not too sure where I can take this for you, but maybe someone else will have some ideas. It could be that you’re not in fact looking for a “JSON parser”, but more a “Skype parser”.
I tried it with a simpler Json code and i managed to make a python scipt that divides the data among the rows. If i use this script for my json data i get error messages.