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
Optional If the delimiter exists in your code (the task will fail if it does), you need to change this attribute.
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
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.
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
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
# 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
# File lib/bcp.rb 51 def create_temp 52 rm_safe @tmp 53 mkdir @tmp 54 end
# 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
# 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
# File lib/bcp.rb 103 def path 104 p = @version || "100" 105 sql_tool p 106 end
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