From: James Edward Gray II <james@...>
Date: 2009-10-01T22:56:29+09:00
Subject: Re: FasterCSV - varying headers

On Oct 1, 2009, at 3:09 AM, Sean Mcknew wrote:

> Hello,

Hello.

> I'm attempting to build a little program that  operates on a large csv
> file (potentially 100,000+ lines), but the challenge is that while I
> will have a couple required columns, I must provide some naming
> flexibility as it is unlikely that the user will be able to match my
> headers word for word in every case. As such, my goal is to provide an
> interface that asks what each header should represent and then treat  
> the
> user's headers as if they followed my original specifications exactly.

> Alternatively, if there's a more appropriate way to tackle this, I'm  
> all ears.

I have some ideas.

First, let's talk about the matching headers problem.  Coming up with  
everything a user might think of to type in sounds hard to me.  What  
if we showed the user which headers are available instead and had them  
pick from a list?  It seems like that would be easier and more accurate.

My other thought is that it looks like you are slurping the whole file  
into memory just to write it all back out.  Why don't we just read a  
line, fix it, write it out, and move on to the next line?  That should  
take less memory.

Here's some example code combining these thoughts:

   $ cat products.csv
   Product Title,Product Price,Product Rating
   Agricola,$55.99,4.5
   Dominion,$35.99,5
   Pandemic,$27.99,4.75
   $ ruby csv_transfer.rb products.csv
   1: Product Title
   2: Product Price
   3: Product Rating
   d: Done

   Column to include:  1
   Added Product Title.
   2: Product Price
   3: Product Rating
   d: Done

   Column to include:  2
   Added Product Price.
   3: Product Rating
   d: Done

   Column to include:  d
   $ cat products_new.csv
   Product Title,Product Price
   Agricola,$55.99
   Dominion,$35.99
   Pandemic,$27.99
   $ cat csv_transfer.rb
   #!/usr/bin/env ruby -wKU

   require "rubygems"
   require "faster_csv"

   file    = ARGV.shift or abort "USAGE:  #{$PROGRAM_NAME} CSV_FILE"
   columns = [ ]
   FCSV.open("#{File.basename(file, '.csv')}_new.csv", "w") do |csv|
     FCSV.foreach(file, :headers => true) do |row|
       # The following is a simple menu selection for columns.
       if columns.empty?
         loop do
           choices = { }
           row.headers.each_with_index do |column, i|
             unless columns.include? column
               n = i + 1
               puts "#{n}: #{column}"
               choices[n] = column
             end
           end
           puts "d: Done"
           puts
           print "Column to include:  "
           choice = gets or break
           if column = choices[choice.strip.to_i]
             columns << column
             puts "Added #{column}."
           elsif choice =~ /\Ad(?:one)?\Z/i
             break
           else
             puts "Invalid column selection."
           end
         end
         if columns.empty?
           puts "No columns selected."
           exit
         end
         csv << columns
       end

       # Copy only the selected columns.
       csv << columns.map { |column| row[column] }
     end
   end

   __END__

Hope that helps.

James Edward Gray II