I am using a web-site digital download program (http://reamdaysoft.com/magic-downloads/overview.html), which collects users email addresses. Users have the option of subscribing to a mailing list by ticking a check-box, the program returns the data as shown below (I don't know what the empty column is for):

"e-mail","Subscribe_to_Newsletter ",""
"abc@myway.com","on",""
"xyz@hotmail.com","off",""

Problem is, in the list of email addresses collected, the subscribed email addresses are mixed with the un-subscribed ones. I needed to get the subscribed email addresses into a separate list.

I created a MySQL table called mail, with 3 columns (e-mail, Subscribe_to_Newsletter, blank) & tried to insert the email addresses. There is no line break between the 2 records, so MySQL reads the insert as having 6 columns. I did this to get it to work:

INSERT INTO mail( email, Subscribe_to_Newsletter,blank)
VALUES (
"abc@myway.com", "on","" ),
("xyz@hotmail.com", "off", ""
);

However as I am expecting to capture, hundreds maybe thousands of email addresses, I can't edit each one. I would like to be able to manipulate the lines of text based on conditions, ie: if a line contains the word "off", then delete or move to another location. I am not a programmer but I don't think this is a difficult thing to do.

I have tried various text editors but can't seem to find one that will do this, any suggestions? or is there a way to load the data into MySQL?.  

Aha!, yes there is!

Have a look at the MySQL LOAD DATA INFILE syntax..

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

This should let you take a CSV file (or whatever) and map the fields to fields in a supplied table.

Let us know how you get on..

B

I ran this query in phpMyAdmin (I don't have shell access):

LOAD DATA INFILE 'info.csv'
INTO TABLE mail;

[nfo.csv (located in the httpdocs directory on the server)]
"e-mail","Subscribe_to_Newsletter ",""
"abc@myway.com","on",""
"xyz@hotmail.com","off",""

& got this error message:

"Error

SQL-query:

LOAD DATA INFILE 'info.csv' INTO TABLE mail

MySQL said: Documentation
#1045 - Access denied for user 'mail'@'localhost' (using password: YES)"

It looks like you don't have File_priv for the account that you're using to log in to mysql.  

Easiest solution may be to obtain that priviledge (or log in through another user account that already has it if you happen to have multiple accounts set up for different operations.   If that's not going to be practical, you could write a script to generate SQL commands like the INSERT you quoted from the data file, then source those.

I solved the problem by loading the file into OpenOffice.org's Spreadsheet & using the Data Filter.

Thank's for the help.  

---