Microsoft Access MDB to Ruby Hash
June 26th, 2008
Ah, Microsoft Access. If only… wait, it’s Microsoft, it will not happen.
I’m rebuilding an ASP app in Ruby, and for seed data I have an MDB database. Having Worked with Ruby’s win32ole before, I knew I could access all its contents; I was happy to find I didn’t need anything more than a windows Install of Ruby to do it.
require "win32ole"
@file = 'C:\yourfile.mdb'
@table = 'tablename'
heads, results = [], []
conn = WIN32OLE.new(”ADODB.Connection”)
conn["Provider"] = “Microsoft.Jet.OLEDB.4.0″
conn.Open(@file)
cat = WIN32OLE.new(”ADOX.Catalog”)
cat.ActiveConnection = conn
cat.Tables(@table).Columns.each { |col| heads << col.name }
rows = conn.Execute(”select `#{heads.join(”`, `”)}` from #{@table}”).getrows.transpose
conn.Close
rows.each do |row|
hash = {}
heads.each_with_index {|k,i|hash[k] = row[i]}
results << hash
end
results.each do |result|
puts result.inspect()
end
The big issue addressed in this code is the extra work involved of re-associating column names; WIN32OLE.new(”ADOX.Catalog”) is needed for that reason. After getting those column names, we have to make sure they are queried in the same order. Note that ‘rows’ here need a transpose- that is part of Access’s weirdness, giving each column as an array by default.
results.each prints everything to screen; it is an Array of hashes, but would not be hard to convert to a hash of hashes. Well, if your Access programmer followed any sort of conventions, but I digress.
No, wait, I conclude.
-Sud.