Rails Seed DB From CSV - Trouble With Date Format

Question

(Here's my requisite line about being new to ruby/rails and programming in general; first SO question as well)

I have a rake task to seed my MySQL DB from a CSV-

...
    CSV.read(uri).each do |row|
      Incident.create(
        :building_address => row[9],
        :agency => row[3],
        :complaint_type => row[6],
        :descriptor => row[5],
        :created => row[1],
        :closed => row[2],
        )
end

:created and :closed are defined as date fields (and are also separate from/unrelated to the rails created_at and updated_at fields), but I'm assuming CSV files recognize only strings.

When I run this task, day and month get switched around. So 1/12/2010 12:00:00 AM in the CSV (which for the CSV and myself in the US means January 12) ends up being parsed as 2010-12-1, and any day past 12 returns null, so 1/20/2010 12:00:00 AM results in a null entry in my DB.

I added

date:
    formats:
        default: "%m/%d/%Y"

to my en.yml, but that seems to only affect the display of a date, and has no effect on the creation of objects through my rake task. After searching around SO and other resources I tried

:created => row[1].strftime,

and

:created => row[1].strptime,

but got undefined method strptime/strftime for "Created Date":String (Created Date being the name of the column in the CSV).

So I also tried

:created => row[1].to_date

to change the (presumably) "date" string in the CSV to a date, but get an error undefined method '<' for nil:NilClass

Changing the date field in the CSV seems like a nonstarter as there are 1 million+ records.

How can I edit this rake task to properly seed my db from this CSV? Or should I change :created from a date to string and manipulate it in the application logic?

mu is too short · Accepted Answer · 2011-11-18 02:13:45Z

1

You could try using DateTime.strptime to convert your strings to DateTime instances:

:created => DateTime.strptime(row[1], '%m/%d/%Y %H:%M:%S %p'),
:closed  => DateTime.strptime(row[2], '%m/%d/%Y %H:%M:%S %p')

The strptime class method will let you specify exactly the format you're using so that none of the software has to guess. I'd recommend that you stick to ISO 8601 date and time formats for internal use in the future, leave the ambiguous formats for human consumption.

answered Nov 18, 2011 at 2:13

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

6 Comments

sbauch Over a year ago

Tried this and a few variations last night, but keep getting an invalid date error. I thought maybe because :created and :closed were Date class instead of DateTime, but changing didn't help. Will keep trying tonight while also reading up more on the ruby docs you linked to.

mu is too short Over a year ago

@sbauch: What type are the :created and :closed columns? Are they date, datetime, or timestamp? If they're dates then try Date.strptime(row[1], '%m/%d/%Y') and Date.strptime(row[2], '%m/%d/%Y') to ignore the time of day part.

sbauch Over a year ago

I tried it with both. Right now I have both columns as date and am able to use Date.strptime('12/15/2010 12:00 AM', '%m/%d/%Y') (having copied that date directly from my csv) in my console fine. But still getting the invalid date error on the rake task.

mu is too short Over a year ago

@sbauch: Which one is complaining about an "invalid date", Date or Incident?

sbauch Over a year ago

Looks like my CSV header was gumming this up. Thanks for the help! edit- Though I think I figured this out, how would one tell? I was getting invalid date /Users/Sam/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/date.rb:1022:in new_by_frags'

|

Collectives™ on Stack Overflow

Rails Seed DB From CSV - Trouble With Date Format

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related