Friday, April 19, 2013

Inserting data into DB2 from CSV file (using Ruby)

I recently had to import some data into DB2 and I came up with this little program to help me out. I had to search the Internet for a few hints and finally came up with this code that was useful to me. I hope that it will help anyone that needs the same thing. I am pretty sure that this can be accomplished even easier, so if you have a suggestion, please let me know.

require 'rubygems'
require 'mswin32/ibm_db'
require 'csv'

db = true
csv_text = IO.read("input.csv").encode('iso-8859-1','utf-16', :invalid=>:replace, :undef => :replace, :replace => '')
csv_text = csv_text.gsub(/'/,"''")
csv = CSV.parse(csv_text, :headers => true)
statements = []
csv.each do |row|
  map = row.to_hash
  k = map.keys.join(",")
  v = map.values.join("','")
  statements << "insert into sch.tab(#{k}) values('#{v}');".gsub(/''/,'null')
end

if db
  conn = IBM_DB.connect("DRIVER={IBM DB2 ODBC DRIVER};DATABASE=db2;\
                       HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;\
                       UID=user;PWD=password;", "", "")
                       if conn
                         puts "We're connected!"
                         data = ""
                         begin
                           statements.each do |row|
                             data = row
                             run = IBM_DB.prepare(conn,row)
                             IBM_DB.execute(run)
                           end
                           IBM_DB.commit(conn)
                         rescue Exception => msg  
                           puts "Inserting #{data} with error #{msg}"
                           IBM_DB.rollback(conn)
                         end  
                         IBM_DB.close(conn)
                         puts "Done!"
                       else
                         puts "There was an error in the connection: #{IBM_DB.conn_errormsg}"
                       end
else
  puts statements
end 

Before you use this you will need to install appropriate gems.

gem install activerecord
gem install ibm_db

If you are behind the proxy, do not forget to set it: SET HTTP_PROXY=http://user:password@ip-address:port

Thanks