# Loading the contents of a CSV file to a database

**URL:** <https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472>\
**Category:** Rails\
**Created:** [March 21, 2006, 3:04pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472 "2006-03-21T15:04:01Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 3:04pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/1 "2006-03-21T15:04:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 3:30pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/2 "2006-03-21T15:30:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 6:58pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/3 "2006-03-21T18:58:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 9:50pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/4 "2006-03-21T21:50:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 7:05pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/5 "2006-03-21T19:05:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 21, 2006, 9:54pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/6 "2006-03-21T21:54:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 22, 2006, 10:09am UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/7 "2006-03-22T10:09:26Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 22, 2006, 11:00am UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/8 "2006-03-22T11:00:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tobycatlin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tobycatlin](https://www.ruby-forum.com/u/tobycatlin)\
**Post date:** [March 22, 2006, 4:17pm UTC](https://www.ruby-forum.com/t/loading-the-contents-of-a-csv-file-to-a-database/55472/9 "2006-03-22T16:17:39Z")

</div>

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