class BradyW::BCP

Supports using Microsoft BCP to load CSV data. Unlike BCP out of the box, this task attempts to “support” comma escaping by converting your CSV files to files with an odd delimiter before loading them in with BCP.

Attributes

delimiter[RW]

Optional If the delimiter exists in your code (the task will fail if it does), you need to change this attribute.

files[RW]

Required Supply the files you wish to load into your tables here. They should be named using the following pattern SEQUENCE-TABLENAME.csv Example:

01-users.csv
02-accounts.csv

OR
001-users.csv
002-accounts.csv
identity_inserts[RW]

Optional If this is set to true, then BCP’s “-E” command line argument will be used. If you have primary keys in your files you wish to preserve, set this to true. Default is false.

version[RW]

Optional By default, this looks for your installed version of BCP with SQL Server 2008. If you’re using SQL Server 2005, set this to “90”

Public Class Methods

new(parameters = :task) click to toggle source
Calls superclass method BradyW::BaseTask::new
   # File lib/bcp.rb
43 def initialize (parameters = :task)
44   super parameters
45   @dbprops = Database.new
46   @config = BradyW::Config.instance.values
47   tmpDir = ENV['TMP'] || '/tmp'
48   @tmp = "#{tmpDir}/bcp"
49 end

Private Instance Methods

connect_string() click to toggle source
    # File lib/bcp.rb
126 def connect_string
127   if @config.db_general_authmode == :winauth
128       "-T -S %s" % [@dbprops.host]
129   else
130       "-U %s -P %s /S%s" % [@dbprops.user,
131                             @dbprops.password,
132                             @dbprops.host]
133   end
134 end
create_temp() click to toggle source
   # File lib/bcp.rb
51 def create_temp
52   rm_safe @tmp
53   mkdir @tmp
54 end
csvtoCustomDelim(oldfile, newfile) click to toggle source
    # File lib/bcp.rb
 84 def csvtoCustomDelim(oldfile, newfile)              
 85     File.open(newfile, "a") do |file|
 86       CSV.foreach(oldfile) do |row|
 87           d = delimiter
 88           row.each { |f| if f.include? d
 89                               puts "Your data contains the crazy delimiter that's currently configured, which is "
 90                               puts "#{d} "
 91                               puts " (the default one) " unless !d
 92                               puts "Pass in the 'delimiter' attribute from your rakefile with a different random value."
 93                               puts "Hopefully then it will not exist in your data and can be used with bcp to import"
 94                               puts "data into the database."
 95                               fail
 96                          end}
 97           newRow = row.join(d)
 98           file.puts newRow
 99       end
100     end
101 end
exectask() click to toggle source
   # File lib/bcp.rb
56 def exectask
57       create_temp
58       puts "Using #{@tmp} as a temp directory"
59 
60       files.each do |csv|
61           currentdir = pwd
62           fileName = File.basename csv
63           csvtoCustomDelim csv, "#{@tmp}/#{fileName}"
64           cd @tmp
65           # need to trim off both the extension and the leading 2 numbers/hyphen
66           sequenceAndTable = File.basename(csv, ".csv")
67           tableName = sequenceAndTable.match(/\d+-(.*)/)[1]
68           args = "\"#{prefix}#{tableName}\" in #{fileName} #{connect_string} -t \"#{delimiter}\" /c #{identity_inserts}-m 1 -F 2"
69 
70           shell "\"#{path}bcp.exe\" #{args}" do |ok,status|
71             if !ok
72               cd currentdir
73               # We want to clean up our temp files if we fail
74               rm_safe @tmp
75               fail "Command failed with status (#{status.exitstatus}):"
76             end
77           end
78 
79           cd currentdir
80       end
81       rm_safe @tmp
82   end
path() click to toggle source
    # File lib/bcp.rb
103 def path
104   p = @version || "100"
105   sql_tool p
106 end
prefix() click to toggle source

BCP doesn’t allow initial catalogs for SQL auth, but does for winauth and we need them since winauth users might use several schemas

    # File lib/bcp.rb
114 def prefix
115     if @config.db_general_authmode == :winauth
116         "%s.dbo." % [@dbprops.name]
117     else
118         ""
119     end
120 end