从bash脚本创建JSON后将JSON响应转换为CSV [英] Converting JSON response to CSV after creating JSON from bash script

问题描述

我有一个bash脚本，该脚本使用curl命令在网站上检索一些JSON信息。

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

我正在尝试从这些JSON结果中选择一些特定信息，将它们填充到CSV文件中。

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.

首先，这是我用来从网站提取JSON数据的bash脚本。 （出于安全原因，我已删除会话ID并更改了信息）

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

这将导致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":"[e-mail]","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":"[e-mail]","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":"[e-mail]","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\":\"[e-mail]\",\"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":"[e-mail]","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":"[e-mail]","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":"[e-mail]","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"}

我然后使用python脚本我在另一个问题上发现了（转换JSON到.csv ）处理类似的问题，并像下面这样使用

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()

当我尝试在终端中运行此python脚本时，我得到以下错误：

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

追溯（最近一次通话）：
文件 merch_dets.py，第5行，在
data = json.load（json_data）
文件 / System / Library / Frameworks / Python中。 framework / Versions / 2.7 / lib / python2.7 / json / init .py，第290行，正在加载
** kw）
文件 / System / Library / Frameworks / Python.framework / Versions / 2.7 / lib / python2.7 / json / init .py，第338行， n加载
return _default_decoder.decode
文件 /System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py，第369行，在解码
中引发ValueError（errmsg（额外数据，s，end，len（s）））
ValueError：额外数据：第2行第1列-第95行第1列（字符2887-250855）

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)

我知道呈现JSON行/文件的方式出了问题，就像我在在线格式化程序中尝试一行时一样它工作正常，但两行都不行。我几乎没有使用JSON和cURL的经验。

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.

推荐答案

除了更改之外更改为Bret Weinraub 提到的JSON格式，则还需要更改代码以迭代JSON元素。现在，您就像使用字典一样使用新数组。

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.

此外，您还存储字段 email的字符串值（在给定的示例中始终只包含一个字符串）在名为 array 的变量中，并循环访问此变量以输出电子邮件地址。这将在不同的行中输出电子邮件地址的所有字符。

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.

按照Bret Weinraub的建议更改JSON格式，并像这样更改迭代（我拥有重命名的自由）您的变量以减少混乱：

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()

请注意 writerow 还将数组作为参数，数组中的每个条目都对应于生成的CSV文件的不同列。有关详细信息，请参见Python csv文档。由于您的示例c ode似乎只创建一个列，我将 ma [ email] 直接插入到数组中，该数组交给了 writerow 在这里。

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.

条目是您的字典（在新JSON列表中每行有一个字典）。

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

我在代码示例中添加了检查，如果它们格式错误/不包含所需数据，将跳过JSON条目。另外，如果该解决方案没有破坏JSON格式，则该解决方案足够强大，足以处理换行符-您的JSON字典不必各占一行。

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.

您在注释中提到，您遇到Unicode编码错误。这是因为您的输入是Unicode编码的，而您的输出是Ascii代码。这个问题总是有点烦人。 Python CSV软件包不能很好地支持Unicode，但是有一个替代品称为unicodecsv ，这应该可以很轻松地解决您的问题。您只需要将导入交换为unicodecsv。示例代码如下：

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()

或者，您可以手动对所有数据进行编码-我通常会尝试避免这种情况，而只能使用Unicode字符串。

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

这篇关于从bash脚本创建JSON后将JSON响应转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！