Bayer_mysql_loader.rb

#! /usr/bin/ruby

require 'logger'
require 'fileutils'
require 'net/ftp'
require 'rubygems'
require 'mysql2'

# logging setup
log = Logger.new("/tmp/sql_load_log.log", 10, 1024000)
log.level = Logger::INFO

# paths
export_work_path ='/Users/pierce/Documents/MarketingScienceDepartment/clients/Numeric/Bayer/SAINTresults_exports/'
classifications_work_path = '/Users/pierce/Documents/MarketingScienceDepartment/clients/Numeric/Bayer/SAINTclassifications_exports/'
export_archive_path = '/Users/pierce/Documents/MarketingScienceDepartment/clients/Numeric/Bayer/SAINTresults_exports/archive'
classifications_archive_path = '/Users/pierce/Documents/MarketingScienceDepartment/clients/Numeric/Bayer/SAINTclassifications_exports/archive'
export_ftp_path = '/domains/marketingscience.co/bayer/saintReceive'
classification_ftp_path = '/domains/marketingscience.co/bayer/saintReceive'

#client = Mysql2::Client.new(:host => "127.0.0.1", :username => "root", :password => "", :database => "bayer")


#begin
#    Dir["#{export_work_path}/*.csv"].each do |file|

#        results = client.query("LOAD DATA LOCAL INFILE '#{export_work_path}#{file}' INTO TABLE bayer.saint_export_results FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 3 LINES (tracking_cd, page_views, visits, click_throughs, registration_complete, print_coupon, article_view, visitors) SET created_at = curdate(), filename = '#{file}';")
#        puts "#{file}: #{results.count}"
#        puts "Loaded file #{file}"
#        log.info("Loaded file #{file}")
#        FileUtils.remove(file)
#        puts "Deleted file #{file}"
#        log.info("Deleted file #{file}")

#    end        
#end

# export
begin
    Dir["#{export_work_path}/*.csv"].each do |file|   # Dir.glob("#{export_work_path}"'*.zip') do |file|  
      puts "test"
      query = ["mysql -u root -e ",'"',"LOAD DATA LOCAL INFILE '",file,"'  INTO TABLE bayer.saint_export_results FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\\"' LINES TERMINATED BY '\\n' IGNORE 3 LINES (tracking_cd, page_views, visits, click_throughs, registration_complete, print_coupon, article_view, visitors) SET created_at = curdate(), filename = '", file,"';\""].join
      puts "#{query}"
      system query
      puts "Loaded file #{file}"
      log.info("Loaded file #{file}")
#      FileUtils.remove(file)
#      puts "Deleted file #{file}"
#      log.info("Deleted file #{file}")
    end
end