How to convert CSV file to multiline JSON

Question

Here s my code  really simple stuff     import csv import json  csvfile   open  file csv    r   jsonfile   open  file json    w    fieldnames     FirstName   LastName   IDNumber   Message   reader   csv DictReader  csvfile  fieldnames  out   json dumps    row for row in reader     jsonfile write out    Declare some field names  the reader uses CSV to read the file  and the filed names to dump the file to a JSON format  Here s the problem     Each record in the CSV file is on a different row  I want the JSON output to be the same way  The problem is it dumps it all on one giant  long line    I ve tried using something like for line in csvfile  and then running my code below that with reader   csv DictReader  line  fieldnames  which loops through each line  but it does the entire file on one line  then loops through the entire file on another line    continues until it runs out of lines   Any suggestions for correcting this   Edit  To clarify  currently I have   every record on line 1      FirstName   John   LastName   Doe   IDNumber   123   Message   None     FirstName   George   LastName   Washington   IDNumber   001   Message   Something      What I m looking for   2 records on 2 lines     FirstName   John   LastName   Doe   IDNumber   123   Message   None     FirstName   George   LastName   Washington   IDNumber   001   Message   Something     Not each individual field indented on a separate line  but each record on it s own line   Some sample input    John   Doe   001   Message1   George   Washington   002   Message2

User · Answer

How about using Pandas to read the csv file into a DataFrame (pd.read_csv), then manipulating the columns if you want (dropping them or updating values) and finally converting the DataFrame back to JSON (pd.DataFrame.to_json).

Note: I haven't checked how efficient this will be but this is definitely one of the easiest ways to manipulate and convert a large csv to json.

User · Answer

I took  SingleNegationElimination s response and simplified it into a three-liner that can be used in a pipeline   import csv import json import sys  for row in csv DictReader sys stdin       json dump row  sys stdout      sys stdout write   n

User · Answer

Add the indent parameter to json dumps   data     this     has    some    things              in     it    with    some    more     print json dumps data  indent 4     Also note that  you can simply use json dump with the open jsonfile   json dump data  jsonfile

User · Answer

As slight improvement to  MONTYHS answer  iterating through a tup of fieldnames   import csv import json  csvfilename    filename csv  jsonfilename   csvfilename split      0      json  csvfile   open csvfilename   r   jsonfile   open jsonfilename   w   reader   csv DictReader csvfile   fieldnames     FirstName    LastName    IDNumber    Message    output       for each in reader    row        for field in fieldnames      row field    each field  output append row   json dump output  jsonfile  indent 2  sort keys True

User · Answer

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

User · Answer

I see this is old but I needed the code from SingleNegationElimination however I had issue with the data containing non utf-8 characters  These appeared in fields I was not overly concerned with so I chose to ignore them  However that took some effort  I am new to python so with some trial and error I got it to work   The code is a copy of SingleNegationElimination with the extra handling of utf-8  I tried to do it with https   docs python org 2 7 library csv html but in the end gave up  The below code worked   import csv  json  csvfile   open  file csv    r   jsonfile   open  file json    w    fieldnames     Scope   Comment   OOS Code   In RMF   Code   Status   Name   Sub Code   CAT   LOB   Description   Owner   Manager   Platform Owner   reader   csv DictReader csvfile   fieldnames   code      for row in reader      try          print       row  Code            for key in row              row key    row key  decode  utf-8    ignore   encode  utf-8                 json dump row  jsonfile          jsonfile write   n       except          print  -    row  Code            raise

User · Answer

import csv import json  file    csv file name csv  json file    output file name json    Read CSV File def read CSV file  json file       csv rows          with open file  as csvfile          reader   csv DictReader csvfile          field   reader fieldnames         for row in reader              csv rows extend   field i  row field i   for i in range len field              convert write json csv rows  json file    Convert csv data into json def convert write json data  json file       with open json file   quot w quot   as f          f write json dumps data  sort keys False  indent 4  separators                for pretty         f write json dumps data     read CSV file json file   Documentation of json dumps

User · Answer

Use pandas and the json library  import pandas as pd import json filepath    quot inputfile csv quot  output path    quot outputfile json quot   df   pd read csv filepath     Create a multiline json json list   json loads df to json orient    quot records quot     with open output path   w   as f      for item in json list          f write  quot  s n quot    item

User · Answer

def read        noOfElem   200    no of data you want to import     csv file name    quot hashtag donaldtrump csv quot     csv file name     json file name    quot hashtag donaldtrump json quot     json file name      with open csv file name  mode  r   as csv file          csv reader   csv DictReader csv file          with open json file name   w   as json file              i   0             json file write  quot   quot                            for row in csv reader                  i   i   1                 if i    noOfElem                      json file write  quot   quot                       return                  json file write json dumps row                    if i    noOfElem - 1                      json file write  quot   quot     Change the above three parameter  everything will be done

User · Answer

You can try this  import csvmapper    how does the object look mapper   csvmapper DictMapper                 name     FirstName            name     LastName             name     IDNumber    type   int             name     Messages               parser instance parser   csvmapper CSVParser  sample csv   mapper    conversion service converter   csvmapper JSONConverter parser   print converter doConvert pretty True    Edit   Simpler approach  import csvmapper  fields     FirstName    LastName    IDNumber    Messages   parser   CSVParser  sample csv   csvmapper FieldMapper fields    converter   csvmapper JSONConverter parser   print converter doConvert pretty True

User · Answer

import csv import json csvfile   csv DictReader  filename csv    r    output     for each in csvfile      row         row  FirstName     each  FirstName       row  LastName      each  LastName       row  IDNumber      each   IDNumber       row  Message       each  Message       output append row  json dump output open  filename json   w   indent 4 sort keys False

User · Answer

You can use Pandas DataFrame to achieve this  with the following Example   import pandas as pd csv file   pd DataFrame pd read csv  path to file csv   sep        header   0  index col   False   csv file to json   path to new file json   orient    records   date format    epoch   double precision   10  force ascii   True  date unit    ms   default handler   None

[python] How to convert CSV file to multiline JSON?

Examples related to python

Examples related to json

Examples related to csv