Conversion of Existing CMS; access to SQL

I’d check out http://devot-ee.com/add-ons/datagrab - either in hopes of using it or as a model to build from.

Thanks for the reply. Datagrab looks like it could be a partial solution. As a newbie here I’m a bit concerned that every question seems to be answered by a reference to a add-on that costs $. As a developer (this post is in the development and programming thread) it is not unreasonable to seek some guidance on the best approach to a process for reading and writing tables in my EE database … which it would seem to me would be appropriate for a utility rather than an add-on. Essentially writing my own datagrab, without the general capabilities inherent in that product but more focused on my actual data conversion needs.

Just as a general proposition, if you have an “old-cms” MYSQL table that needs to have certain selected fields converted to an EE MYSQL table, why go through XML and/or CVS when you can just read the records in your old table in a loop, process them as needed, and update the EE MYSQL table with them?

EE stores entries in several tables, so any import process needs to update all of them and make sure entry_id’s are there as a key to hold them together. I mentioned Datagrab because you could look at it’s code to see what those tables all were. If there were a free way to do that I would have mentioned it, but even then $45 is a small price to pay to save time in that regard.

Are you looking at a utility because the need is recurring? Otherwise it’s just a series of queries to run at the MySQL level vs. being in the EE Control Panel. I did this on client site years ago (before Datagrab existed) and just ran some update SQL in PHPMyAdmin to pull content from an imported Excel sheet and split it up into the necessary EE tables.

Overall what I’ve found over the years is that unless the import process involves thousands of records or is a recurring need then ultimately the more cost-effective way to migrate content may be manually. I can hire a temp office worker, do a bit of training and pay for a day of content entry time a whole lot cheaper than spending my time figuring out how to do it manually.

I’m continuing to spelunk around trying to figure out the best approach to this. I found the query module yesterday and thought for a while I might be able to use that, but looking at the code it appears to be limited to a Select type query.

I’d like to run this idea past the experts here. EE templates can run PHP. That’s built in. So how about if we had a data conversion template, or a series of them for different table conversions that essentially did something like this:

<?php 

$results = $this->EE->db->query("SELECT * FROM nuke_users");

if ($results->num_rows() > 0)
{
    foreach($results->result_array() as $row)
    {
        echo $row['username'].' - '.$row['user_email']."
\n";
    }
}
?>

In the above instance, I used PHPmyadmin to go get a copy of my users table from my old CMS in a different database. Imported it into my EE database. With the query above, I can read through the old table. It should not be too difficult to extend this approach to process the records, extract the fields I want for my EE database table and insert them. A field by field mapping would be implicit but once that’s done it appears that I could just run a template to do my conversion for me.

Eventually I have news tables, and calendar tables, and bulletin board tables and even some idiosyncratic module tables (a bicycle ride calendar for instance) to convert so I’d like to nail a viable approach before moving on. And to answer one question from a previous reply: yes, this will be a recurring process. I can see the conversion process extending, elapsed time-wise, for six months or more and the need to run and reload the tables multiple times as I refine the field mappings and learn more about how to do things in EE.