Hi,

I have a mysql database whose contents I want to put in a Drupal website. So far I have been able to import quite a few of these mysql databases by simply converting them to csv files then using the Node Import module, which worked fabulously.

The databases I was able to import this way were relatively simple, but now I have several complex ones that have articles and such and therefore do not lend themselves to converting easily to csv. Every time I try I get the final column in my table being transported to a whole new row and split up horribly, my database gets massacred. I did manage at some point to do the conversion to csv but when I imported into Drupal the column with detailed info had almost no punctuation left, the separate lines of content ran into each other, and such.

Is there an automatic way to do this, to take any mysql database and import it into Drupal without losing your formatting?

thanks

Comments

arhak’s picture

I haven't try this insanity, but it's a starting point for research.
Get the demo module (http://drupal.org/project/demo) install a fresh Drupal with that module and create a snapshot (provided by the demo module) and try tweaking the snapshot to work with your row database, then revert to that snapshot.
Take into account that the same modules must be available in the fresh Drupal plus the demo module (also, once the snapshot get restored the demo module will be disabled because it's nonexistence in your raw database)
Might this work or am I talking nonsenses?

bananamuffin’s picture

My main concern is how to get the mysql data imported into the drupal content databases. I tried looking at the database structures after creating some cck types and importing data using the node import module. I was hoping to find a simple way to rewrite the import statements in my mysql file so they could import correctly into Drupal and show up correctly in my content types but alas, I am not that good at mysql and I got hopelessly lost in the database tables.

Not sure how the demo module would help me here with actually importing the data. Can you elaborate?

http://progblogs.com - A directory of programmers and for programmers

dnewkerk’s picture

Not sure when I'll have time (a lot of work), but within a few days I'll share the import SQL I wrote for importing one of my sites (I did a lot of "rewriting" data as well using SQL during the process to bring it into line for Drupal's needs). It has notes on the full process that should inform you about what tables/fields do what. It mostly involves only creating the "content" (not categorization, menus, etc), but that's a start and maybe all you need (or maybe not). I might be able to post it in full/unedited on Tuesday, though I have to skim through it first to make sure I didn't write any passwords or things like that in it.

-- David
absolutecross.com

bananamuffin’s picture

Thanks! Sounds like what I need. I'll be waiting.

http://progblogs.com - A directory of programmers and for programmers

Andy Galaxy’s picture

How did it go? I'd be keen to use this.

bananamuffin’s picture

David must have become busy, I haven't heard back from him.

In the meantime I'v been using a rather painful method but it works.

I create the content type I want and its custom fields using CCK, to correspond with the data in my SQL file
I then, using phpmyadmin, import my sql file into Excel and save as TAB delimited file
I then import using node import module

It's working fine for me except that large paragraphs of information lose their formatting.

I used this method to import several huge database, in chunks, to create http://allmedications.info

http://progblogs.com - A directory of programmers and for programmers

drawk’s picture

Instead of trying to get the SQL spot on (which can be particularly complex when dealing with CCK), try programatically creating the nodes. Load your data via SQL, create an empty node object, populate it and use node_save to save it. The correct db tables will be populated through node_save.

bananamuffin’s picture

I am not a drupal expert yet, so forgive me if my questions sound simple

How do I load my data via SQL, into what? Also, what exactly is node_save? Is it a module?

thanks

http://progblogs.com - A directory of programmers and for programmers

jeffa’s picture

The idea is to create nodes by using an API, rather than trying to shove your data directly into Drupal's database schema.

One huge advantage to using this method is that any hooks you have set up will be activated. If you just slam data into the schema, you will not leverage these hooks. This all depends on how complex your node set up is, naturally.

Look into node_submit() and node_save()

I myself have yet to cross this bridge in Drupal, but I have worked with the Krang CMS and this is exactly how we were able to import very complex data into our new web site.

drupalize’s picture

I am researching how to import my data
not to clear on the method described using node_save.

Robert

dnewkerk’s picture

Sorry for the delay. I did indeed get busy and then lost track of this thread. I went ahead today and posted a lengthy text file containing the custom SQL queries I used to convert a custom CMS's database to Drupal:
SQL queries to import from a custom CMS into Drupal

These of course are highly custom for my own site and won't apply as-is to anything else... however they may be helpful in assisting you in writing your own queries.

Hope it helps.

-- David
absolutecross.com
[new guide/lesson in progress: Creating a CCK and Views powered Drupal site - feedback welcome]

ronliskey’s picture

following

glass.dimly’s picture

After a database migration, sometimes your nodes don't show up, even after clearing the cache. If you've imported the right tables, namely, node_revisions, and node, you can loop through your nodes, load them, then save them. This allows them to be registered with your site's modules.

My code snippet to do this is here:

http://drupal.org/node/559364

skitten’s picture

Have you tried the Table Wizard and Migrate modules? I think they would get you a lot of the way there.

glass.dimly’s picture

There is a related handbook page which I created about some of the problems involved in the migration of raw sql tables, etc, which may be of use.

http://drupal.org/node/570906