CSV to Json - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: CSV to Json (/thread-28186.html) |
CSV to Json - starfish - Jul-08-2020 As a python beginner, I like to take a CSV file and load and then output json I have tried a few things - pandas, csvmapper but I'm struggling to see the best way to to read this and create the sample json from the CSV. I think it is a Dictionary with 2 lists embedded but obvious how to approach this Quote:A,B,source,status,RecordType,xA,xB I would like to create this [{ "RecIds": [{ "type": "A", "value": "q1" }], "source": "Direct", "status": "New", "recordType": "Quote", "xIds": [{ "type": "B", "value": "x200" }] }, { "RecIds": [{ "type": "A", "value": "x1" }, { "type": "B", "value": "y2" }], "source": "Direct", "status": "New", "recordType": "Quote", "xIds": [{ "type": "A", "value": "x200" }, { "type": "B", "value": "y200" }] }]Best approach that I have found is so thing like this https://medium.com/coinmonks/parsing-a-spreadsheet-into-a-json-file-using-python-6118f5c70bd3 import csv, json, xlrd def csvParse(csvfile): # Open the CSV f = open(csvfile, 'r') # Change each fieldname to the appropriate field name. reader = csv.DictReader(f, fieldnames=( "Protocol", "Source Port", "Destination Port", "Source IP", "Destination IP", "Source MAC", "Destination MAC", "segment ID", "Frame name", "Frame ID", "segment name", "packet ID", "Receivers")) framenames = [] store = [] # Store frame names in a list for row in reader: frame = {"FrameName": row["Frame name"], "FrameID": row["Frame ID"], "protocol": row["Protocol"], "segments": []} if row["Frame name"] not in framenames: framenames.append(row["Frame name"]) store.append(frame) # Create Objects for Frames, segments and packets segment = {"segmentName": ""} for frame in store: f = open(csvfile, 'r') reader = csv.DictReader(f, fieldnames=( "Protocol", "Source Port", "Destination Port", "Source IP", "Destination IP", "Source MAC", "Destination MAC", "segment ID", "Frame name", "Frame ID", "segment name", "packet ID", "Receivers")) for row in reader: if frame["FrameName"] == row["Frame name"]: if segment["segmentName"] != row["segment name"]: segment = { "segmentID": row["segment ID"], "segmentName": row["segment name"], "srcPort": row["Source Port"], "destPort": row["Destination Port"], "packets": [{ "packetID": row["packet ID"], "Receivers": row["Receivers"], "destIP": row["Destination IP"], "destMAC": row["Destination MAC"], "srcIP": row["Source IP"], "srcMAC": row["Source MAC"] }] } frame["segments"].append(segment) else: packet = { "packetID": row["packet ID"], "Receivers": row["Receivers"], "destIP": row["Destination IP"], "destMAC": row["Destination MAC"], "srcIP": row["Source IP"], "srcMAC": row["Source MAC"] } segment["packets"].append(packet) # Parse the CSV into JSON out = json.dumps(store, indent=4) # Save the JSON f = open('data.json', 'w') f.write(out)But as I said not sure if this is the right or best approach RE: CSV to Json - Larz60+ - Jul-08-2020 please attach csv file RE: CSV to Json - starfish - Jul-08-2020 (Jul-08-2020, 08:51 PM)Larz60+ Wrote: please attach csv file My CSV would look like A,B,source,status,RecordType,xA,xB q1,,Direct,New, Quote,x200,, x1,y2,Direct,New, Quote,x200,y300 RE: CSV to Json - starfish - Jul-10-2020 I wondered if anyone had any pointers on how to create this json from the simple CSV RE: CSV to Json - Larz60+ - Jul-11-2020 I think this is close if not there: import os import csv import json def csv_to_json(): alldata = [] ddict = {} # Excpect input file in same directory as this script, so set cwd os.chdir(os.path.abspath(os.path.dirname(__file__))) with open('csvfile.csv') as fp, open('jsonfile.json', 'w') as fo: crdr = csv.DictReader(fp) for row in crdr: rnode = ddict = {} dnode = rnode['RecIds'] = {} dnode['type'] = 'A' dnode['value'] = row['A'] rnode['source'] = row['source'] rnode['status'] = row['status'] rnode['recordType'] = row['RecordType'] xnode = rnode['xIds'] = {} xnode['type'] = 'B' xnode['value'] = row['B'] alldata.append(ddict) json.dump(alldata, fo) def read_it_back_as_dict(): with open('jsonfile.json') as fp: mydict = json.load(fp) print(mydict) if __name__ == '__main__': csv_to_json() read_it_back_as_dict()results: I think this is a way overly complicated json format and makes for extra work in deciphering.It's always good to kkeep it simple RE: CSV to Json - starfish - Jul-11-2020 (Jul-11-2020, 03:17 AM)Larz60+ Wrote: I think this is a way overly complicated json format and makes for extra work in deciphering. Thank you for looking at this - I have really struggled to create this json - unfortunately it is part of an existing api that I want to interact with. unfortunately does not quite generate expected format "RecIds": [{ "type": "A", "value": "x1" }, { "type": "B", "value": "y2" }]RecIds can be one or more identifiers as per sample Modding your example I can the basic structure but I encounter 3 problems:
import os import csv import json def csv_to_json(): alldata = [] ddict = {} # Excpect input file in same directory as this script, so set cwd os.chdir(os.path.abspath(os.path.dirname(__file__))) with open('sample.csv') as fp, open('jsonfile.json', 'w') as fo: crdr = csv.DictReader(fp) for row in crdr: rnode = ddict = {} dnode = rnode['RecIds'] = {} dnode['type'] = 'A' dnode['value'] = row['A'] dnode['type2'] = 'B' dnode['value2'] = row['B'] rnode['source'] = row['source'] rnode['status'] = row['status'] rnode['recordType'] = row['RecordType'] xnode = rnode['xIds'] = {} xnode['type'] = 'xA' xnode['value'] = row['xA'] xnode['type2'] = 'xB' xnode['value2'] = row['xB'] alldata.append(ddict) out=json.dumps(alldata, indent=4) f = open('jsonfile.json', 'w') f.write(out) def read_it_back_as_dict(): with open('jsonfile.json') as fp: mydict = json.load(fp) print(mydict) if __name__ == '__main__': csv_to_json() read_it_back_as_dict() RE: CSV to Json - starfish - Jul-18-2020 Not sure how to fix the problems dnode = rnode['RecIds'] = {"RecIds":[]} segment = { "type": "A", "value": row["A"] } dnode['RecIds'].append(segment) |