CiviCRM Community Forums (archive)

Dear Auntie Xav - I am having a problem with my boyfriend

Actually the problem is that when I import the data with my boyfriend in it, what I want to do is to 'update' his data if a match is found, but not update it if no match is found, ie the reverse of what the system currently allows.

We currently have 'if match found then skip/update etc' what we don't have is 'if no match found then skip/update etc'

Thank you, I feel so much better now I have that off my chest.

Another one: "dynamic dedupe rules".

Actually just a pop-up after screen1 that says 'have you checked that your default Strict Matching Rule is the one you want to use for this import' would save a few brain cells - especially the ones that end up splattered on the wall!

Yep - we had that last one on the Make-it-Happen list looking for a kick-start cash injection offer

As a slightly dirty but instantly available working short term fix to a lot of the Excel related issues, the macro I put up at http://forum.civicrm.org/index.php/topic,16442.0.html now prevents many of these problems:

• It properly saves non-fubar-ed UTF-8 characters, including Arabic, Chinese etc as well as common issues like Polish accents
• I don't know what the 'dreaded "if you have an empty field in the last column" ' is, but as a random by-product of the way it's set up it creates a uniformly blank last column that you'll always not import, which I believe would avoid this.
• It forces "," regardless of locale (although I agree that this is no substitute to solving this one Civi-side)
• It fixes that other problem of Excel-exported names that begin with certain accented characters being skipped
• It solves peterd's boyfriend problems - except the one about who does the washing up. You're on your own for that one.

One 'pain in the ...' of my own: why is there no 'upload' box on the "Congratulations, you have 436 errors" page? I'm guessing the expected use is that when people hit errors, they run import anyway, take the error.csv, fix it, cull the first two columns, save it, then import it to complete the set. This makes some sense, but only if we assume that people are confident hitting 'proceed' when a slow, delicate process they don't really understand tells them there's an error, and if we also assume people are as comfortable working with an ugly, zero-formatting machine generated .csv file as they are with a friendly, pretty spreadsheet they'd been working with for a few days already. These assumptions are probably true of most techies, but it seems most people feel more confident doing one perfect, error free import, in which case manually going right back to the start and re-doing all the config like de-dupe rules etc is infuriating.

It'd be great to see an upload allowed on the error/confirmation screen when there's errors which proceeds using the existing configuration. Maybe a wise piece of validation to add would be a check that there's still the same number of columns as before, to reduce the chances of the CSV ending up misaligned with the existing mapping if someone decides to delete a problematic column.

Two more very specific gripes where fine-looking data gives frequent unexpected errors:

• If 'Website' already checks for 'http://' at the start, could it not add in "http://" where a field begins 'www.' and is therefore clearly a website?
• "Tanzania, United Republic of"? Surely there wouldn't be riots on the streets of Dar Es Salaam if Civi accepted just "Tanzania"? Likewise surely Macedonia should be either "Macedonia", or a diplomatic, Greece-appeasing "Macedonia, FYR" - I'm not sure who benefits from forcing "Macedonia, Republic of"

Finally here's an 'If only' suggestion: what would be amazing to see would be a friendly AJAX powered import process which replaces the unnerving mystery voodoo of the 'Importing ... 0%' box, and also replaces the 500-error baiting of "Create resource intensive SQL queries unique to imports, and hope the server doesn't crack under the weight" with a line-by-line AJAX API-based process that takes the next line of CSV, validates it, imports what it can using standard API calls, prints a row to a HTML table on the screen including any errors in the appropriate cell, shrinks the last-but-2 row if there's no errors (making errors easy to spot while skimming over the results and keeps the process accountable and easy to follow if someone does want to keep an eye on how a particular import is going), and repeats. You could even print error cells as JQuery input fields where value= the value that failed and which do an onBlur API save of that record to the appropriate record - this wouldn't be hard and would make fixing errors that slipped through the first error check really simple.

End of rant. hope the long 3-part post isn't too unsightly, this had been bugging me a lot lately!

[...]common issues like Polish accent[...]

Not that I disagree with you, but Piotr and Michal might object with your statement

Beside that, loads of good ideas.

And your explanation made me realise why the error report is in csv instead of html. I never used it, and always fix the original document comparing with the awfully formatted error csv report , go back to the start and run through the wizard from hell one more time. (I might have hinted already here and there I'm not very fond of wizard

As for your api idea. Didn't someone work on that already ? Or planned to ? Would be a neat solution indeed, with a slight concern about transactions/rollback (not sure there isn't that issue anyway)

X+

I've noticed some allusions from you about building custom imports using the APIs a few times on the forums - got any spare URLs handy? I've found plenty of relevant looking reading but it's hard to see how the various pieces of work and ideas from here and there fit together and what actually became of them. (this might be a common problem!)

Or are the contact add / update functions robust enough to simply trust out of the box? I know they've got a dupe check built in and I gather from what I've seen that validation would to be coded in again on the side... (I miss working in MVC!)

Yes, civicrm_contact_create and friends is what I use. I tend to not trust the dedupe, but do it manually (eg. do a civicrm_contact_get and based on the result update or create a new contact, so I have a full control of what I test as duplicates, that tend to be specific).

The big benefit is that it's trivial to re-run an import run (that's php-cli scripts). Not quite the same feeling with the import UI.

In what sense do you mean transactions/rollback - do you mean in terms of making imports reversible, keeping appropriate records of each transaction?

When I do import a file and if it fails in the middle, I'd rather have nothing imported than having to guess where it stops. This being said, if the message is clear enough probably not a big deal

At some point in the near future I'd be up for contributing some time to help make something like this happen, especially on the ui design side of things (not least because I don't want to be held responsible for any import wizard induced high blood pressure or hair loss on the part of the staff I hand over to!)

That'd be great. Do you know what part(s) of the long list of ideas you'd want to give a go ?
If I may by picking up the ideas, what strikes me as making the most improvements:

- being able to see directly the errors as html instead of having to download a csv
- being able to re-upload the corrected csv in that step
- being able to "continue" on errors (eg. thank you for notify me that the country in line 14 is not a valid one, but please import her (first name, email and what else there is) anyway without the country, I'll correct manually in civi instead of skipping it (an option about what mode I want would be great)

Have you tried phpexcel ? If it works good enough, that would be search a relief to stop giving excel the opportunity to fubar the file.

X+

all these model-level database-facing actions duplicated between multiple pages, some of which are tied to particular views, leaves me thoroughly confused!).

can you elaborate a bit more on this with specific example. We can and should clean up the code base when folks point us to redudant / bad code etc

lobo