How to Convert CSV File to Multiline JSON

How to convert CSV file to multiline JSON?

The problem with your desired output is that it is not valid json document,; it's a stream of json documents!

That's okay, if its what you need, but that means that for each document you want in your output, you'll have to call json.dumps.

Since the newline you want separating your documents is not contained in those documents, you're on the hook for supplying it yourself. So we just need to pull the loop out of the call to json.dump and interpose newlines for each document written.

import csv
import json

csvfile = open('file.csv', 'r')
jsonfile = open('file.json', 'w')

fieldnames = ("FirstName","LastName","IDNumber","Message")
reader = csv.DictReader( csvfile, fieldnames)
for row in reader:
json.dump(row, jsonfile)
jsonfile.write('\n')

Creating individual JSON files from a CSV file that is already in JSON format

It's not clear from your picture what your CSV actually looks like.

I mocked up a really small CSV with JSON lines that looks like this:

Request
"{""id"":""1"", ""name"":""alice""}"
"{""id"":""2"", ""name"":""bob""}"

(all the double-quotes are for escaping the quotes that are part of the JSON)

When I run this little script:

import csv

with open('input.csv', newline='') as input_file:
reader = csv.reader(input_file)
next(reader) # discard/skip the fist line ("header")

for i, row in enumerate(reader):
with open(f'json_file_path/parsedJSONfile{i}.json', 'w') as output_file:
output_file.write(row[0])

I get two files, json_file_path/parsedJSONfile0.json and json_file_path/parsedJSONfile1.json, that look like this:

{"id":"1", "name":"Alice"}

and

{"id":"2", "name":"bob"}

Note that I'm not using json.dumps(...), that only makes sense if you are starting with data inside Python and want to save it as JSON. Your file just has text that is complete JSON, so basically copy-paste each line as-is to a new file.

JSON Single Line Parse to Multi-Line CSV with Python

I think you are making this over-complicated and confusing JSON with CSV. Hat tip to @thalesmallo who beat me to the punch on using the DictWriter class. Try this:

import csv
from plaid import Client

Client.config({
'url': 'https://api.plaid.com'
})

#Connect to Plaid
from plaid import Client
from plaid import errors as plaid_errors
from plaid.utils import json

client = Client(client_id='test_id', secret='test_secret')
account_type = 'suntrust'

try:
response = client.connect(account_type, {
'username': 'plaid_test',
'password': 'plaid_good'
})
except plaid_errors.PlaidError:
pass
else:
connect_data = response.json()
response = client.connect_get()
data = response.json()
transactions = data['transactions'] # see https://plaid.com/docs/api/#data-overview

#Save the transactions JSON response to a csv file in the Python Projects directory
header = ("date", "_account", "name", "amount")
with open('transactions.csv', 'w') as f:
writer = csv.DictWriter(f, fieldnames=header, extrasaction='ignore')
writer.writeheader()
for x in transactions:
writer.writerow(x)

CSV to JSON convert using python

If you print each dictionary during row in csvReader loop you'll see:

{'appname': 'backend', 'hostname': 'testserver1', 'id': '1'}
{'appname': 'frontend', 'hostname': 'testserver2', 'id': '2'}
{'appname': 'database', 'hostname': 'testserver3', 'id': '3'}

So you need to modify the loop to get desired behavior:

        # Open a csv reader called DictReader
with open(csvFilePath, encoding='utf-8') as csvf:
csvReader = csv.DictReader(csvf)

# Convert each row into a dictionary
# and add it to data
for row in csvReader:
for columnName in row:
if columnName not in data:
data[columnName] = []
data[columnName].append(row[columnName])

The output JSON file will look like:

{
"appname": [
"backend",
"frontend",
"database"
],
"hostname": [
"testserver1",
"testserver2",
"testserver2"
],
"id": [
"1",
"2",
"3"
]
}

Convert CSV to a nested JSON while formatting values for specific keys to numeric/int/float

Use str.isdigit to check the string and then use int.

Ex:

from csv import DictReader
from itertools import groupby
from pprint import pprint
import json

with open(filename) as csvfile:
r = DictReader(csvfile, skipinitialspace=True)
data = [dict(d) for d in r]

groups = []
uniquekeys = []

for k, g in groupby(data, lambda r: (r['CLID'], r['District'])):
groups.append({
"CLID": k[0],
"District": k[1],
"attributes": [{k:int(v) if v.isdigit() else v for k, v in d.items() if k not in ['CLID','District']} for d in list(g)] #Update
})
uniquekeys.append(k)

print(json.dumps(groups, indent = 4) + '\n}')


Related Topics



Leave a reply



Submit