Problems with simple import of csv into a MySQL database (load data infile doesn't work)

Question

Relatively new to Ruby, running: Ruby 1.9.2 and MySQL 5.5.19

I have a csv file full of data that I'd like to add to an existing table on a mysql database. The csv file does not have headers. Yes, I'm aware that there are multiple questions on this topic already. Here's how it breaks down:

Answer #1: Use LOAD DATA INFILE Unfortunately, LOAD DATA INFILE gives me the following error: "Can't get stat of 'filename.csv' (Errcode: 2)" This appears to be some kind of permissions issue. I've tried this both directly at the mysql prompt (as root), and through a Ruby script. I've tried various chmod options on the csv file, as well as moving the csv file to various directories where other users have said it works for them. No luck. Regardless, most people at this point recommend...

Answer #2: Use LOAD DATA local INFILE Unfortunately this also returns an error. Apparently local infile is a mysql option turned off by default, because its a security risk. I've tried turning it on, but still get nothing but errors, such as: ERROR 1148 (42000): The used command is not allowed with this MySQL version and also undefined method `execute' for # (NoMethodError)

Answer #3: I can find various answers involving Rails, which don't fit the situation. This isn't for a web application (although a web app might access it later), I'm just trying to add the data to the database for right now as a one-time thing to do some data analysis.

The Ruby file should be incredibly simple:

require 'rubygems'
require 'csv' (or fastercsv?)
require 'mysql'

db = mysql.connect('localhost','root','','databasename')

CSV.foreach('filename.csv') do |row|
   ?????
   db.execute("INSERT INTO tablename ?????")
end

P.S. Much thanks in advance. Please no answers that point to using LOAD DATA INFILE or LOAD DATA LOCAL INFILE. Already wasted enough hours trying to get that to work...

Well, try harder. load data infile is the fastest (and probably most appropriate) way to import CSV file. — Sergio Tulentsev
– Sergio Tulentsev, Commented May 7, 2012 at 9:02
That's a first for stackoverflow that I've ever seen. "Try harder?". If you have any specific recommendations about how to fix the problem I'd be glad to hear them. — user1377556
– user1377556, Commented May 7, 2012 at 17:44

Tom Meinlschmidt · Accepted Answer · 2012-05-07 23:08:59Z

2

ad mysql:

LOAD DATA INFILE '/complete/path/csvdata.csv' INTO TABLE mytable(column1,column2,...);

ad ruby

require 'csv'
require 'mysql'

db = mysql.real_connect('localhost','root','password','database')

file=CSV::Reader.parse('filename.csv')
file.each do |row|
  values = row.inject([]){|k,v| k<<"'#{v}'";k}.join(',')
  db.query("insert into table(column, column ...) values(#{values})")
end

db.close

it assumes csv file contains ALL the columns required..

answered May 7, 2012 at 23:08

Tom Meinlschmidt

2071 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problems with simple import of csv into a MySQL database (load data infile doesn't work)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related