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?
Add a comment |
4 Answers
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
Comments
I'd use shell tools like sed or awk to convert the input format to something that mysqlimport can handle.
Comments
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;
Comments
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.
