[SIMS] SIMS Reporting on Email Address contents

[pete]

Say I have a couple of thousand email addresses in SIMS, entered by people with varying levels of understanding what an RFC-compliant email address looks like.

Is there any way (using built-in SIMS tools or read-access to the database) I can search through them all and flag up any that are broken? Writing my own rules as to "broken" is fine - I just want a way of weeding out commas, "binternet", multiple @ etc without manually* reviewing everything.

(And yes, I have submitted a Feature Request because Capita's idea of "checking email addresses" seems to consist of "does it have an @ and something after the dot?").

[LosOjos]

I don't think there is I'm afraid...

If I were in your shoes (and I soon will be with parent's online reporting), I'd dump the lot out to Excel and write a VBA function with your checking rules in it

[SouthCoastSaint]

If you are thinking of using a comms package there is a quick way. We use school comms which gives us a bounce report for each outgoing email. When we went live I sent a test email to validate addresses. If you back that up with a text, you can also ask parents to check for email and alert you if not received. Worked well with our new year 7 parents this year.

[Sylv3r]

Similar checking to the mobile numbers.

In theory it should be a case of does it have 11 numbers (yes / no)

[creese]

I don't know how effective it would be but using the IF/AND filtering with "contains" should weed out most things.

EDIT: I've just quickly put a report together. It picks up "@@" will pick up binternet. It won't allow a "," to be put in now.

This report would obviously rely on you entering the likely error into the filter.

[rpwillis]

I've just knocked up a quick utility to validate email addresses in a csv file. So if you create a report in Sims containing Admission Number and email address and create a csv file from that it can check the format of the email addresses.

It uses the regular expression ^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$ to do the checking, so it certainly doesn't handle all formats as defined in RFC 2822, but it's good enough for most purposes.

More information and download at at Free Utility to Check Email Addresses.

[pete]

I've just knocked up a quick utility to validate email addresses in a csv file. So if you create a report in Sims containing Admission Number and email address and create a csv file from that it can check the format of the email addresses.

It uses the regular expression ^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$ to do the checking, so it certainly doesn't handle all formats as defined in RFC 2822, but it's good enough for most purposes.

More information and download at at Free Utility to Check Email Addresses.

Er thanks, very much - wasn't expecting that.

I assume it doesn't actually care what's in the first column in the csv? Just that there's an identifier of some type?

(Reason I ask - we attach emails to the contact, rather than the kid - it ensures an update to $contact will update siblings as well)

[pete]

And......it appears CTF files still allow things like "Grandparents 01234 567 890" to get slung in the email fields.

*mutters darkly about primary schools*

[rpwillis]

I assume it doesn't actually care what's in the first column in the csv? Just that there's an identifier of some type?

Correct, it's just for the message that's it's an invalid email address so you can find it in the input file.