在 ruby 中将 .json 转换为 .csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7845015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert .json to .csv in ruby
提问by Shamith c
I want to convert .json file into .csv file using ruby. Pleases help me to do this.
我想使用 ruby 将 .json 文件转换为 .csv 文件。请帮助我做到这一点。
Also propose any tool to achieve this.
还提出任何工具来实现这一点。
回答by Alex Peattie
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
回答by Mark Locklear
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
回答by korCZis
I think the easies way to convert the JSON to CSV in ruby is using json2csv ruby gem.
我认为在 ruby 中将 JSON 转换为 CSV 的简单方法是使用 json2csv ruby gem。
PS: I may be biased as I am author of this.
PS：我可能有偏见，因为我是本文的作者。
Running the command
运行命令
json2csv convert data/sample.json
Input File
输入文件
In this particular case it will convert following json:
在这种特殊情况下，它将转换以下 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": "Hyman",
"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,Hyman,Plumber,"#111 Sutter St, 94104",San Francisco,Korean Deli near to main entrance
回答by fguillen
Based on @Alex's answerbut adding csv headersand example test.
基于@Alex 的回答，但添加了csv 标头和示例 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
回答by Sculper
Edit:
编辑：
This functionality described below is now available as a gem. After installing with gem install json_converter, the following snippet can be used to generate a CSV from a valid JSON string or object:
下面描述的这个功能现在可以作为gem 使用。安装后gem install json_converter，以下代码段可用于从有效的 JSON 字符串或对象生成 CSV：
require 'json_converter'
json_converter= JsonConverter.new
# Assume json is a valid JSON string or object
csv = json_converter.generate_csv json
Original Answer:
原答案：
If your JSON data is relatively simple (no nesting or arrays), Alex's answer is probably the cleanest way of handling this problem.
如果您的 JSON 数据相对简单（没有嵌套或数组），Alex 的答案可能是处理这个问题的最简洁的方法。
However, if you doneed to take arrays and nested objects into account, I've attempted to port a web versionof such a converter to ruby. It can be found here. The methods that handle the actual restructuring of data are array_fromand flatten.
但是，如果您确实需要考虑数组和嵌套对象，我已尝试将此类转换器的Web 版本移植到 ruby。可以在这里找到。处理实际数据重组的方法是array_from和flatten。
The array_frommethod attempts to identify what a "row" of data looks like for a given dataset. It is not perfect, and you may want to tweak this part for different datasets.
该array_from方法尝试确定给定数据集的数据“行”是什么样的。它并不完美，您可能需要针对不同的数据集调整此部分。
# Attempt to identify what a "row" should look like
def array_from(json)
queue, next_item = [], json
while !next_item.nil?
return next_item if next_item.is_a? Array
if next_item.is_a? Hash
next_item.each do |k, v|
queue.push next_item[k]
end
end
next_item = queue.shift
end
return [json]
end
The flattenmethod recursively iterates over the JSON object(s), and generates an object that represents headers and values. If an object is nested, the header for its column will be prefixed with its parent key(s), delimited by the /character.
该flatten方法递归地遍历 JSON 对象，并生成一个表示标头和值的对象。如果对象是嵌套的，则其列的标题将以其父键作为前缀，并由/字符分隔。
# The path argument is used to construct header columns for nested elements
def flatten(object, path='')
scalars = [String, Integer, Fixnum, FalseClass, TrueClass]
columns = {}
if [Hash, Array].include? object.class
object.each do |k, v|
new_columns = flatten(v, "#{path}#{k}/") if object.class == Hash
new_columns = flatten(k, "#{path}#{k}/") if object.class == Array
columns = columns.merge new_columns
end
return columns
elsif scalars.include? object.class
# Remove trailing slash from path
end_path = path[0, path.length - 1]
columns[end_path] = object
return columns
else
return {}
end
end
If there are any nullvalues in the original JSON, you'll need to convert these to something other than nilbefore attempting the conversion - you'll generally end up with uneven rows if you don't. The nils_to_stringsmethod handles that:
如果null原始 JSON 中有任何值，则需要nil在尝试转换之前将它们转换为其他值- 如果不这样做，通常最终会得到不均匀的行。该nils_to_strings方法处理：
# Recursively convert all nil values of a hash to empty strings
def nils_to_strings(hash)
hash.each_with_object({}) do |(k,v), object|
case v
when Hash
object[k] = nils_to_strings v
when nil
object[k] = ''
else
object[k] = v
end
end
end
Here's a brief example of how this would be used:
这是一个如何使用它的简短示例：
json = JSON.parse(File.open('in.json').read)
in_array = array_from json
in_array.map! { |x| nils_to_strings x }
out_array = []
in_array.each do |row|
out_array[out_array.length] = flatten row
end
headers_written = false
CSV.open('out.csv', 'w') do |csv|
out_array.each do |row|
csv << row.keys && headers_written = true if headers_written === false
csv << row.values
end
end
And finally, here's some example input/output:
最后，这是一些示例输入/输出：
Input:
输入：
{
"Forms": [
{
"Form": {
"id": "x",
"version_id": "x",
"name": "x",
"category": "",
"subcategory": null,
"is_template": null,
"moderation_status": "x",
"display_status": "x",
"use_ssl": "x",
"modified": "x",
"Aggregate_metadata": {
"id": "x",
"response_count": "x",
"submitted_count": "x",
"saved_count": "x",
"unread_count": "x",
"dropout_rate": "x",
"average_completion_time": null,
"is_uptodate": "x"
}
},
"User": {
"username": "[email protected]"
}
},
{
"Form": {
"id": "x",
"version_id": "x",
"name": "x",
"category": "",
"subcategory": null,
"is_template": null,
"moderation_status": "x",
"display_status": "x",
"use_ssl": "x",
"modified": "x",
"Aggregate_metadata": {
"id": "x",
"response_count": "x",
"submitted_count": "x",
"saved_count": "x",
"unread_count": "x",
"dropout_rate": "x",
"average_completion_time": null,
"is_uptodate": "x"
}
},
"User": {
"username": "[email protected]"
}
}
]
}
Output:
输出：
Form/id,Form/version_id,Form/name,Form/category,Form/subcategory,Form/is_template,Form/moderation_status,Form/display_status,Form/use_ssl,Form/modified,Form/Aggregate_metadata/id,Form/Aggregate_metadata/response_count,Form/Aggregate_metadata/submitted_count,Form/Aggregate_metadata/saved_count,Form/Aggregate_metadata/unread_count,Form/Aggregate_metadata/dropout_rate,Form/Aggregate_metadata/average_completion_time,Form/Aggregate_metadata/is_uptodate,User/username
x,x,x,"","","",x,x,x,x,x,x,x,x,x,x,"",x,[email protected]
x,x,x,"","","",x,x,x,x,x,x,x,x,x,x,"",x,[email protected]
回答by David Lio
Ruby code to convert from json to csv.
将 json 转换为 csv 的 Ruby 代码。
Handles arrays by converting them to double quoted strings.
通过将数组转换为双引号字符串来处理数组。
#json-to-csv.rb
require 'json'
require 'csv'
file = File.read('./input.json')
hash = JSON.parse(file)
CSV.open('./output.csv', 'w') do |csv|
headers = hash.first.keys
csv << headers
hash.each do |item|
values = item.values
printable_values = Array.new
values.each do |value|
printable_values << value.to_s.gsub(/\[|\]/,'').gsub(/"/,'\'')
end
csv << printable_values
end
end
回答by Ishwaryaa Balaji
This is handle headers & nested json.
这是句柄头和嵌套的 json。
require 'csv'
require 'json'
@headers = []
file = File.open('file.json')
JSON.parse(file.read).each do |h|
h.keys.each do |key|
@headers << key
end
end
uniq_headers = @headers.uniq
file = File.open('file.json')
finalrow = []
JSON.parse(file.read).each do |h|
final = {}
@headers.each do |key2|
final[key2] = h[key2]
end
finalrow << final
end
CSV.open('output.csv' , 'w') do |csv|
csv << uniq_headers
finalrow.each do |deal|
csv << deal.values
end
end
