Piping A File In To MySQL

Photo of Greg Harvey
Thu, 2011-12-15 18:14By greg

Our use case here is if you have a lot of data you want to insert in the MySQL - data that you might usually use the Drupal UI for, but there's so much of it you'd rather not if you can help it!

The example here is for a client of ours. For this particular client we have to launch a few new sites a month, each one comes with a new access list of email addresses to add to the Drupal "Access rules" page. There's no bulk upload, and doing these one by one with the UI is laborious, to say the least!

We could write a module, but it's an edge case really and it's pretty simple to deal with straight in MySQL. Enter the MySQL command, LOAD DATA INFILE.

First, your database user needs to be able to load the "infile". To do this, login as MySQL root and execute a command like this (for your user, obviously) to give your site's MySQL use file access permissions:

GRANT FILE ON *.* TO my_db_user@localhost;

Next up we need to prep a file. Don't worry about putting strings in ' marks and all that - MySQL will sort itself out. It knows the data type of the column anyway. My input file looked like this (having copied and pasted a column of email addresses from a provided spreadsheet and used Replace in Gedit for Linux to provide the additional ,mail,1 at the end of each line):

someone@hotmail.com,mail,1
foo@yahoo.com,mail,1
barperson@gmail.com,mail,1
bob@ymail.com,mail,1
etc.

I saved that on the server at ~/access-emails.txt - now to pass it to MySQL:

I used drush to login to MySQL with the drush sql-cli command from within the web root of the site and executed this command:

mysql> LOAD DATA INFILE '/home/greg/access-emails.txt'
INTO TABLE access
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(mask, type, status);

First bit is self-explanatory, as is the second. Then we tell MySQL what character the data is delimited by (I'm not sure on the default, so I explicitly stated a comma) and what a line ending looks like (again, not being sure of the default I explicitly stated a line break). Finally, and this is particularly important if there are auto_increment fields you need to deal with, which columns in MySQL the data in the rows maps to. In this case, the access table looks like this:

mysql> DESCRIBE access;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| aid | int(11) | NO | PRI | NULL | auto_increment |
| mask | varchar(255) | NO | | | |
| type | varchar(255) | NO | | | |
| status | tinyint(4) | NO | | 0 | |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

I don't want to provide the aid column, that's an auto_increment field, so I deliberately omit it from the end of the SQL statement so MySQL knows that data is not provided - so it will leave it NULL if it has no other instruction, or auto increment the ID if it's an auto_increment field.

That's it! Easy import of access data supplied by a client in a spreadsheet. 83 individual form operations done in a bit of pasting and replacing and a single SQL statement. \o/

Here's the MySQL manual page for this, for more options:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html