I had to move our WordPress CiviCRM install to a new server with a new domain, and decided that I wanted to do a fresh/clean install on the new server, and then I would import all the contacts from the old server.
In order for this method to be successful, here are some things I that I had to consider:
– Make sure to update both installs to the same version of CiviCRM.
– Make sure to go thru the CUSTOMIZE DATA AND SCREENS options, and you will need to add the items to the new install (such as Custom Fields, Profiles, Activity Types, etc) before you import anything, so that they match exactly with the old server.
– The import feature of CiviCRM only lets you import one type of contact at a time (Individual, Household or Organization), so when you export contacts from the old server you will need to split it into three exports, one for each type of contact. Do not export all types together, as you will not be able to import properly.
EXPORT
– To start the Export process, go to Find Contacts and choose the contact type you want and click SEARCH.
– Then you need to select the box that says ALL XYZ RECORDS. NOTE: For initial testing purposes, you might just want to select two or three contacts instead of all of them, so that you can test them to see if they export correctly, and if they import correctly on the new server. If the export or import does not work as expected, then you can make adjustments until the process it working correctly.
– Then choose EXPORT CONTACTS from the Actions drop down.
– Choose SELECT FIELDS FOR EXPORT, and if its your first time, you will leave the Use Saved Field Mapping blank (unless you have already saved a Field Mapping you want to use), and click CONTINUE.
– Now comes the most work! You will need to use the ADD FIELD button, and add every single field, one at a time. Make sure to use the SAVE FIELDS button often, and name it something like all_fields_organizations, so that if you need to do the export again, you have the field mapping already set up.
– Once you have the field mapping setup, click on DOWNLOAD FILE and you should have a CSV file with all your contacts of this type.
IMPORT
– Now login to your new server, and to start the Import process, look for Import Contacts under the Contacts menu.
– Choose your Contact Type to match what you exported.
– For Duplicate Contacts choose NO DUPLICATE CHECKING (this makes sure everything in your CSV file is imported).
– For Date Format, choose the mm/dd/yyyy option with the 4-digit year.
– If you have already mapped an import, you can use a Saved Field Mapping, or leave it blank if this is your first import.
– CiviCRM will try to match up the Column names with correct fields, but you need to verify if they are correct or not, and adjust them as necessary.
– For example, I set the following Columns as follows:
Addressee = Organization Name
Display Name = Organization Name
Sort Name = Organization Name
Organization Name = Organization Name
External Identifier = DO NOT IMPORT
Contact ID = External Identifier
– NOTE: On the old server, the External Identifier field was not being used. So I am putting the old servers Contact ID into the new servers External Identifier column. This allows me to have a reference for the contact in the old database.
– Save/Update your Field Mapping so you have it for later and click on CONTINUE. This will NOT import anything yet, but will give you a preview of what data will look like, and will also advise you if there are any errors in the CSV file that need to be fixed.
– Click IMPORT NOW when you are ready to import. Again, I recommend you just initially export a couple of contacts and try importing them to make sure the whole process works properly.
OPTIONAL STEP
When you import a contact in CiviCRM, both it’s Created Date and Modified Date get set to the date/time of import.
This last step may not be required for some of you, but for us, it was important the each Contacts CREATED DATE and LAST MODIFIED DATE remained the same as in the old database.
This requires a PHP script to pull those dates from the old database, and then another PHP script to update them in the new database.
In order for these scripts to work, it is important that we somehow associate the Contact ID from the old database with the new contact, and this where I set the Contact ID = External Identifier on the import mapping above.
OLD SERVER SCRIPT (see below)
This script will grab the Contact ID, Created Date and Modified Date of every contact in the database, and write them to a CSV file.
NEW SERVER SCRIPT (see below)
This script will read the CSV file above. It will then look at the Contact ID it read from the file, and see if there is a match in the External Identifier field. If it finds this match, it will then update the Created Date and Modified Date of the contact to match the old server dates.
MINOR ISSUE with BR tag
I noticed that one my custom fields (text/notes) imported line returns as & l t ; br / & g t ; [spaces added so it displays] and then there was extra < br / > tag visible/showing up in the text.
What I did was run an UPDATE with REPLACE on the specific column in that table and replace it with blank BEFORE I ran the NEW SERVER SCRIPT below.
OLD SERVER SCRIPT PHP CODE
// This script will get the Contact ID, Created Date and Modified Date of all contacts and write them to output.csv
$mysqli = new mysqli("localhost","db_user_name","db_password","db_name");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
echo "Connected to DB
";
if ($result = $mysqli -> query("SELECT id,modified_date,created_date FROM civicrm_contact ORDER BY id")) {
echo "Number of rows found: " . $result -> num_rows."
";
if ($result -> num_rows > 0) {
// FILE open
$save_csv = "/path/to/public_html/output.csv"; // CHANGE ME ... Old server location wherever you put this script. Note that you have to move output.csv to the new server after its created.
$csv_file_pointer = fopen($save_csv, "w");
// FILE Write CSV header
$csv_data = "id,modified_date,created_date";
fwrite($csv_file_pointer, $csv_data.PHP_EOL);
while ($row = $result->fetch_array()) {
echo $row['id']." - ".$row['modified_date']." - ".$row['created_date']."
";
$csv_data = $row['id'].",".$row['modified_date'].",".$row['created_date'];
// FILE Write CSV data
fwrite($csv_file_pointer, $csv_data.PHP_EOL);
}
// FILE close
fclose($csv_file_pointer);
}
}
$mysqli -> close();
NEW SERVER SCRIPT PHP CODE
// This script will read output.csv file and then use the Contact ID from old database to look for a match in the new database External Identifer field. If a match is found, it will update the Created Date and Modified Date of the new contact to match the old database.
$mysqli = new mysqli("localhost","db_user","db_password","db_name");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
echo "Connected to DB
";
$file = fopen('/path/to/public_html/output.csv', 'r'); // CHANGE ME ... New server location wherever you put this script. Note that you have to move output.csv from the old server to this new server.
while (($line = fgetcsv($file)) !== FALSE) {
$extern_id = $line[0];
$modified_date = $line[1];
$created_date = $line[2];
if ($result = $mysqli -> query("SELECT id,modified_date,created_date FROM civicrm_contact WHERE external_identifier ='$extern_id' LIMIT 1 ")) {
if ($result -> num_rows > 0) {
//while ($row = $result->fetch_array()) {
$row = $result->fetch_array();
$id = $row['id'];
$old_modified_date = $row['modified_date'];
$old_created_date = $row['created_date'];
echo "FOUND id=$id with ext_id match=$extern_id :: old_mod=$old_modified_date old_created=$old_created_date new_mod=$modified_date new_created=$created_date";
// UPDATE here
if ($result = $mysqli -> query("UPDATE civicrm_contact SET modified_date='$modified_date', created_date='$created_date' WHERE id='$id' ")) {
echo " --UPDATED
";
} else {
echo " --Error: could not update.
";
}
//}
}
}
}
fclose($file);
echo "
DONE
";
$mysqli -> close();
