Import Contact Issue

Question

I am importing CSV files, about 7000 contacts per import, about 20 fields per contact, about 800kb sile size. DB size is about 55k, issue arose at about 20k. Since then, it times out after 2000ish records. The importation ultimately executes with fields and data appearing to correctly function thus far through testing.

Issue is the importation process freezes. It seems to run about 4 to 5 minutes, then apparently times out. Server demonstrates mysql processes, usually about 6-10, in sleep state when "time out" occurs. Checking table in phpmyadmin, I can quickly see where to edit csv file to continue.

I am not deduping, though I am validating address data. I suspect the address validation is causing the issue. Does the USPS address validator have a "counter" on it that is now limiting my "per day/then/per session" usage?

I verified the processor, free memory, file size, etc are all way under utilized.

Rememdy is kill all user/application processes, repair database and continue forward.

But, if I can adjust a setting to enable better processing, without sacrificing security, pls share.

Thx

Answer 1

You have several options, which I detail below - but I think the most important thing to do is check your CiviCRM log. It's likely to tell you what your problem is.

Past that:

One option is to adjust your php.ini settings to change the max_execution_time to zero. You can also do this in civicrm.settings.php if you prefer.

Another is to import your data on the command line using the API CSV import tool - but this requires breaking up your data into separate entities (e.g. Contact, Address, Email, etc.). This is very efficient on large imports but not worth it unless you can script it (e.g. with Pentaho Data Integration).

I agree that it's very likely that either geocoding or USPS address validation is likely a culprit - but the easiest way to test that is to disable, do a test import and check the result.

If you're a programmer and don't like using Pentaho, you could also try writing an importer that uses the advimport extension.