Rails - Mar 2006 - Loading the contents of a CSV file to a database

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.

-- 
Posted via http://www.ruby-forum.com/.

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.

-- 
Alex

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

-- 
Posted via http://www.ruby-forum.com/.

Toby Catlin wrote:
<snip>
>       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
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.

-- 
Alex

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


-- 
Posted via http://www.ruby-forum.com/.

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


-- 
Posted via http://www.ruby-forum.com/.

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?

-- 
Alex

> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060322/cd6d6b87/attachment.html

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


-- 
Posted via http://www.ruby-forum.com/.