How do I update > 1000 invalid records

[grobinson]

I used an email validator service to find invalid email addresses in my CiviCRM database. Problem is I have over 1000.

1. Option 1. Normally you would flag invalid email addreses as ON HOLD, but short of opening all 1000 records, I cannot find a batdch method of marking these records ON HOLD

2. Option 2. I thought of using import to replave the bad email addresses with a nul. But CiviCRM will overwrite an existing value with a NUL value

Help please. I do not want to update over 1000 records by hand!

[totten]

If you you're comfortable with spreadsheet formulas and a tiny bit of SQL:

1. Copy the 1000 email addresses into a new spreadsheet (column A)
2. In column B, use a formula and apply it to all the emails. (example for row 1 below)

Code: [Select]

="update civicrm_email set on_hold = 1 where email = '" & A1 & "';"

3. Observe that column B  should now display, e.g.

Code: [Select]

update civicrm_email set on_hold = 1 where email = 'user@example.org';

4. Copy the text from column B
5. Paste the text into your favorite SQL tool and execute it.

--

If you're comfortable on the Unix command-line, then you might do something like:

1. Copy the 1000 email addresses into a flat text file ("invalid-emails.txt")

2. Run:

Code: [Select]

cat invalid-emails.txt | while read email ; do echo "UPDATE civicrm_email SET on_hold = 1 WHERE email = '$email';" ; done | mysql -u USERNAME -p -h HOSTNAME DBNAME

# (USERNAME, HOSTNAME, DBNAME should be adapted to your particular system; you can usually figure out these values by searching civicrm.settings.php for the "DSN")