Parse CSV Data with Ruby

Question

I am trying to return a specific cell value based on two criteria.

The logic:

If ClientID = 1 and BranchID = 1, puts SurveyID

Using Ruby 1.9.3, I want to basically look through an excel file and for two specific values located within the ClientID and BranchID column, return the corresponding value in the SurveyID column.

This is what I have so far, which I found during my online searches. It seemed promising, but no luck:

require 'csv'

# Load file
csv_fname = 'FS_Email_Test.csv'

# Key is the column to check, value is what to match
search_criteria = { 'ClientID' => '1', 
                   'BranchID' => '1' }

options = { :headers      =>  :first_row,
            :converters   =>  [ :numeric ] }

# Save `matches` and a copy of the `headers`
matches = nil
headers = nil

# Iterate through the `csv` file and locate where
# data matches the options.

CSV.open( csv_fname, "r", options ) do |csv|
  matches = csv.find_all do |row|
    match = true
    search_criteria.keys.each do |key|
      match = match && ( row[key] == search_criteria[key] )
    end
    match
  end
  headers = csv.headers
end

# Once matches are found, we print the results
# for a specific row. The row `row[8]` is
# tied specifically to a notes field.

matches.each do |row|
  row = row[1]
  puts row
end

I know the last bit of code following matches.each do |row| is invalid, but I left it in in hopes that it will make sense to someone else.

How can I write puts surveyID if ClientID == 1 & BranchID == 1?

Borodin · Accepted Answer · 2013-08-21 22:56:43Z

You were very close indeed. Your only error was setting the values of the search_criteria hash to strings '1' instead of numbers. Since you have converters: :numeric in there the find_all was comparing 1 to '1' and getting false. You could just change that and you're done.

Alternatively this should work for you.

The key is the line

Hash[row].select { |k,v| search_criteria[k] } == search_criteria

Hash[row] converts the row into a hash instead of an array of arrays. Select generates a new hash that has only those elements that appear in search_criteria. Then just compare the two hashes to see if they're the same.

require 'csv'

# Load file
csv_fname = 'FS_Email_Test.csv'

# Key is the column to check, value is what to match
search_criteria = {
  'ClientID' => 1,
  'BranchID' => 1,
}

options = {
  headers:    :first_row,
  converters: :numeric,
}

# Save `matches` and a copy of the `headers`
matches = nil
headers = nil

# Iterate through the `csv` file and locate where
# data matches the options.

CSV.open(csv_fname, 'r', options) do |csv|
  matches = csv.find_all do |row|
    Hash[row].select { |k,v| search_criteria[k] } == search_criteria
  end
  headers = csv.headers
end

p headers


# Once matches are found, we print the results
# for a specific row. The row `row[8]` is
# tied specifically to a notes field.

matches.each { |row| puts row['surveyID'] }

tigeravatar · Accepted Answer · 2013-08-21 22:46:11Z

Possibly...

require 'csv'

b_headers = false
client_id_col = 0
branch_id_col = 0
survey_id_col = 0

CSV.open('FS_Email_Test.csv') do |file|
  file.find_all do |row|
    if b_headers == false then

      client_id_col = row.index("ClientID")
      branch_id_col = row.index("BranchID")
      survey_id_col = row.index("SurveyID")
      b_headers = true

      if branch_id_col.nil? || client_id_col.nil? || survey_id_col.nil? then
        puts "Invalid csv file - Missing one of these columns (or no headers):\nClientID\nBranchID\nSurveyID"
        break
      end

    else
      puts row[survey_id_col] if row[branch_id_col] == "1" && row[client_id_col] == "1"
    end

  end
end

Collectives™ on Stack Overflow

Parse CSV Data with Ruby

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related