CODEWITHSUNDEEP
jsoncsvparse-platform
Jonathan Dolden
Jonathan Dolden

Reputation: 11

Converting CSV to JSON Solution

I have just developed an iOS app using parse for my back end server. I have the app complete and ready to go and have a large number of entires to add to the parse backend, however i have aculated the data not realising until now to up load a class including geo points i need to use json. my data file is structured as follows:

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

I need to convert this format to the following

{ "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"
    }
] }

I have several thousand entries so as you can imagine i don't want to have to do it manually. I only have access to a Mac so any suggestions will need to be Mac friendly. Previous answers I've found haven't worked because of the geographic data.

Upvotes: 0

Views: 5146

Answers (3)

jq170727
jq170727

Reputation: 14665

Here is a solution using jq.

If filter.jq contains the following filter

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

and data contains the sample data then the command

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

produces

{
  "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
      }
    }
  ]
}

Upvotes: 1

micebrain
micebrain

Reputation: 576

You can use a python script (Mac is pre-installed with python) Sample code:

#!/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))

Upvotes: 3

Antti
Antti

Reputation: 1118

Maybe you could use http://www.convertcsv.com/csv-to-json.htm -website to convert those things?

Upvotes: 1

Related Questions