Importing CSV files to database - Rejecting certain rows

If I want to import a csv file, which would include an email column, is there a way to only accept rows that have a valid email format and reject rows that have invalid formats?

Answers


You can filter the rows while you digest them. I normally parse the CSV and generate and save objects. You could filter with standard validations.

If my controller has an import action and expects a csv_file parameter the controller action looks like:

def import
  require "csv"
  if params[:csv_file].present?
    CSV.parse(File.open(params[:csv_file].tempfile){|f| f.read}, headers: true, header_converters: :symbol) do |row|
      obj = MyObject.new(row)
      obj.save # validation filter rows
    end
  end
end

and in your model

validates_format_of :email, :with => /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\z/i

You can use a regex expression to match the column content before processing the row, e.g.:

CSV.foreach(path_to_file, :headers => true) do |row|
  if /\A[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]+\z/.match(row['email'])
     #process the row (e.g. insert into database)
  end
end

Need Your Help

How to add new Properties to an existing class at runtime independent of existing members with read-write capability in c#

c# dynamic runtime properties creation

I need to make a property for each column in a database table but we do not know the number of columns before hand.

How to post parameter value to some actionlink

asp.net asp.net-mvc-3

In my view i have 10 link every link associated with some unique value. Now i want that associated value at my controller action and from that action i want to redirect the flow to some other action