Fuck. We spent all this time convincing one of the oldest clients with the agency into upgrading their old and kludgy, PHP 3 era, nightmare website into a snazzy ExpressionEngine 2.2 gem of modern technology. But, like the assholes we are, we didn’t quite think through the fact that they have a complicated data structure (having been built when PHP really sucked) that we’d have to import into ExpressionEngine. So, you know, fuck…

But then, after the pants come on and a moment to reflect is had and the obviousness of the overreaction becomes clear. This is ExpressionEngine after all; there’s bound to be some utility available to handle this with the least amount of pain possible. And there is: the Member Import Utility and, even better, it’s included into the core of ExpressionEngine so it’s already there.

And in traditional ExpressionEngine fashion its built in much the same and logical way most would design an import system. There are two different modes to work with: one to import an ExpressionEngine Member XML file and another to create said file from a CSV formatted file.

The CSV format conversion wasn’t really all that for the purposes of this project (mostly due to conditional rewriting of specific column entries) but it does have it’s uses. For instance, with smaller, self contained, exports it should be a snap to easily hand over the export straight from phpMyAdmin and convert it into an ExpressionEngine Member XML file. One big drawback though is that the CSV import doesn’t appear to create new custom member fields on import so manual mapping is required per import.

The flip side of the CSV format convertor is the straight ExpressionEngine Member XML import utility. The big drag with this, and only if you don’t use the CSV convertor, is that a script has to be written to handle the conversion. Not too big a deal but, considering most installations will vary, this is going to require manual intervention every time a new system is imported. But, unlike the CSV convertor, the Member XML importer does actually create missing custom fields so updating the system shouldn’t be too big  a drag.

For my purposes I wrote a dirty little script to create the XML file for me so it was simply a matter of importing. I’m lazy, in the long term, so keeping as much of this as simple as possible is always a priority for me. But, since it’s a throw away thing cleanliness and best practices is less important than getting me more time to do real work. A word of caution though; the XML parser within ExpressionEngine isn’t very helpful when it comes to error messages and telling you what went wrong. You will curse.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
$sql = "SELECT * FROM users";
$result = mysql_query($sql);
if(!$result)
{
	echo mysql_error();
	exit;
}
$ee = "<members>\n";
while($row = mysql_fetch_assoc($result))
{
	$ee .= "\t<member>\n";
	$ee .= "\t\t<username><!." ".$row."]]]]><!." ".$row."]]]]><!."]]]]><!]]]><![CDATA[></".$key.">\n";
	}
	$ee .= "\t</member>\n";
}
 
$ee .= '</members>';
 
echo $ee;
exit;
?>

The above code is fine if your existing database is cool but, and this is weird, ExpressionEngine goes full retard when it encounters anything it doesn’t like in the XML and won’t let you proceed. It’s really quite stunning. The XML importer knows that duplicate email addresses are bad in the database but instead of, say, allowing you to skip or force things the importer will just die and scream about a duplicate leaving you the only option of then manually pruning your data. Why they couldn’t be bothered with a checkbox or something allowing you to skip bad entries is beyond me but it makes the experience really, really, painful if your data sucks.

Still though, if your data is good to begin with the importer is a nice touch.