Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert .json to .csv in ruby

I want to convert .json file into .csv file using ruby. Pleases help me to do this.

Also propose any tool to achieve this.

like image 630
Shamith c Avatar asked Oct 21 '11 04:10

Shamith c


People also ask

Is it easy to convert JSON to CSV?

As mentioned in the previous answers the difficulty in converting json to csv is because a json file can contain nested dictionaries and therefore be a multidimensional data structure verses a csv which is a 2D data structure.


4 Answers

Try something like this:

require 'csv'
require 'json'

csv_string = CSV.generate do |csv|
  JSON.parse(File.open("foo.json").read).each do |hash|
    csv << hash.values
  end
end

puts csv_string
like image 91
Alex Peattie Avatar answered Sep 18 '22 13:09

Alex Peattie


To actually write to file...

require 'csv'
require 'json'

CSV.open("your_csv.csv", "w") do |csv| #open new file for write
  JSON.parse(File.open("your_json.json").read).each do |hash| #open json to parse
    csv << hash.values #write value to file
  end
end
like image 33
Mark Locklear Avatar answered Sep 18 '22 13:09

Mark Locklear


I think the easies way to convert the JSON to CSV in ruby is using json2csv ruby gem.

PS: I may be biased as I am author of this.

json2csv github repo

Running the command

json2csv convert data/sample.json

Input File

In this particular case it will convert following json:

cat data/sample.json

{
    "12345": {
        "Firstname": "Joe",
        "Lastname": "Doe",
        "Address": {
            "Street": "#2140 Taylor Street, 94133",
            "City": "San Francisco",
            "Details": {
                "note": "Pool available"
            }
        }
    },

    "45678": {
        "Firstname": "Jack",
        "Lastname": "Plumber",
        "Address": {
            "Street": "#111 Sutter St, 94104",
            "City": "San Francisco",
            "Details": {
                "note": "Korean Deli near to main entrance"
            }
        }
    }
}

Output

cat data/sample.json.csv

id,Firstname,Lastname,Address.Street,Address.City,Address.Details.note
12345,Joe,Doe,"#2140 Taylor Street, 94133",San Francisco,Pool available
45678,Jack,Plumber,"#111 Sutter St, 94104",San Francisco,Korean Deli near to main entrance
like image 26
korCZis Avatar answered Sep 16 '22 13:09

korCZis


Based on @Alex's answer but adding csv headers and example test.

# utils.rb
require "csv"
module Utils
  def self.array_of_hashes_to_csv(array_of_hashes)
    CSV.generate do |csv|
      csv << array_of_hashes.first.keys
      array_of_hashes.each { |hash| csv << hash.values }
    end
  end
end

# utils_test.rb
class UtilsTest < MiniTest::Unit::TestCase
  def test_array_of_hashes_to_csv
    array_of_hashes = [
      { :key1 => "value1", :key2 => "value2" },
      { :key1 => "value3", :key2 => "value4" }
    ]
    expected_result = "key1,key2\nvalue1,value2\nvalue3,value4\n"
    assert_equal(expected_result, Utils.array_of_hashes_to_csv(array_of_hashes))
  end
end
like image 40
fguillen Avatar answered Sep 20 '22 13:09

fguillen