Parsing Rails CSV to Hash

Question

I'm trying to parse a .csv file in rails but things are acting odd. As I convert it to a hash, I can't access some keys and the values aren't always correctly associated with the right header.

Each row.to_hash looks something like this:

{"\"Name\""=>"\"Windows Explorer\"", "\"Start\""=>"\"15/03/26 5:34:00 PM\"", "\"End\""=>"\"15/03/26 5:34:10 PM\"", "\"Duration\""=>"\"0:00:10\"", "\"Process\""=>"\"Windows Explorer\""}

row["\"Process\""] returns "\"Windows Explorer\""

However, row["\"Name\""] returns nil

I'm not sure why it isn't returning "\"Windows Explorer\""

Any Ideas?

code

def self.import(file)

    mappings = {"\"Start\"" => "Start", "\"End\"" => "End","\"Duration\"" => "Duration","\"Process\"" => "Process","\"Name\"" => "Name"}

    CSV.foreach(file.path, headers: true, :quote_char => "\'") do |row| #need to escape quotes for import
        csv_row = row.to_hash
        csv_row = Hash[csv_row.map {|k, v| [mappings[k] || k, v] }] # remove escaped characters
        TimeLog.create(start_time: csv_row["Start"], end_time: csv_row["End"], duration: csv_row["Duration"], application: csv_row["Process"], document: csv_row["Name"])
    end

end

csv sample

"Name","Start","End","Duration","Process"
"Windows Explorer","15/03/26 5:34:00 PM","15/03/26 5:34:10 PM","0:00:10","Windows Explorer"
"Info","15/03/26 5:33:53 PM","15/03/26 5:34:00 PM","0:00:07","Time Doctor Lite"
"Question","15/03/26 5:33:47 PM","15/03/26 5:33:53 PM","0:00:06","Time Doctor Lite"
"AutoCAD","15/03/26 5:33:33 PM","15/03/26 5:33:47 PM","0:00:14","AutoCAD"
"ManicTime - TRIAL","15/03/26 5:33:28 PM","15/03/26 5:33:33 PM","0:00:05","ManicTime"
"Downloads","15/03/26 5:33:22 PM","15/03/26 5:33:28 PM","0:00:06","Windows Explorer"
"My Drive - Google Drive - Google Chrome","15/03/26 5:32:33 PM","15/03/26 5:33:22 PM","0:00:49","Google Chrome"
"Downloads","15/03/26 5:32:19 PM","15/03/26 5:32:33 PM","0:00:14","Windows Explorer"
"Microsoft Excel - Ryan App Tracking Thu.xlsx","15/03/26 5:32:14 PM","15/03/26 5:32:19 PM","0:00:05","Microsoft Excel"
"Save As","15/03/26 5:31:53 PM","15/03/26 5:32:14 PM","0:00:21","Microsoft Excel"
"Microsoft Excel - Ryan App Tracking Tue.xlsx","15/03/26 5:31:37 PM","15/03/26 5:31:53 PM","0:00:16","Microsoft Excel"
"ManicTime - TRIAL","15/03/26 5:31:21 PM","15/03/26 5:31:37 PM","0:00:16","ManicTime"
"My Drive - Google Drive - Google Chrome","15/03/26 5:31:10 PM","15/03/26 5:31:21 PM","0:00:11","Google Chrome"
"Inbox - ryan@coincraft.co - CoinCraft Mail - Google Chrome","15/03/26 5:31:01 PM","15/03/26 5:31:10 PM","0:00:09","Google Chrome"
"Untitled - Google Chrome","15/03/26 5:30:56 PM","15/03/26 5:31:01 PM","0:00:05","Google Chrome"
"Form Submission - Habitech Enquiry - Get Started - admin@smartskin.com.au - Habitech Systems Mail - Google Chrome","15/03/26 5:30:47 PM","15/03/26 5:30:56 PM","0:00:09","Google Chrome"
"Inbox (263) - admin@smartskin.com.au - Habitech Systems Mail - Google Chrome","15/03/26 5:30:39 PM","15/03/26 5:30:47 PM","0:00:08","Google Chrome"
"Master","15/03/26 5:30:33 PM","15/03/26 5:30:39 PM","0:00:06","Windows Explorer"
"Overview - Report 26/03/2015 17:29 - Reports - Paymo 3 - Google Chrome","15/03/26 5:30:22 PM","15/03/26 5:30:33 PM","0:00:11","Google Chrome"

`CSV.new(File.open(file.path, "r:bom|utf-8"), headers: true).each do |row|` works see http://stackoverflow.com/questions/25232719/ruby-csv-illegal-quoting-in-line-1-csvmalformedcsverror — Ryan King, Mar 27 '15 at 23:13

score 2 · Answer 1 · answered Mar 27 '15 at 15:08

2

If you just use " as your quote_char you don't need the mapping.

This is what I get when I run it in my terminal using your supplied data:

2.1.2 :004 > CSV.foreach('data.csv', :headers => true, :quote_char => '"') { |r| puts r.to_hash }
{"Name"=>"Windows Explorer", "Start"=>"15/03/26 5:34:00 PM", "End"=>"15/03/26 5:34:10 PM", "Duration"=>"0:00:10", "Process"=>"Windows Explorer"}

answered Mar 27 '15 at 15:08

Drenmi

8,492
4
42
51

I get `CSV::MalformedCSVError: Illegal quoting in line 1.` – Ryan King Mar 27 '15 at 22:42
1

`CSV.new(File.open(file.path, "r:bom|utf-8"), headers: true).each do |row|` works see http://stackoverflow.com/questions/25232719/ruby-csv-illegal-quoting-in-line-1-csvmalformedcsverror – Ryan King Mar 27 '15 at 23:13

score 1 · Answer 2 · answered Mar 27 '15 at 15:09

1

Try row["\"Name\""] with a capital 'N' like it appears to be in the hash.

answered Mar 27 '15 at 15:09

msergeant

4,771
3
25
26

Sorry that was just a typo above `row["\"Name\""]` returns `nil`. – Ryan King Mar 27 '15 at 22:38

Parsing Rails CSV to Hash

2 Answers2