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.

Toby C. 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.

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

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 [email protected]: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

Toby C. wrote:

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.

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

Toby C. 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 [email protected]:42’)

Without wishing to pry, what are the first two lines of the CSV?

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 [email protected]: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.

That looks like it might do the trick. GOing to have to wait til after
work to try it out :frowning:
must get instant rails on a thumb drive or something

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs