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

PendingIntent and AlarmManager

java android android-intent android-alarms

I'm trying to broadcast after 20 seconds and receive the broadcast with an extended receiver ExtendedReceiver.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.