Import into MySQL from CSV/Excel, Ruby
You have some data in a CSV file (or a spreadsheet that you’ve dumped to CSV) that you’d like to load into a MySQL database. Nothing very interesting to say about this except that faffing looking up both sets of docs is tedious for what are quite simple bits of code and a fairly occasional task. (If you spend a lot of your time dealing with CSV / Excel files, you’ve probably made some bad decisions in life, but at least you’ll know this by heart 
)
The source file is a list of resistor values - column headings ‘Value’ (read product code), ‘Resistor A’, ‘Resistor B’, ‘Resistor C’. The CSV looks something like:
0,1.1,27,180 7,4.7,22,47 21,2.2,18,4.7
The table looks something like:
CREATE TABLE resistor_configs ( id INT AUTO_INCREMENT, value INT, resistor_a FLOAT, resistor_b FLOAT, resistor_c FLOAT, PRIMARY KEY (id) ) DEFAULT CHARSET=utf8
…and the Ruby runs as follows:
#!/usr/bin/ruby
#
require 'csv'
require 'mysql'
my = Mysql::init()
# You can do any SSL stuff before the real_connect
# args: hostname, username, password, database
my.real_connect("localhost", "root", "", "products_development")
my.query("DELETE FROM resistor_configs")
CSV.open('/tmp/resistors.csv', 'r') do |row|
# No escaping here, because I trust the input file. You may not
my.query("INSERT INTO resistor_configs" +
"(value, resistor_a, resistor_b, resistor_c)" +
"VALUES (#{row[0]}, #{row[1]},” +
“#{row[2]}, #{row[3]})”)
end
There you have it. Nothing very clever, but easier to copy and paste than to read the fine manual.