Toby Catlin
2006-Mar-21 14:04 UTC
[Rails] 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 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_actionsWhat''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 > endIt 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
Toby Catlin
2006-Mar-21 17:59 UTC
[Rails] Re: Loading the contents of a CSV file to a database
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 ruby-forum.com.
Alex Young
2006-Mar-21 18:05 UTC
[Rails] Re: Loading the contents of a CSV file to a database
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 fieldsYou''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
Toby Catlin
2006-Mar-21 20:51 UTC
[Rails] Re: Re: Loading the contents of a CSV file to a database
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 ruby-forum.com.
Toby Catlin
2006-Mar-21 20:55 UTC
[Rails] Re: Re: Loading the contents of a CSV file to a database
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 ruby-forum.com.
Alex Young
2006-Mar-22 09:09 UTC
[Rails] Re: Re: Loading the contents of a CSV file to a database
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
Thibaut Barrère
2006-Mar-22 10:00 UTC
[Rails] Re: Re: Loading the contents of a CSV file to a database
> 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: wrath.rubyonrails.org/pipermail/rails/attachments/20060322/cd6d6b87/attachment.html
Toby Catlin
2006-Mar-22 15:17 UTC
[Rails] Re: Re: Re: Loading the contents of a CSV file to a database
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 ruby-forum.com.