Exporting existing data into Drupal

How to migrate raw data from database into Drupal

[webtechi2010]

I have a site where I have forms to insert data into database and custom build CMS to display the data in a formatted way. However, I believe that my CMS lacks several features like SEO etc. and after searching a lot in the internet found out that Drupal is the answer.

Now the problem is that I do not want to copy-paste the data from the existing database to Drupal. But I still want to display data using Drupal.

Also, I want to retain my existing URLs. Seen a lot of Drupal 7 videos, but nobody seems to have the know-how of my solution.

Posting here with a lot of hope. Please help.

A bit about myself, I know PERL better than PHP.

[ergophobe]

You have a few options, but you will most likely want to use the Feeds or Migrate modules

[drupal.org...]

[drupal.org...]

That would be to get your content in there. As for the URLs, that would depend on how they are currently stored and how easily you could map them to the new system.

In any case, it's not likely to be a straightforward so keep good incremental backups as you work through issues so you can always back out to a known stable state.

[jamie]

Hi,

I am just doing the exact same thing. The problem is because everyone's needs are different there is no real copy and paste migrate tutorial or code snippet (like there is for almost everything else code wise).

However once you have installed drupal and the migrate module, install the migrate_examples module which has four or five real world examples of data migration. I based mine on the migrate beer example. It's difficult getting your head around the way drupal works, and took me several hours of coding to get the first data migrating (and I have quite a bit of php xp) but if you've rolled your own CMS, you'll get there.

I am a bit knee deep in the migration right now, but I'll try to check back regularly if you have any questions.

Good luck

Ps. Interestingly enough it was ergophobe who recommended drupal for a large site ;)

[ergophobe]

Shame on me ;-)

I hope it's working out for you. But yes, migration can be a real hassle. I not too long ago migrated from a gallery app to Drupal 7 and just had to do a fair bit of coding and manual cleanup.

And not every type of data from one system has a natural migration path to another, so often there has to be custom processing.

[webtechi2010]

Thanks a lot guys. At least now I have a direction. Not sure how far I will go, but seems it would be challenging and will take a lot of time and effort.

[ergophobe]

Just trying to migrate from Drupal 6 to Drupal 7 on a complex site is bad enough!

So yes, pad your time budget substantially. It will take longer than you think.

[jamie]

I hope it's working out for you.

just into the final push now. the content is migrating. we are now working on display. it's a big learning curve, especially after coming from a "let's quickly code that and upload it" system to the module/hook system used by drupal.

but we are looking to future-proof the site and reduce it's dependancy on custom code. bit worried about performance now, but we're going to hide it all behind varnish and hope for the best!

[ergophobe]

>>future-proof the site and reduce it's dependancy on custom code.

This will reap benefits - I am working on a site now where the original developer just took huge chunks of javascript and even PHP and pasted it into blocks and then developed custom modules with no user input filtering. It's a total mess and very hard to maintain.

>>worried about performance now

Of course Varnish (or the Boost module) will help reduce server load, but a lot of modern Drupal sites are very slow on the front end too. A couple of things to look into
- alternate aggregation schemes
- a Javascript loader (LABjs, head.js, control.js, etc).

I just recently made a site feel a lot speedier for someone with those tweaks.

[webtechi2010]

Good to hear jamie that your are in the final stages. I have just started to read the book - The Definitive Guide to Drupal 7, and learning Drupal the Drupal way. For me it's a long way to go. Till then, keeping my fingers crossed ....

[ergophobe]

Any thoughts on those books?

For me, Pro Drupal Development for Drupal 6 was a great reference and framework (even though I had been using Drupal since 4.5, it was with Drupal 6 I started wanting to do things the "right" way). It was a great book, but I've heard mostly bad things about the Drupal 7 version.

FYI - huge changes coming in Drupal 8.
- new theming system using Twig
- codebase massively reworked to run off of the Symfony framework
- in-place editing
- configuration management mostly exportable to code rather than stuck in the DB (this will make life so much easier).

[jamie]

ergophobe, thanks for the labjs and head.js tips, i've not come across those before. will enjoy playing with those over the next few days (the things we do for fun!)

[jamie]

i've just come from 7 years using smarty only to start coding std php again in drupal's templates and now they are moving to twig - i swear i spend half my time learning syntax! ;)

[ergophobe]

I know. The idea is that the template layer should be open to designers and designers can't be trusted to not get online, look up some SQL tutorial and deciding that it will be a good idea to write something like this into their template:

db_query("SELECT `nid` FROM `node` WHERE `nid` LIKE '" . $GET['unsanitized_user_var']);

And... goodbye website!

[Kendo]

I believe that my CMS lacks several features like SEO

It would be a lot easier to add the missing SEO.

Simply work out what you are missing and add it into your existing CMS. When you do have a closer look, you should realise that there is no "magic" and that all you need to do is automatically populate a few tags from the content record of the page.

The one thing that you may miss is page naming instead of using ID numbers, but that's no big deal as the novelty must by now have worn off that ploy.

[explorador]

webtechi2010, I understand your situation, been there and solved it but it's no easy task. I migrated 5 big websites, thousands of pages... into 5 diff drupal sites. I posted a thread around here regarding the images (intros) that became the biggest problem. But yes, it can be done, I built a few scripts on perl to solve it, sorry... won't work in your case because were designed for my own custom built CMS/content...

But basically what I did was:

A. Experimented with Feeds.
You can use the RSS format to export/import. I found that it was not too difficult in my case to make my own CMS export the data into feeds. I found some problems while doing so:

- Long feeds ended up in errors while importing
- Tried shorter feeds, from 100 to 200 pages each
- But... had some trouble with special characters
- Then dates... you know, perl and RSS date format...
- Then I stopped because I couldn't get around importing the intro images (one to appear on the front page or index lists


B. Then I tried CSV
Made my cms export CSV data... then used the same Drupal modules for importing/exporting but again character problems, dates and intro images. Besides I found, in my experience that those modules are kinda buggy. At times will perform well, other times wont. I had too much content to work on so I abandoned the idea.


C. Success... Custom export
1. I created a few pages (articles) with the data structure I use
2. Exported Drupal data to CSV but not using modules, I used phpMyAdmin
3. Analyzed the tables and data
4. Modded my own CMS to export equivalent data in CSV
5. Imported the data into the MYSQL database using phpMyAdmin
6. Didn't touch anything while importing (very important)
7. Then deleted the cache on the database tables
8. Then via Drupal page to do so

Success! even my urls were kept

I only needed to work with the following tables:

Node
URL alias
Node revisions
File managed
Taxonomy Index
Taxonomy term data
Taxonomy term hierarchy
URL alias tags
URL alias
Field revision
Field revision (field image)

The table names might not be exact on that list, I used "preview" as the name of the intro images so the table name changed to preview not image.

Some tables are duplicates "node-node revision". In some cases I got away without the revisions, in other it was needed... so I used node and node revisions both times.

Yes it can be done, just be patient. There is more to say about importing the images (intro or index lists) but I doubt you need it.


Good luck, it's easier than you think.
I only used Perl to to the job.
Moved all the files to my computer to work locally (that's faster)

**My personal problem was with the images for index lists (intros). I solved this too but partially because the images need to be referenced (for access and mod data), so it trowed an error when I tried to edit any page from Drupal, it was easier to delete the image and then edit/replace. But no problem because my pages were final, no edition needed.