1

I would like to convert an JSON Array into a CSV file. This is because I want to generate a dataset that I can test using JMeter on my API whether my API is strong enough to handle many requests at the same time.

As soon as I normally import the array into the CSV, the array comes up with a single quotes so that the API does not take it as a valid JSON. When I use json.dumps(array) then the array will be placed in the CSV file as shown on the right, which is also not correct "[""726102""]".

I would like my array: ['726102'] in this way -> ["726102"] in the CSV file so that I can read it with JMeter

array = ["726102"]
with open('result.csv', 'w', newline='', encoding="utf-8") as file:
    fieldnames = ["array"]
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()

    writer.writerow({'array': json.dumps(array)})

The result is now:

array
"[""726102""]"

But what I expected it to be:

array
["726102"]
martineau
  • 119,623
  • 25
  • 170
  • 301
Simon
  • 474
  • 2
  • 4
  • 20

3 Answers3

2

You can do it by explicitly setting a couple of the dialects and formatting parameters of the csv.DictWriter instance when it's created so it will handle the output from json.dumps().

import csv
import json


array = ["726102"]
with open('result.csv', 'w', newline='', encoding="utf-8") as file:
    fieldnames = ["array"]
    writer = csv.DictWriter(file, fieldnames=fieldnames,
                                  quoting=csv.QUOTE_NONE, quotechar='\\')  # ADDED
    writer.writeheader()
    writer.writerow({'array': json.dumps(array)})

# Display results.
with open('result.csv', 'r', encoding="utf-8") as file:
    for line in file:
        print(line.rstrip())

Contents of results.csv file afterwards:

array
["726102"]
martineau
  • 119,623
  • 25
  • 170
  • 301
1

json.dumps(array) gives a string '["726102"]'.

When this is put into your CSV, it's enclosed in quotes because the entire thing is one field, so you get this string: "["726102"]"

Additionally, because your string contains quotes, they need to be escaped. So every quote is replaced by two, and you end up getting "[""726102""]"

Are you sure you need to use json.dumps to stringify what looks like a simple list, and write it to a "csv" that seems to contain only one value per line?

Pranav Hosangadi
  • 23,755
  • 7
  • 44
  • 70
  • 1
    After a bit of playing around, I have similar questions. The OP seems to be trying to write an array to a cell in a CSV, which is not how CSV files are intended to be used. I think what OP really wants is for each row of the CSV file to contain the elements of each array in the source JSON. Now it's up to the OP to name the fields for those elements (if a header is really necessary, which is also unclear). – bobsbeenjamin Sep 03 '20 at 17:40
-1

This is How you can convert JSON data to CSV

import json 
import csv 
  
  
# Opening JSON file and loading the data 
# into the variable data 
with open('data.json') as json_file: 
    data = json.load(json_file) 
  
employee_data = data['emp_details'] 
  
# now we will open a file for writing 
data_file = open('data_file.csv', 'w') 
  
# create the csv writer object 
csv_writer = csv.writer(data_file) 
  
# Counter variable used for writing  
# headers to the CSV file 
count = 0
  
for emp in employee_data: 
    if count == 0: 
  
        # Writing headers of CSV file 
        header = emp.keys() 
        csv_writer.writerow(header) 
        count += 1
  
    # Writing data of CSV file 
    csv_writer.writerow(emp.values()) 
  
data_file.close()

Thanks

Revisto
  • 1,211
  • 7
  • 11