Converting JSON response to CSV after creating JSON from bash script

Question

I have a bash script that uses a curl command to retrieve some JSON information on a website.

I am trying to pick some specific information from these JSON results and populate them into a CSV file. Below is as far as I have gotten.

First here is the bash script I use to pull the JSON data from the website. (I have removed session ID and changed info for security reasons)

#!/bin/bash
#this script takes in the id of any customer and gives back the customer details in json format

mdid=("abcdefg" "hijklmno")
result=$(
for i in "${mdid[@]}"
do
#curl command to connect to the session and use $i as the variable for the mdid above.
curl "https://www.test.com/api/customer/$i" -H "Cookie: xxx";  -H "X-Requested-With: XMLHttpRequest" -w "\n" -H "Connection: keep-alive" --compressed
done;
)
#printing to test.txt in same folder as bash script
echo "$result" > test.json

This Results in the following entries into test.json, two lines on separate lines

{"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"Studio","ERROR_LIST":[],"MERCHANT_CODE":"218331","VALID":true,"_mId":"T712484","_status":"INPROCESS","email":"test7@gmail.com","name":"Studio","valid":true},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[{"accountMode":"Live","label":"Virtual Terminal","publishableKey":"niunibiubniunijknkjknj","source":"VIRTUAL_TERMINAL"},{"accountMode":"Live","label":"Default","publishableKey":"iiuhiuhiu","source":"ECOMMERCE"}],"appLogoUrl":null,"applicationId":"541d75e0-7db8b343a31f","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":null,"email":"test1@gmail.com","id":"ddddeff","invitationCode":null,"locale":"en_IE","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"54 My St, 1","businessAddress2":null,"businessCity":"Abbey","businessCountry":"IRL","businessPhone":null,"businessState":"DUBLIN","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"UAB \"Studio\"","maxTransactionAmount":null,"mccCode":"5712","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"tlana","ownerLastName":"nava","phone":"37647","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"9104d65i08d071","routingNumber":null,"singleTransactionAmount":null,"statementName":"UAB \"Studio\"","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":"http://www.design.lt"},"merchantId":"12484","merchantPromotionCode":null,"mposEnabled":true,"name":"Studio","netonfiguration":null,"onboardedDate":1505513232485,"onboardingMethod":null,"onboardingStatus":"INPROCESS","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[{"key":"MERCHANT_DETAILS","value":"{\"zip\":\"Wicklow\",\"phone\":\"342647\",\"email\":\"suppoor@outlook.com\",\"address\":\"Bck 6\",\"state\":\"Ireland\",\"addressLine2\":\"Unit 8, Bl Par\",\"city\":\"Wicklow\"}"},{"key":"VAT_NUMBER","value":"/evzaqen/"}],"timezone":"Europe/Dublin","tinStatus":null}
{"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"test","ERROR_LIST":[],"MERCHANT_CODE":"594920","MID_ASSIGNED":true,"VALID":true,"_mId":"103558","_status":"APPROVED","acquiringMid":"1036598","descriptor":"test 8885551212","email":"test@gmail.com","gatewayMid":"SIMP337","id":"SIMP337","level4Mid":"76576576","name":"test","status":"APPROVED","transactionCurrency":"USD;EUR;GBP","valid":true,"paymentGatewayKey":"ytfytfytfyt"},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[],"appLogoUrl":null,"applicationId":"949bdde5-07-d8d58f4c3d01","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":"test85551212","email":"test@gmail.com","id":"9f3a7d7","invitationCode":null,"locale":"en_US","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"123 test","businessAddress2":null,"businessCity":"Atlanta","businessCountry":"IRL","businessPhone":null,"businessState":"CARLOW","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"stest","maxTransactionAmount":null,"mccCode":"521","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"moto","ownerLastName":"test","phone":"3141212","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"2920","routingNumber":null,"singleTransactionAmount":null,"statementName":"test","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":null},"merchantId":"1036558","merchantPromotionCode":null,"mposEnabled":true,"name":"test","netonfiguration":null,"onboardedDate":1456846054925,"onboardingMethod":null,"onboardingStatus":"CLOSED","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[],"timezone":"Europe/Dublin","tinStatus":"InCompliance"}

I then use a python script I found on a different question (Converting JSON to .csv) that deals with a similar issue to this and used it like the following

import csv
import json

json_data = open("test.json")
data = json.load(json_data)

f = csv.writer(open("results.csv","wb+"))

for i in data:
    ma = data[i]["merchantApplication"]
    array = ma["email"]
    for j in array:
        f.writerow(j)

json_data.close()

I then get the following error when I try to run this python script in terminal:

Traceback (most recent call last): File "merch_dets.py", line 5, in data = json.load(json_data) File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/init.py", line 290, in load **kw) File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/init.py", line 338, in loads return _default_decoder.decode(s) File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py", line 369, in decode raise ValueError(errmsg("Extra data", s, end, len(s))) ValueError: Extra data: line 2 column 1 - line 95 column 1 (char 2887 - 250855)

I know there is something wrong with the way I am presenting the json lines/file as when I try one line in an online formatter it works fine, but both lines do not. I have very little experience working with JSON and cURL.

Answer 1

In addition to the changes to the JSON format mentioned by Bret Weinraub, you also need to change your code iterating the JSON element. Right now you are using the new array as if it was a dictionary.

Also, you are storing the string value of the field "email" (always only contains a string in your given example) in a variable called array and iterate through this variable to output the email address. This will output all characters of the email address in different lines.

Change your JSON format as suggested by Bret Weinraub and change your iteration like this (I took the freedom of renaming your variables to be less confusing:

import csv
import json

json_data = open("test.json")
data = json.load(json_data)

f = csv.writer(open("results.csv","wb+"))
for entry in data:
    if "merchantApplication" in entry:
        ma = entry["merchantApplication"]
        if "email" in ma:
            f.writerow([ma["email"]])

json_data.close()

Please note that writerow is also taking an array as an argument, each entry in the array corresponds to the different columns of the resulting CSV file. See the Python csv documentation for details. Since your example code seemed to only create one column, I inserted ma["email"] directly into an array that I handed to writerow here.

entry is your dictionary (of which you have one per line in your new JSON list).

I included checks in my code example that will skip JSON entries if they would be malformed / not contain the data you want. Also, this solution is robust enough to handle line breaks just fine, if they are not breaking the JSON format - your JSON dictionaries do not have to be in one line each.

Update: Unicode

As you mentioned in the comments, you are having Unicode encoding errors. This is due to your input being Unicode encoded and your output being Ascii code. A problem that is always a little bit annoying. The Python CSV package does not support Unicode well, but there is a drop-in replacement called unicodecsv, which should solve your problem with very little effort. You simply have to exchange the import to unicodecsv. Example code is below:

import unicodecsv as csv
import json

json_data = open("test.json")
data = json.load(json_data)

f = csv.writer(open("results.csv","wb+"))
for entry in data:
    if "merchantApplication" in entry:
        ma = entry["merchantApplication"]
        if "email" in ma:
            f.writerow([ma["email"]])

json_data.close()

Alternatively, you could encode all your data manually - I usually try to avoid that and purely work with Unicode strings.

Answer 2

IMHO, the simplest way is to first process the file by line and the convert each line (seen as a string) to a dictionary:

...
json_data = open("test.json")
for line in json_data:
    data = json.loads(line)
    ma = data["merchantApplication"]
    array = ma["email"]
    f.writerow(array)
...

Beware, the above code will create a csv file containing one single field...

Answer 3

Your json file is not valid JSON, as you have multiple JSON root elements.

You can use an online validator to check. For example: https://jsonformatter.curiousconcept.com/

Once you are writing valid json to the file, try again.

For instance, to make this valid:

Instead of:

{"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"Studio","ERROR_LIST":[],"MERCHANT_CODE":"218331","VALID":true,"_mId":"T712484","_status":"INPROCESS","email":"test7@gmail.com","name":"Studio","valid":true},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[{"accountMode":"Live","label":"Virtual Terminal","publishableKey":"niunibiubniunijknkjknj","source":"VIRTUAL_TERMINAL"},{"accountMode":"Live","label":"Default","publishableKey":"iiuhiuhiu","source":"ECOMMERCE"}],"appLogoUrl":null,"applicationId":"541d75e0-7db8b343a31f","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":null,"email":"test1@gmail.com","id":"ddddeff","invitationCode":null,"locale":"en_IE","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"54 My St, 1","businessAddress2":null,"businessCity":"Abbey","businessCountry":"IRL","businessPhone":null,"businessState":"DUBLIN","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"UAB \"Studio\"","maxTransactionAmount":null,"mccCode":"5712","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"tlana","ownerLastName":"nava","phone":"37647","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"9104d65i08d071","routingNumber":null,"singleTransactionAmount":null,"statementName":"UAB \"Studio\"","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":"http://www.design.lt"},"merchantId":"12484","merchantPromotionCode":null,"mposEnabled":true,"name":"Studio","netonfiguration":null,"onboardedDate":1505513232485,"onboardingMethod":null,"onboardingStatus":"INPROCESS","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[{"key":"MERCHANT_DETAILS","value":"{\"zip\":\"Wicklow\",\"phone\":\"342647\",\"email\":\"suppoor@outlook.com\",\"address\":\"Bck 6\",\"state\":\"Ireland\",\"addressLine2\":\"Unit 8, Bl Par\",\"city\":\"Wicklow\"}"},{"key":"VAT_NUMBER","value":"/evzaqen/"}],"timezone":"Europe/Dublin","tinStatus":null}
{"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"test","ERROR_LIST":[],"MERCHANT_CODE":"594920","MID_ASSIGNED":true,"VALID":true,"_mId":"103558","_status":"APPROVED","acquiringMid":"1036598","descriptor":"test 8885551212","email":"test@gmail.com","gatewayMid":"SIMP337","id":"SIMP337","level4Mid":"76576576","name":"test","status":"APPROVED","transactionCurrency":"USD;EUR;GBP","valid":true,"paymentGatewayKey":"ytfytfytfyt"},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[],"appLogoUrl":null,"applicationId":"949bdde5-07-d8d58f4c3d01","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":"test85551212","email":"test@gmail.com","id":"9f3a7d7","invitationCode":null,"locale":"en_US","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"123 test","businessAddress2":null,"businessCity":"Atlanta","businessCountry":"IRL","businessPhone":null,"businessState":"CARLOW","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"stest","maxTransactionAmount":null,"mccCode":"521","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"moto","ownerLastName":"test","phone":"3141212","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"2920","routingNumber":null,"singleTransactionAmount":null,"statementName":"test","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":null},"merchantId":"1036558","merchantPromotionCode":null,"mposEnabled":true,"name":"test","netonfiguration":null,"onboardedDate":1456846054925,"onboardingMethod":null,"onboardingStatus":"CLOSED","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[],"timezone":"Europe/Dublin","tinStatus":"InCompliance"}

Use:

[
 {"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"Studio","ERROR_LIST":[],"MERCHANT_CODE":"218331","VALID":true,"_mId":"T712484","_status":"INPROCESS","email":"test7@gmail.com","name":"Studio","valid":true},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[{"accountMode":"Live","label":"Virtual Terminal","publishableKey":"niunibiubniunijknkjknj","source":"VIRTUAL_TERMINAL"},{"accountMode":"Live","label":"Default","publishableKey":"iiuhiuhiu","source":"ECOMMERCE"}],"appLogoUrl":null,"applicationId":"541d75e0-7db8b343a31f","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":null,"email":"test1@gmail.com","id":"ddddeff","invitationCode":null,"locale":"en_IE","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"54 My St, 1","businessAddress2":null,"businessCity":"Abbey","businessCountry":"IRL","businessPhone":null,"businessState":"DUBLIN","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"UAB \"Studio\"","maxTransactionAmount":null,"mccCode":"5712","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"tlana","ownerLastName":"nava","phone":"37647","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"9104d65i08d071","routingNumber":null,"singleTransactionAmount":null,"statementName":"UAB \"Studio\"","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":"http://www.design.lt"},"merchantId":"12484","merchantPromotionCode":null,"mposEnabled":true,"name":"Studio","netonfiguration":null,"onboardedDate":1505513232485,"onboardingMethod":null,"onboardingStatus":"INPROCESS","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[{"key":"MERCHANT_DETAILS","value":"{\"zip\":\"Wicklow\",\"phone\":\"342647\",\"email\":\"suppoor@outlook.com\",\"address\":\"Bck 6\",\"state\":\"Ireland\",\"addressLine2\":\"Unit 8, Bl Par\",\"city\":\"Wicklow\"}"},{"key":"VAT_NUMBER","value":"/evzaqen/"}],"timezone":"Europe/Dublin","tinStatus":null},
{"accountMode":"Live","acquirer":"TEST","acquirerConstraints":{"cardTypes":["MASTERCARD","MAESTRO","VISA"],"cvcRegexp":"^[0-9]{3}$","cvcRequired":true,"maxAmount":500000,"minAmount":50},"acquirerDetails":{"TEST":"test","ERROR_LIST":[],"MERCHANT_CODE":"594920","MID_ASSIGNED":true,"VALID":true,"_mId":"103558","_status":"APPROVED","acquiringMid":"1036598","descriptor":"test 8885551212","email":"test@gmail.com","gatewayMid":"SIMP337","id":"SIMP337","level4Mid":"76576576","name":"test","status":"APPROVED","transactionCurrency":"USD;EUR;GBP","valid":true,"paymentGatewayKey":"ytfytfytfyt"},"acquirerValidations":null,"allowedCurrencies":["EUR","USD","GBP"],"apiKeyPairs":[],"appLogoUrl":null,"applicationId":"949bdde5-07-d8d58f4c3d01","authorizationCode":"","closedDate":null,"closureReason":null,"declineAvsAddressFailure":false,"declineAvsZipFailure":false,"declineCvcFailure":false,"defaultCurrency":"EUR","descriptor":"test85551212","email":"test@gmail.com","id":"9f3a7d7","invitationCode":null,"locale":"en_US","merchantApplication":{"accountNumber":null,"acquirer":"TEST","annualAmount":null,"annualVolume":null,"applicationType":"APPROVAL","bankName":"UNKNOWN","brand":null,"businessAddress":"123 test","businessAddress2":null,"businessCity":"Atlanta","businessCountry":"IRL","businessPhone":null,"businessState":"CARLOW","businessZip":null,"data":null,"email":"test@gmail.com","escalationPhone":null,"fax":null,"legalName":"stest","maxTransactionAmount":null,"mccCode":"521","merchantPromotionCode":null,"mobile":null,"monthlyAmount":null,"monthlyVolume":null,"ownerFirstName":"moto","ownerLastName":"test","phone":"3141212","GuideAccepted":null,"privacyAccepted":true,"privacyVersion":"1a","referenceId":"2920","routingNumber":null,"singleTransactionAmount":null,"statementName":"test","taxId":null,"termsAccepted":true,"termsVersion":"1a","url":null},"merchantId":"1036558","merchantPromotionCode":null,"mposEnabled":true,"name":"test","netonfiguration":null,"onboardedDate":1456846054925,"onboardingMethod":null,"onboardingStatus":"CLOSED","partner":null,"saqCompliant":false,"saqExpires":null,"settings":[],"timezone":"Europe/Dublin","tinStatus":"InCompliance"}
]