Two scripts to use Ruby Hashes for working with CSV data and inserting results into a MySQL DB. For my purposes I didn’t want all my data from the CSV in a database, else I could have just imported a CSV using terminal or phpmyadmin, etc.
Background: For a project, I get a bunch of CSVs exported from Excel. The CSV’s are not complete, so I have to do a lot of scripting, searching between them to get all the data I need. Having done this project several times, it gets confusing with lots of tweaking the scripts and CSVs and far too many files. So I figured it’d probably be easier to get the data into a database and have one copy of the final good data there. This is a no brainer for many, but there aren’t really guidelines for the project and so it took me a while to come to this conclusion.
E.g. 1 – Using Ruby’s MySQL Functions
Sidebar: There aren’t a lot of blog posts or tutorials about how to just code a site or app using ruby and a database. Partly because most people in the ruby community work with Rails or another framework and use ORMs and non-relational databases and all sorts of abstraction. I read a cool article which I agreed with that said “No true mod_ruby is hurting Ruby’s credibility”. I think the author’s point is it’s difficult for people who want to get working with Ruby to do so. Specifically, deploying ruby applications can be quite difficult. This is in contrast to say, working with PHP, which for simple apps is all you really need get up and running. Drop your .php files on most servers and things just work.
I often find myself working with a PHP web app and thinking, “I’d love to write ruby code instead of PHP, but I don’t necessarily want to use a rails app.” So I’m starting to do quick and dirty apps with Sinatra and the Ruby MySQL library. Using Sinatra seems the closest to a mod_ruby type option. I haven’t deployed a Sinatra app yet, but soon though. A lot of my Ruby-ing is on my local machine just for automating tasks and thus does not need to be on the web.
You need the ruby mysql driver, which you probably already have (“gem list” in terminal). If you don’t, install on an intel Mac running Leopard by typing this in the mac terminal:
ARCHFLAGS="-arch i386" sudo gem install mysql -- --with-mysql-config=/path/to/your/mysql_config
If you’re on a Mac with powerpc or on windows, you can find details here in the section for your system: BuildingWebApps.com. Then you need a script like the following:
require 'rubygems'
require 'mysql'
require 'csv'
db = Mysql.new('localhost','DB_user_name','db_password','db_name')
inv = CSV.parse(File.open("inv.csv", "rb").read.gsub("\r", "\n").strip)
inv_headers = inv.shift.map { |i| i.to_s}
inv_strings = inv.map { |row | row.map { |cell| cell.to_s }}
inv_hashes = inv_strings.map { |row| Hash[*inv_headers.zip(row).flatten]}
inv_hashes.each do |r|
val1 = db.escape_string(r['hash_key_name'].to_s)
val2 = db.escape_string(r['hash_key_name'].to_s)
val3 = db.escape_string(r['hash_key_name'].to_s)
val4 = db.escape_string(r['hash_key_name'].to_s)
db.query("INSERT INTO your_table_name (
val1,
val2,
val3,
val4
)
VALUES (
'#{val1}',
'#{val2}',
'#{val3}',
'#{val4}'
)"
)
end
db.close
E.g. 2 – Using ActiveRecord
ActiveRecord is a ORM that lets you use objects to read/write to your database. I believe ActiveRecord requires your table names to be plural. So you can access a table named “Posts” using the singular, like so:
class Post < ActiveRecord::Base end
require 'rubygems'
require 'active_record'
require 'csv'
ActiveRecord::Base.establish_connection(
:adapter => 'mysql',
:host => 'localhost',
:username => 'db_user_name',
:password => 'db_password',
:database => 'db_name'
)
class Record < ActiveRecord::Base
end
inv = CSV.parse(File.open("inv.csv", "rb").read.gsub("\r", "\n").strip)
inv_headers = inv.shift.map { |i| i.to_s}
inv_strings = inv.map { |row | row.map { |cell| cell.to_s }}
inv_hashes = inv_strings.map { |row| Hash[*inv_headers.zip(row).flatten]}
inv_hashes.each do |r|
val1 = r['key1']
val2 = r['key2']
val3 = r['key3']
val4 = r['key4']
@rec = Record.new(:val1 => key1, :val2 => key2,:val3 => key3, :val4 => key4)
@rec.save
end
