Forum: Ruby on Rails Loading the contents of a CSV file to a database

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
66083b923de61829b4b84735ddb5fa32?d=identicon&s=25 Toby Catlin (tobycatlin)
on 2006-03-21 15:04
Hello Everybody,

I picked up on the buzz about RoR and wanted to see what the fuss is
about. So far  i am very impressed but have hit a bit of a wall with a
little play app i am writting.
I want to iterate through a CSV file of my bank transactions and see
just how much money i spend on beer and pies every month.

I have successfully setup a database with a single table called
transactions and setup a skeleton and entered a few records using the
basic CRUD functions. I have also installed FasterCSV and successfully
listed the contents of the csv file. I thought it would be a simple step
to loop through the file and insert the values in to the db. In addition
to the generated admin_controller.rb file containing list, update etc i
put

  def csv_preview
  require 'faster_csv'
    @csv_trans_action = FasterCSV.read("c:/nugget/statement.csv")
  end

  def csv_load
    require 'faster_csv'
    @csv_trans_actions = FasterCSV.read("c:/nugget/statement.csv")

    for trans_action in @trans_actions
      for csv_trans_action in @csv_trans_actions

        trans_action.trans_date_at  = csv_trans_action[0]
        trans_action.description    = csv_trans_action[1]
        trans_action.amount         = csv_trans_action[2]
        trans_action.save
      end
   end
  end

What is funny is that it seems to insert the first row and then throw an
error about a array value being nil (Sorry don't have the exact message
to hand). This is a bit of a stab in the dark regarding how to do this
so if anyone has any comments i'd be very grateful.
I am also wondering how i would return what happended back to
/views/csv_load.rhtml.
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-03-21 15:30
(Received via mailing list)
Toby Catlin wrote:
>   def csv_load
>     require 'faster_csv'
>     @csv_trans_actions = FasterCSV.read("c:/nugget/statement.csv")
>
>     for trans_action in @trans_actions
What's this line doing?  Where do @trans_actions come from?

>       for csv_trans_action in @csv_trans_actions
>
>         trans_action.trans_date_at  = csv_trans_action[0]
>         trans_action.description    = csv_trans_action[1]
>         trans_action.amount         = csv_trans_action[2]
>         trans_action.save
>       end
>    end
>   end
It looks to me like you're looping over your existing transactions,
rewriting each of them with *all* of the csv's transactions in turn - I
doubt that's what you want.

Is there a blank line at the end of the CSV file?  If there was, I
wouldn't be surprised if you ended up with a nil value for
csv_trans_action at the end of the loop.
66083b923de61829b4b84735ddb5fa32?d=identicon&s=25 Toby Catlin (tobycatlin)
on 2006-03-21 18:58
The nested loop was intentional. Ultimatly i want to do a check to see
if the record in the csv file has already been inserted into the
database, although when i look at it now it does seem a little out of
whack.

i guess the best place for me to start would be a single loop of the csv
file adding each row to the db. would this be like (sorry at work so
can't try it myself):

  def csv_load
    require 'faster_csv'
    @csv_trans_actions = FasterCSV.read("c:/nugget/statement.csv")

      for csv_trans_action in @csv_trans_actions

        trans_action.trans_date_at  = csv_trans_action[0]
        trans_action.description    = csv_trans_action[1]
        trans_action.amount         = csv_trans_action[2]
        trans_action.save
      end
  end

in theory should that work?? assuming there is a table called
trans_actions with trans_date_at, description and amount as fields

thanks toby
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-03-21 19:05
(Received via mailing list)
Toby Catlin wrote:
<snip>
> trans_actions with trans_date_at, description and amount as fields
You'll need to actually reference the TransAction model at some point,
so something like:

for csv_trans_action in @csv_trans_actions
   TransAction.create(:trans_date_at => csv_trans_action[0],
     :description => csv_trans_action[1],
     :amount => csv_trans_action[2])
end

would probably be better.
66083b923de61829b4b84735ddb5fa32?d=identicon&s=25 Toby Catlin (tobycatlin)
on 2006-03-21 21:50
Thanks thats nearly got it but for some reason there are no values after
the first iteration of the loop
The first row of the csv inserts fine but I get this error on the second
trip round:

Mysql::Error: #23000Column 'trans_date_at' cannot be null: INSERT INTO
trans_actions (`trans_date_at`, `amount`, `description`) VALUES(NULL,
-10.0, 'CASH RB SCOT FEB28 LONDON CAMDE@09:42')

I have checked the csv file and it's fine. it loads all the values in
the about csv_list function.
thanks for any help
66083b923de61829b4b84735ddb5fa32?d=identicon&s=25 Toby Catlin (tobycatlin)
on 2006-03-21 21:54
oh ended up with btw

 def csv_load
    require 'faster_csv'
    @csv_trans_actions =
FasterCSV.read("D:/nugget/public/statement.csv")

    for csv_trans_action in @csv_trans_actions
       TransAction.create(:trans_date_at => csv_trans_action[0],
         :description => csv_trans_action[1],
         :amount => csv_trans_action[2])
    end
  end
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-03-22 10:09
(Received via mailing list)
Toby Catlin wrote:
> Thanks thats nearly got it but for some reason there are no values after
> the first iteration of the loop
> The first row of the csv inserts fine but I get this error on the second
> trip round:
>
> Mysql::Error: #23000Column 'trans_date_at' cannot be null: INSERT INTO
> trans_actions (`trans_date_at`, `amount`, `description`) VALUES(NULL,
> -10.0, 'CASH RB SCOT FEB28 LONDON CAMDE@09:42')
>
Without wishing to pry, what are the first two lines of the CSV?
91eb330fb36d1e03c856574dfb77d2bc?d=identicon&s=25 Thibaut Barrère (Guest)
on 2006-03-22 11:00
(Received via mailing list)
> Mysql::Error: #23000Column 'trans_date_at' cannot be null: INSERT INTO
> trans_actions (`trans_date_at`, `amount`, `description`) VALUES(NULL,
> -10.0, 'CASH RB SCOT FEB28 LONDON CAMDE@09:42')

I'm doing almost the same thing as you : import csv files using ar.

Out of my head I seem to recall I had that kind of error when the date
could
not be properly parsed. I ended up writing something like:

trans_action.trans_date_at  = check_date(csv_trans_action['date'])

With some verbose error message when the date cannot be parsed.
66083b923de61829b4b84735ddb5fa32?d=identicon&s=25 Toby Catlin (tobycatlin)
on 2006-03-22 16:17
That looks like it might do the trick. GOing to have to wait til after
work to try it out :(
must get instant rails on a thumb drive or something
This topic is locked and can not be replied to.