CODEWITHSUNDEEP
rubycsv
vaidab
vaidab

Reputation: 59

manipulating csv with ruby

I have a CSV from which I've removed the irrelevant data. Now I need to split "Name and surname" into 2 columns by space but ignoring a 3rd column in case there are 3 names, then invert the order of the columns "Name and surname" and "Phone" (phone first) and then put them into a file ignoring the headers. I've never actually learned Ruby but I've played with Python 10 years ago. Can you help me? This is what I was able to do until now:

E.g.

require 'csv'

csv_table = CSV.read(ARGV[0], :headers => true)

keep = ["Name and surname", "Phone", "Email"]

new_csv_table = csv_table.by_col!.delete_if do |column_name,column_values|
  !keep.include? column_name
end

new_csv_table.to_csv

Upvotes: 0

Views: 154

Answers (1)

Cary Swoveland
Cary Swoveland

Reputation: 110675

Begin by creating a CSV file.

str =<<~END
Name and surname,Phone,Email
John Doe,250-256-3145,[email protected]
Marsha Magpie,250-256-3154,[email protected]
END

File.write('t_in.csv', str)
  #=> 109

Initially, let's read the file, add two columns, "Name" and "Surname", and optionally delete the column, "Name and surname", without regard to column order.

First read the file into a CSV::Table object.

require 'csv'


tbl = CSV.read('t_in.csv', headers: true)
  #=> #<CSV::Table mode:col_or_row row_count:3>

Add the new columns.

tbl.each do |row|
  row["Name"], row["Surname"] = row["Name and surname"].split
end
  #=> #<CSV::Table mode:col_or_row row_count:3>

Note that if row["Name and surname"] had equaled “John Paul Jones”, we would have obtained row["Name"] #=> “John” and row["Surname"] #=> “Paul”.

If the column "Name and surname" is no longer required we can delete it.

tbl.delete("Name and surname")
  #=> ["John Doe", "Marsha Magpie"]

Write tbl to a new CSV file.

CSV.open('t_out.csv', "w") do |csv|
  csv << tbl.headers
  tbl.each { |row| csv << row }
end
  #=> #<CSV::Table mode:col_or_row row_count:3>

Let's see what was written.

puts File.read('t_out.csv')

displays

Phone,Email,Name,Surname
250-256-3145,[email protected],John,Doe
250-256-3154,[email protected],Marsha,Magpie

Now let's rearrange the order of the columns.

header_order = ["Phone", "Name", "Surname", "Email"]


CSV.open('t_out.csv', "w") do |csv|
  csv << header_order
  tbl.each { |row| csv << header_order.map { |header| row[header] } }
end


puts File.read('t_out.csv')
  #=> #<CSV::Table mode:col_or_row row_count:3>

displays

Phone,Name,Surname,Email
250-256-3145,John,Doe,[email protected]
250-256-3154,Marsha,Magpie,[email protected]

Upvotes: 1

Related Questions