MySQL bulk value insert

Question

I have a bunch of emails, each separated by a comma (,) (not csv). I want to upload them to a database table (with single field email) such that each email goes into separate record entry. what could be the most easiest way to do that? I have an idea of using grep to replace commas with my sql syntax.. but searching for any other workaround.. any idea?

Answer 1

I'd use shell tools like sed or awk to convert the input format to something that mysqlimport can handle.

Answer 2

MySQL supports multiple inserts in a single statment

INSERT INTO [Table] ([col1], [col2], ... [colN] )
VALUES ([value1], [value2], ... [valueN] )
     , ([value1], [value2], ... [valueN] )
     , ([value1], [value2], ... [valueN] )
;

You could pretty quickly format a comma-separated file into this format.

Answer 3

Perhaps something like:

LOAD DATA INFILE '/where/the/file/is'
INTO TABLE table (email)
FIELDS TERMINATED BY ','
LINES STARTING BY '';

Syntax docs here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 4

Convert the current ',' separated email list to a one line per email list

tr ',' '\n' < inputfilename > outputfilename

use load data infile after logging into mysql, make sure your table only has one column in this case

load data infile 'outputfilename' into table tablename;

http://dev.mysql.com/doc/refman/5.1/en/load-data.html