How to get correct csv format from hash in ruby
Hash to csv
hash:
{
"employee" => [
{
"name" => "Claude",
"lastname"=> "David",
"profile" => [
"age" => "43",
"jobs" => [
{
"name" => "Ingeneer",
"year" => "5"
}
],
"graduate" => [
{
"place" => "Oxford",
"year" => "1990"
},
],
"kids" => [
{
"name" => "Viktor",
"age" => "18",
}
]
}
}]
This is an example hash I will be working on. So, as you can see, there are many array levels in it.
My question is, how do I put it in the CSV file correctly?
I've tried this:
column_names = hash['employee'].first.keys
s=CSV.generate do |csv|
csv << column_names
hash['scrap'].each do |x|
csv << x.values
end
end
File.write('myCSV.csv', s)
but I only get name
, lastname
and profile
as a key when I catch them all ( age
, jobs
, name
, year
, graduate
, place
...).
Also, how can I concatenate one value in each case? Because I actually have all of them employee[x]
that only accept one cell. Are there any parameters that I missed?
Ps: This could be the next post
source to share
Valid CSV output has a fixed number of columns, your hash has a variable number of values. The jobs
, graduate
and keys kids
can have several meanings.
If your only goal is to make a CSV output that can be read in Excel, for example, you can enumerate your hash, take the maximum number of key / value pairs per key, sum it, and then write your CSV output, fill in the blanks with "".
There are many examples on stack overflow here, looking for a "deep hash" to start with.
Your result will have a different number of columns with each of your hashes.
This is too much work if you ask me. If you just want to present a readable result, your best and easiest option is to convert the Hash to YAML, which is generated for readability:
require 'yaml'
hash = {.....}
puts hash.to_yaml
employee:
- name: Claude
lastname: David
profile:
- age: '43'
jobs:
- name: Ingeneer
year: '5'
graduate:
- place: Oxford
year: '1990'
kids:
- name: Viktor
age: '18'
source to share
If you want to convert a hash to a CSV file or record, you need to get a "flat" representation of your keys and values. Something like the following:
h = {
a: 1,
b: {
c: 3,
d: 4,
e: {
f: 5
},
g: 6
}
}
def flat_keys(h)
h.keys.reject{|k| h[k].is_a?(Hash)} + h.values.select{|v| v.is_a?(Hash)}.flat_map{|v| flat_keys(v)}
end
flat_keys(h)
# [:a, :c, :d, :g, :f]
def flat_values(h)
h.values.flat_map{|v| v.is_a?(Hash) ? flat_values(v) : v}
end
flat_values(h)
# [1, 3, 4, 5, 6]
Then you can apply this to generate CSV output.
source to share
It depends on how these fields are represented in the database.
For example, yours jobs
has a keyed hash name
, and yours kids
also has a keyed hash name
, so you can't just flatten them, because the keys must be unique.
jobs
- probably a different model (database table), so you probably have to (depending on the database) write it separately, including things like related object id, etc.
Are you sure you're not overhead? Judging from your last question and because you seem to be considering csv as a simple key pair, omitting all the database view and relationships.
source to share