Loading the contents of a CSV file to a database

Rails
1

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.

2

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.

3

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

4

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

5

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.

6

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

7

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 CAMDE@09:42’)

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

8

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.

9

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