Author Topic: Rules on Import Data from CSV (Read 723 times)

JohnFF · September 11, 2013, 02:19:23 pm

How can I add rules to the import csv files to flag and not import any records with mobile phones that aren't "playing nice"?

Background: with winter fast approaching, it's about time we had a good old spring clean!

Does anyone have any data cleaning advice, scripts, or custom modules they'd be willing to share?

Particular focus is on mobile phones. We've got missing leading 0s (when Excel removed them), mobile and landline phone numbers sharing a field, and things like "0208 999 9999 ext 999" "0208 999 9999 or 0208 000 0000 at weekends" "01/01/1980". This doesn't include mobiles down as landlines and vice versa.

However, my main focus is on prevention...

Erik Hommel · September 16, 2013, 05:55:09 am

My advice is to use the CiviCRM API and create your own script so you can clean up the data? That is arguably quicker than trying to do magic with the import? It is what I always fall back on if the source data is not nice.....:-)

totten · September 16, 2013, 12:48:08 pm

You might also try cleaning the spreadsheet before importing.

I don't do much importing and haven't used this personally, but several folks well of "Google Refine" (now called "Open Refine" -- http://openrefine.org/ )

JohnFF · September 17, 2013, 01:25:33 am

To me a large part of this is helping people understand the requirements for our clean data, not just trying to autofix things.

In the same way that if you import a multiple select value, and it finds a value not in the multi select, it flags it as an error and refuses to import it.

I need members of the team to understand that "07700000000 at Weekends, otherwise call his wife on 02099999999" isn't a valid entry for a phone number! Also, something that rejects errant dates of birth that make their way into the phone column.

Are there any callbacks for the verification? Maybe that are used for the multiselect thing.

Parvez · September 23, 2013, 04:52:47 am

Hey John

You could use hook_civicrm_import to do some post import cleaning or notification but this is post import. That way you're not stopping the import but you are notifying users/admins of the invalid numbers being imported.

You could also run a report thats emailed out with invalid numbers in it so that everyone is aware of the problem.

Just my 2cents worth!

Parvez

CiviCRM Community Forums (archive)

News:

Author Topic: Rules on Import Data from CSV (Read 723 times)

JohnFF

Rules on Import Data from CSV

Erik Hommel

Re: Rules on Import Data from CSV

totten

Re: Rules on Import Data from CSV

JohnFF

Re: Rules on Import Data from CSV

Parvez

Re: Rules on Import Data from CSV