How can i add an Array into a column of a CSV file in Ruby?

Question

i'm building a CSV file using ruby and i have different columns, i.e.

"company_name", "company_phones"

The company name is no issue since it's a simple string, but the company has more than one phone, which in my code is being stored as

phones = [phone1, phone2]

When i add it into the CSV as follows:

      CSV.open("output.csv", "a") do |csv|
    csv << [
      company_name,
      company_phones,
    ]
  end

I've tried different ways to do it but it ends up:

1) Showing up with double double quotes ([""Phone1"", ""Phone2""]) 2) And this, when reading the CSV, shows like ["\"Phone1"", "\"Phone2""] and it's not even an array but a string.

Is there something i'm missing, or it just can't be done writing CSV and i should change the format of my output?

You want CSV inside your CSV? You might really want JSON or possibly YAML. — tadman
– tadman, Commented Jun 11, 2020 at 2:37
What is the purpose of the csv file? As tadman pointed out you might be better off using json or yaml, if you want to store and read complex data from a text-representation. Both formats would support Arrays, while in csv - as the current awnser explains - requires you to choose a custom serialization for you values. Then again, when reading from your csv file, you need the knowledge of how exactly you serialized the array to be able to deserialize it. For json / yaml you can rely on existing parsers to do the job right. — trueunlessfalse
– trueunlessfalse, Commented Jun 11, 2020 at 6:11

Cary Swoveland · Accepted Answer · 2020-09-21 18:17:14Z

This situation calls for a CSV custom converter.

Write the CSV file

Let's first create a CSV file from the following information.

FName = 't.csv'

headers = ["company", "vals", "phone_nbrs"]
rows = [
  ['ABC', [1.3, 2.1, 4.1], ["800-555-1000", "800-555-1001"]],
  ['XYZ', [7.3, 9.5], ["800-555-2000", "800-555-2001"]] 
]

I will do that by converting each of the arrays in rows to a string containing elements of the array converted to strings and separated by a space. For example, [1.3, 2.1, 4.1] will be saved as the string:

[1.3, 2.1, 4.1].join(' ')
  #=> "1.3 2.1 4.1"

As will be seen, this particular string representation of arrays is arbitrary, just one among many ways of doing that. For example we could save the array of floats shown above as "1.3:2.1:4.1" or "1.3¯\\_(ツ)_/¯2.1¯\\_(ツ)_/¯4.1".

Let's now create the CSV file.

require 'csv'

CSV.open(FName, "w") do |csv|
  csv << headers
  rows.each { |name,vals,phone_nbrs|
    csv << [name, vals.join(' '), phone_nbrs.join(' ')] }
end

Let's see what was written.

puts File.read(FName)
company,vals,phone_nbrs
ABC,1.3 2.1 4.1,800-555-1000 800-555-1001
XYZ,7.3 9.5,800-555-2000 800-555-2001

Read the CSV file

The trick here is to define a converter that converts the strings of strings representing floats to arrays of floats and the strings of telephone numbers to arrays of strings.

convert_arrays = lambda do |value, field|
  case field.header
  when 'vals'
    value.split.map(&:to_f)
  when 'phone_nbrs'
    value.split
  else
    value
  end
end
  #=> #<Proc:0x0000599427913ec0@(irb):19 (lambda)>

Now read the file into an array.

CSV.foreach(FName, :headers => true, converters: [convert_arrays]).
  with_object([]) { |row, arr|
    arr << row.values_at('company', 'vals', 'phone_nbrs') }
  #=> [["ABC", [1.3, 2.1, 4.1], ["800-555-1000", "800-555-1001"]],
  #    ["XYZ", [7.3, 9.5], ["800-555-2000", "800-555-2001"]]]

See CSV::new and these examples of CSV converters.

This seems like a good and comprehensive answer if you're forced to use csv - but as was mentioned above, there might be better options for storing complex data as text.
@trueunlessfalse, I confess to being a little mischievous in my reply to your comment, but couldn’t resist. Keep in mind that most askers at SO are motivated by one of two things: need and curiosity. It seems like this question is mostly of the need variety, so your comment on the question is germane, but don’t undervalue questions motivated by curiosity, which can have great educational value. I've learned a lot about regular expressions, for example, from SO questions whose answers require enormously complex and contorted regexes that no one in their right mind would use in practice.
This is great and it's actually what i ended up doing! Yeah i would've used JSON but this is a freelance job for a contractor and he wants everything stored in CSV, so that's it.

Collectives™ on Stack Overflow

How can i add an Array into a column of a CSV file in Ruby?

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related