Ignore rows with blank values while importing a CSV in rails application
Solution 1
This should work
CSV.open(import_file, skip_blanks: true).reject { |row| row.all?(&:nil?) }
EDIT
You requested for readlines, it calls open in the CSV source code in the end but this is it:
CSV.readlines(import_file, skip_blanks: true).reject { |row| row.all?(&:nil?) }
I feel open would perform better though I have not done any bench marking
CSV.open(import_file, skip_blanks: true, headers: true).reject { |row| row.to_hash.values.all?(&:nil?) }
CSV.readlines(import_file, skip_blanks: true, headers: true).reject { |row| row.to_hash.values.all?(&:nil?) }
The above returns a collection of CSV::Row objects
Solution 2
This returns a CSV::Table object.
CSV.parse(import_file, headers: true, skip_blanks: true).delete_if { |row| row.to_hash.values.all?(&:blank?) }
Solution 3
The solution depends on if you read the CSV with or without headers, and if you want to keep working with a CSV::Table
object or if you are happy with an array of CSV::Row
Without headers you'll get rows that looks something like:
[#<CSV::Row nil nil nil]
With headers:
[#<CSV::Row "name":nil "abbreviation":nil "age":nil]
SOLUTION
Without headers and return array of CSV::Row
table.reject { |row| row.all?(&:nil?) }
Without headers and return CSV::Table
table.delete_if { |row| row.to_hash.values.all?(&:nil?) }
With headers and return array of CSV::Row
table.reject { |row| row.to_hash.values.all?(&:nil?) }
Without headers and return CSV::Table
table.delete_if { |row| row.to_hash.values.all?(&:nil?) }
FURTHER READING
Ruby Documentation for delete_if
Jackson
Updated on June 22, 2022Comments
-
Jackson over 1 year
I have an app where I allow users to import CSV data.
Everything was working until users started importing data with blank rows in the CSV file. I am using the following to grab the rows out of the CSV file:
CSV.readlines(import_file, headers: true, skip_blanks: true)
I thought that if I added the option to skip_blanks that it would do that but it hasn't. Any ideas on how I can ignore the blank rows.
Thanks!
-
Jackson almost 10 yearsThis is a good answer but is there anyway to do the same thing but use readlines instead. (I would prefer to stick to that because the csv importer depends on the format that readlines provides the data in).
-
bjhaid almost 10 years@GrahamJackson updated the answer to use readlines as you requested, if this works can you upvote and accept the answer?
-
Jackson almost 10 yearsThank you for the help. This doesn't solve my problem but I appreciate it and will up vote it because it may help someone else. I need to remove the null values but still return a CSV object. This solution will return an array.
-
bjhaid almost 10 years@GrahamJackson let me modify more, so you have your csv objects
-
Jackson almost 10 yearsThank you. The new solution you provided does provide a collection of csv objects but doesn't end up removing the rows with nil values.
-
bjhaid almost 10 years@GrahamJackson well with my test csv it does remove it, I need to catch some sleep, I hope I have helped :-)
-
Jackson almost 10 yearsOk, so I figured out how to get the results back as an array of CSV objects thanks to your solution. Something like this: 'CSV.readlines(import_file, skip_blanks:true, headers:true).reject {|row| row.to_hash.values.all?(&:nil?)}'
-
Keith Johnson over 9 yearsCan the .reject block be used and also return a CSV::Table object? I've been playing around with this and it always returns an array.
-
wired00 over 7 yearsthe
blank?
was a nice solution for me, then works for '', ' ' and nil row data