将CSV转换为JSON解决方案

问题描述：

我刚刚为我的后端服务器开发了一个使用解析的iOS应用程序。我有完整的应用程序，并准备好去，并有大量的条目添加到解析后端，但是我已经提交了数据，直到现在还没有意识到要加载包括我需要使用json的地理点的类。将CSV转换为JSON解决方案

Country,PostCode,State,Suburb,location/__type,location/latitude,location/longitude,locname,phone,streetaddress,website 
Australia,2000,NSW,Cronulla,GeoPoint,-33.935434,151.026887,Shop ABC,+61297901401,ABC Canterbury Road,http://www.123.com.au

我需要这个格式转换为以下

{ "results": [ 
    { 
     "Country": "Australia", 
     "PostCode": "2000", 
     "State": "NSW", 
     "Suburb": “Crounlla”, 
     "location": { 
      "__type": "GeoPoint", 
      "latitude": -33.935434, 
      "longitude": 151.026887 
     }, 
     "locname": "Shop ABC”, 
     "phone": "+123456”, 
     "streetaddress": “ABC Canterbury Road", 
     "website": "http://www.123.com.au" 
    } 
] }

我有几千个条目，这样你可以想像我不想要做的：我的数据文件的结构如下它手动。我只能访问Mac，因此任何建议都需要Mac友好。我发现以前的答案由于地理数据而无法使用。

答

您可以使用Python脚本（Mac是Python预安装）示例代码：

#!/usr/bin/python 

import csv 
import json 


header = [] 
results = [] 
with open('data.csv', 'rb') as f: 
    reader = csv.reader(f) 
    for row in reader: 
     if len(header) > 0: 
      location = {} 
      datarow = {} 
      for key, value in (zip(header,row)): 
       if key.startswith('location'): 
        location[key.split('/')[1]] = value 
       else: 
        datarow[key] = value 
      datarow['location'] = location 
      results.append(datarow) 
     else: 
      header = row 

     print json.dumps(dict(results=results))

答

也许你可以使用http://www.convertcsv.com/csv-to-json.htm -website来转换这些东西？

答

下面是使用jq的解决方案。

如果filter.jq包含以下滤波器

def parse: 
    [ 
     split("\n")[]      # split string into lines 
    | split(",")       # split data 
    | select(length>0)     # eliminate blanks 
    ] 
; 

def reformat: 
    [ 
     .[0] as $h      # headers 
    | .[1:][] as $v      # values 
    | [ [$h, $v]     
     | transpose[]      # convert array 
     | {key:.[0], value:.[1]}   # to object 
     ] | from_entries     # 
    | reduce (
      keys[]       # 
     | select(startswith("location/")) # move keys starting 
    ) as $k (       # with "location/" 
      .        # into a "location" object 
     ; setpath($k|split("/");.[$k]) # 
     | delpaths([[$k]])    # 
    ) 
    | .location.latitude |= tonumber  # convert "latitude" and 
    | .location.longitude |= tonumber  # "longitude" to numbers 
    ] 
; 

{ 
    results: (parse | reformat) 
}

和data包含样本数据，则命令

$ jq -M -Rsr -f filter.jq data

产生

{ 
    "results": [ 
    { 
     "Country": "Australia", 
     "PostCode": "2000", 
     "State": "NSW", 
     "Suburb": "Cronulla", 
     "locname": "Shop ABC", 
     "phone": "+61297901401", 
     "streetaddress": "ABC Canterbury Road", 
     "website": "http://www.123.com.au", 
     "location": { 
     "__type": "GeoPoint", 
     "latitude": -33.935434, 
     "longitude": 151.026887 
     } 
    } 
    ] 
}

将CSV转换为JSON解决方案

相关推荐