Changing Date Format on Rails CSV Import to MYSQL

Question

Not sure how to change a date in my csv file when importing into MySQL that is in 12/1/2011(mm/dd/yyyy) format. MySQL is in yyyy/mm/dd.

How can I do this?

My rake file

require 'csv'
require 'date'

desc "Import gac from csv file"
task :import => [:environment] do
  Dir.chdir("#{Rails.root}/lib/assets")
  csv_file = "file.csv" 
  CSV.foreach(csv_file, :headers => true) do |row|
    Institution.create({
      :company => row[0],
      :solveid => row[2],
      :phone => row[5],
      :other => row[6],
      :clientdate => (DateTime.strptime row[7], "%m/%d/%Y").strftime "%Y/%m/%d", 
      :cunumber => row[1],
      :street => row[8], 
      :city => row[9], 
      :state_id => row[4], 
      :zip => row[10],
      :source => row[11], 
      :source2 => row[12],
      :demodate1 => (DateTime.strptime row[13], "%m/%d/%Y").strftime "%Y/%m/%d",
      :demodate2 => (DateTime.strptime row[14], "%m/%d/%Y").strftime "%Y/%m/%d",
      :demodate3 => (DateTime.strptime row[15], "%m/%d/%Y").strftime "%Y/%m/%d",
      :client => row[17],
      :prospect => row[19], 
      :alliedlead => row[16], 
      :notcontacted => row[18]
    })
  end
end

This is giving me an error.

rake import
rake aborted!
/Users/dave/rails_projects/allied_contest/lib/tasks/intitutions.rake:14: syntax error, unexpected tSTRING_BEG, expecting '}'
... row[7], "%m/%d/%Y").strftime "%Y/%m/%d", 
...                               ^
/Users/dave/rails_projects/allied_contest/lib/tasks/intitutions.rake:14: syntax error, unexpected ',', expecting keyword_end
...%m/%d/%Y").strftime "%Y/%m/%d", 
...                               ^

John · Accepted Answer · 2012-11-02 06:55:28Z

3

   if row[7].present?
      date = row[7].split("/")
      new_date = "#{date[2]}/#{date[0]}/#{date[1]}"
   else
     new_date = "N/A"
   end

EDIT: The error you are getting is because the row is nil. I've added a check for nil.

edited Nov 2, 2012 at 6:55

answered Nov 2, 2012 at 3:32

John

4,3825 gold badges34 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DaveG Over a year ago

John, How would I do this with a row? Should it be row[7] = row[7].split("/") or something similar?

DaveG Over a year ago

John getting the following: rake aborted! undefined method `split' for nil:NilClass

John Over a year ago

updated my answer. There must be some rows in your csv file that do not have dates.

Sean Vieira · Accepted Answer · 2012-11-02 03:43:55Z

2

If you need to validate the dates then you can use the DateTime::strptime and DateTime#strftime methods:

require 'date'

# ... snip ...
:clientdate => DateTime.strptime(row[7], "%m/%d/%Y").strftime("%Y/%m/%d")

edited Nov 2, 2012 at 3:43

answered Nov 2, 2012 at 3:35

Sean Vieira

161k34 gold badges321 silver badges297 bronze badges

6 Comments

DaveG Over a year ago

Sean...I keep getting the following error: rake aborted! /Users/dave/rails_projects/allied_contest/lib/tasks/intitutions.rake:14: syntax error, unexpected tSTRING_BEG, expecting '}' ... row[7], "%m/%d/%Y").strftime "%Y/%m/%d", ... ^ /Users/dave/rails_projects/allied_contest/lib/tasks/intitutions.rake:14: syntax error, unexpected ',', expecting keyword_end ...%m/%d/%Y").strftime "%Y/%m/%d",

Sean Vieira Over a year ago

@DaveG - This may be one of those cases where you need parenthesis for method calls - I updated my answer.

DaveG Over a year ago

I'm sure this works. I guess I need to check for nil values since the date isn't on all fields. Following error: "can't convert nil into String"

Dfr Over a year ago

You can handle nil case this way: :clientdate => row[7].nil? ? nil : DateTime.strptime(row[7], "%m/%d/%Y").strftime("%Y/%m/%d")

DaveG Over a year ago

Thanks Sean, both great answers, and both work. I felt like this was more concise. Thanks Again!

|

Collectives™ on Stack Overflow

Changing Date Format on Rails CSV Import to MYSQL

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related