How do I create a copy of some columns of a CSV file in Ruby with different data in one column?

85
votes

I have a CSV file called "A.csv". I need to generate a new CSV file called "B.csv" with data from "A.csv".

I will be using a subset of columns from "A.csv" and will have to update one column's values to new values in "B.csv". Ultimately, I will use this data from B.csv to validate against a database.

  1. How do I create a new CSV file?
  2. How do I copy the required columns' data from A.csv to "B.csv"?
  3. How do I append values for a particular column?

I am new to Ruby, but I am able to read CSV to get an array or hash.

3
rubycsv
This is lacking basic information, such as showing us your effort toward solving the problem. This information is in the CSV documentation. Read "How to Ask" and "minimal reproducible example". – the Tin Man
Possible duplicate of Output array to CSV in Ruby – phunehehe

3 Answers

193
votes

As mikeb pointed out, there are the docs - http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/CSV.html - Or you can follow along with the examples below (all are tested and working):

To create a new file:

In this file we'll have two rows, a header row and data row, very simple CSV:

require "csv"
CSV.open("file.csv", "wb") do |csv|
  csv << ["animal", "count", "price"]
  csv << ["fox", "1", "$90.00"]
end

result, a file called "file.csv" with the following:

animal,count,price
fox,1,$90.00

How to append data to a CSV

Almost the same formula as above only instead of using "wb" mode, we'll use "a+" mode. For more information on these see this stack overflow answer: What are the Ruby File.open modes and options?

CSV.open("file.csv", "a+") do |csv|
  csv << ["cow", "3","2500"]
end

Now when we open our file.csv we have:

animal,count,price
fox,1,$90.00
cow,3,2500

Read from our CSV file

Now you know how to copy and to write to a file, to read a CSV and therefore grab the data for manipulation you just do:

CSV.foreach("file.csv") do |row|
  puts row #first row would be ["animal", "count", "price"] - etc.
end

Of course, this is like one of like a hundred different ways you can pull info from a CSV using this gem. For more info, I suggest visiting the docs now that you have a primer: http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/CSV.html

4
votes

Have you seen Ruby's CSV class? It seems pretty comprehensive. Check it out here: http://ruby-doc.org/stdlib-1.9.3/libdoc/csv/rdoc/CSV.html

0
votes

You will probably want to use CSV::parse to help Ruby understand your CSV as the table of data that it is and enable easy access to values by header.

Unfortunately, the available documentation on the CSV::parse method doesn't make it very clear how to actually use it for this purpose.

I had a similar task and was helped much more by How to Read & Parse CSV Files With Ruby on rubyguides.com than by the CSV class documentation or by the answers pointing to it from here.

I recommend reading that page in its entirety. The crucial part is about transforming a given CSV into a CSV::Table object using:

table = CSV.parse(File.read("cats.csv"), headers: true)

Now there's documentation on the CSV::Table class, but again you might be helped more by the clear examples on the rubyguides.com page. One thing I'll highlight is that when you tell .parse to expect headers, the resulting table will treat the first row of data as row [0].

You will probably be especially interested in the .by_col method available for your new Table object. This will allow you to iterate through different column index positions in the input and/or output and either copy from one to the other or add a new value to the output. If I get it working, I'll come back and post an example.