Import into MySQL from CSV/Excel, Ruby

December 5th, 2007 posted by codders

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.