CiviCRM Community Forums (archive)

We are a new user of CiviCRM and have a database of about a million records we need to import.   Our database is in mySQL on the same server where we ae running civiCRM. 

Initially, I wrote a program that dumped the DB into csv files that were just a bit smaller than 2Mb and we have proven that those easily import.  Unfortunately, there are almost 300 of them and bringing them in is REALLY boring and very slow.

I ended up writing a little script that would read our database and insert the records directly into the civiCRM tables civicrm_contact, civicrm_address, civicrm_phone  and civicrm_email as well as the tables of custom fields that we added.  The script was written in perl and my original question to the group here was about the field civicrm_contact.hash (specifically, what should be there -- it looked like a random value) 

For whatever it is worth, I ended up using Digest::MD5::md5_hex on a field that held our original file key (and which is known to be unique) and the records seem to work fine in the application.

The import process, done this way, seems to move along pretty well -- about a thousand records per minute.  It won't be finished until morning, but it does seem to be quite a bit faster than our earlier approach with the csv files.

Best,
Nancy Miracle

Thank you very much!   I wasn't quite sure where to put the post and I apologize for having it two places.

That first forum topic (4148) sounds interesting, but I'm not enough of a php coder to understand all the niceties of what is being discussed there.  The idea of copying from another mySQL table to a temporary table in a known standard format and then using a standard processing module seems to be very reasonable (if I understand what is being said there)   

I'm not sure I understand 'the new system will apply transformations to the entire table at once whenever possible'  Is this (for instance) the sort of case where if the source database was using char(1) fields with "Y" or "N" and the equivalent field in CiviCRM is Boolean, to use the SQL statements such as "UPDATE database.tablename SET targetField="1" where targetField="Y"' or something like that?

Again, thanks very much for the help.
Nancy

Nevermind Nancy,

I found your comments in the linked thread above...  Thank you for all the detail you've provided.

the column (hash and external identifier) in the civicrm_contact table have a unique contraint against it. So the DB takes care of this for us

Most of the code does check that no duplicate exists before inserting a record

lobo