importing data into CCK created node

daniel sanchez - April 2, 2007 - 02:25

I have a website with TV repair tips which i recently converted to Drupal. I love Drupal by the way. Anyway, the old tips database looked like this: http://www.tvrepairtips.org/test/oldtips.png
The Drupal created tables look like this:
http://www.tvrepairtips.org/test/node.png
http://www.tvrepairtips.org/test/node_tip.png

I have over 30,000 tips in the old database and am looking for the best way to import them.

If i wrote SQL code like this:
INSERT INTO `node` (`nid`, `vid`, `type`, `title`, `uid`, `status`, `created`, `changed`, `comment`, `promote`, `moderate`, `sticky`) VALUES
(180,180,'tip','zenith',0,1,1175287736,1175287736,2,1,0,0),
(181,181,'tip','Toshiba',0,1,1175295107,1175295107,2,1,0,0),
(182,182,'tip','Mitsubishi',0,1,1175295562,1175295562,2,1,0,0) and so on....

INSERT INTO `node_tip` (`vid`, `nid`, `field_solution_value`, `field_model_andor_chassis_value`) VALUES
(180,180,'dead or intermittent dead','20a43'),
(181,181,'First customer said picture was small, Horiz. and Vert...Then went dead','9P5511C101'),
(182,182,'Dead','MS3250C121'),(28,28,'Dead, continuious blink','TP55F81') and so on...

would this work? are there other tables that need to be updated?
seems like a slow ass way to do it but i'm a newb to PHP, MySQL and Drupal.

» Login or register to post comments

most likely: it would not

jari - April 2, 2007 - 05:09

most likely: it would not work.

Drupal adds all kinds of stuff to various tables, depending on the modules installed. I wouldn't do it straight to the database with SQL, but rather make a PHP script like discussed here: http://drupal.org/node/67887

You can do it directly with

rjl - April 2, 2007 - 06:37

You can do it directly with SQL

Tables

node: you've already noted that
node_revisions: this primarily stores body and teaser fields (which you are not using) and allows for revisions, you should have a record in this table for each node
node_comment_statistics: stores info on comment stats for each node. Even if you have comments turned off, you need a record in this table for each node
node_tip: appears to be a table with CCK fields, upgrade your CCK module, this table will be re-named to "content_type_tip"
sequences: you need to update 2 rows. each node has one "nid" and minimally one "vid", the next "nid" and "vid" values are store in this table, once you know what values you will be using, update the two rows with the next number, the rows have name values of "node_nid" and "node_revisions_vid"

I usually dump my data in MS Excel and then create my INSERT statements

First I get my data into an MS Excel spreadsheet and add two columns for "nid" and "vid"

I find out my next nid and vid values from the "sequences" table, and update the "sequences" table based on how many new rows I will need.
I use Edit->Fill->Series to fill in the columns incrementing by 1

I export 1 row of data from my database to get the querystring I need

So for the "node" table I get

-- 
-- Table structure for table `node`
-- 

CREATE TABLE `node` (
  `nid` int(10) unsigned NOT NULL auto_increment,
  `vid` int(10) unsigned NOT NULL default '0',
  `type` varchar(32) NOT NULL default '',
  `title` varchar(128) NOT NULL default '',
  `uid` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '1',
  `created` int(11) NOT NULL default '0',
  `changed` int(11) NOT NULL default '0',
  `comment` int(11) NOT NULL default '0',
  `promote` int(11) NOT NULL default '0',
  `moderate` int(11) NOT NULL default '0',
  `sticky` int(11) NOT NULL default '0',
  
  *** I REMOVED SOME STUFF HERE ***

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3643 ;

-- 
-- Dumping data for table `node`
-- 

INSERT INTO `node` VALUES (1, 1, 'page', 'My Page Title', 1, 1, 1168116129, 1170524637, 0, 0, 0, 0);

Then in MS Excel I create something like the following to create my querystring

="INSERT INTO node VALUES (" & A2 & ", " & B2 & ", 'tip', '" & C2 & "', 1, 1, 1168116129, 1168116129, 0, 0, 0, 0);"

Column A is my "nid" value
Column B is my "vid" value
Column C is my "title" value
etc.

I copy the output of the column in a text editor, remove the first row, save the file, and import into my database.

Do this for each of the tables mentioned.

The biggest issue is apostrophes. If you have a text editor that highlights SQL code they can be pretty easy to find. Also you will get an error and it will tell you which line was bad, easy enough to remove the lines above and fix the line with the error and then re-import it. Also don't forget if you aren't successful, you can always delete it.

DELETE FROM [table] WHERE nid BETWEEN [x] AND [y]

If you find you are doing a lot of extra stuff, you need to sometimes empty the "cache" table

Hope that is helpful

Afterthought

rjl - April 2, 2007 - 06:43

If you are going to attempt the above, I would also recommend doing a test of maybe just the first 5 rows of your data imported into each of the appropriate drupal tables. Then go to your site and view those nodes to see if it worked.

I'm gonna try it with a test

daniel sanchez - April 3, 2007 - 00:51

I'm gonna try it with a test site first.
I'm gonna back everything up and set up the site as if had just crashed and lost everything.
then i'm gonna import with SQL using Excel to create the INSERT statements.
It'll have to be this weekend.
I'll post (hopefully happy) results.

any other info would be greatly appreciated.

Remember to truncate cache_content table - at least in 5.1

Campsoupster - April 4, 2007 - 01:53

Just spend a bunch of time pulling my hair out trying to upload cck data by hand.

This is really worth it's own handbook page, btw, if someone with the permissions had the time to create and aggregate it.

So, yeah, if you don't clear cache_content newly uploaded CCK field content might not show. I tried clearing the cache table, but that didn't solve the problem. Not sure if this is new to 5.1 or what.

Go for it

Michelle - April 4, 2007 - 01:58

Anyone can create a handbook page. Have at it :)

Michelle

--------------------------------------
My site: http://shellmultimedia.com

I've created a few handbook

rjl - April 4, 2007 - 11:04

I've created a few handbook pages and would be happy to write it up. I'm never sure when something is a good enough idea for a handbook page, but with your blessing, I'm happy to do it.

Don't need my blessing :)

Michelle - April 4, 2007 - 12:59

If you know how to do something Drupal related that someone could benefit from and are willing to write it up, go for it. I've never heard anyone complain, "Jeez, I wish this wasn't documented." LOL

Michelle

--------------------------------------
My site: http://shellmultimedia.com

Handbook page created

rjl - April 4, 2007 - 15:16

Handbook page created at How-to: Import data into CCK nodes (5.x)

While roughly the same conceptually, it's a lot more detailed than the comment I posted above

How-to: Import data into CCK nodes (5.x)

coupet - April 4, 2007 - 20:25

great info, well described.

Suggest include how to assign imported nodes to specific existing taxonomy terms.

----
Darly

Page not found

KG2 - November 8, 2007 - 11:20

Did you ever solve this? I followed the How to

truncated cache and cache_ content
CCK data is in the db, but returns Page not found
I am pulling my hair out
Do you know what I should do now?

404, page not found

emmajane - May 28, 2007 - 22:20

I'm trying to move an old, custom module out of a 4.6 drupal install and into a 5.1 drupal install. I've pulled the data out of the old database ("node" and "pattern" tables) and have recreated them according to these instructions. I've also pulled the node_comment_statistics from the old site and have updated the sequence table. However, when I try to access the new pages (they appear in the list of content at /admin/content/node) I am greeted with a 404 if I try to "edit" or "view" the page.

I've looked at the diff file of what's in the database before and after I create a new "pattern" entry using the GUI/Web site... and I can't find another table that I ought to be updating...but I'm a bit stumped as to where to look next.

suggestions would be very, VERY appreciated.

------
GiorgosK
Geoland Web development/design part of the world experts network

Internal Server Error

geminibiz - September 23, 2007 - 13:25

Hi!

Unfortunately your links to screens don't work.

The Drupal created tables look like this:
http://www.tvrepairtips.org/test/node.png - Internal Server Error

------------------------
Anton A. Parhomenko
IT Blogger

<?php
    
// over here you connect to your old database and retrieve the values like this till the end

      while ($row= mysql_fetch_array($resultwithlimit)) {

      $a1 = $row["YourTitlefield"];
      $a2 = $row["firstnameauthor"];
      $a3 = $row["lastnameauthor"];
      $a4 = $row["otherfield42"];
      $a5 = $row["themistfield"];
      $a6 = $row["body"];
      $a7 = $row["field2"];
      $a8 = $row["wordsvalley"];
      $a9 = $row["randomfield29"];

$mynode = array();

$mynode['title'] = "$a1";

$mynode['type'] = "yourcontenttype"; // all content types have names which are given at the time of the content type creation. visible at content types page when you drag your mouse over the list of content types

$mynode['name'] = "thenameoftheusersubmittingthepage";//not necessary

$mynode['body'] = "$a6"; // You can remove the body field from your content type if you want.

$mynode['status'] = 1;//stands for published=1 or unpublished=0 content

$mynode['uid'] = 121;//uid is user id, the user id 1 being the id of the one who makes first id after a drupal installation, uid 1 has all prvilleged, make sure your user id comes with all privileges,preferably use userid 1 to save yourself from the hassle.

$mynode['promote'] = 0; // promote =0 doesn't promote the content to the front page , whereas promote=1 promotes the content to the front page

$mynode['comment'] = '2'; // comment 0=off , comment 1=readonly, comment 2=allowed

$mynode['field_friendlytitle'] = array(array('value' => "$a4" ));// field_friendlytitle is the field I presumably created using cck for the content type. Notice how I am filling in the value for field_friendlytitle using array in an array. 

$mynode['field_firstnameauthor'] = array(array('value' => "$a2" ));
$mynode['field_lastnameauthor'] = array(array('value' => "$a3" ));
$mynode['field_fieldIcreatedwithcck'] = array(array( 'value' => "$a9"));
$mynode['field_fieldIcreatedwithcck1'] = array(array('value' => "$a8" ));
$mynode['field_fieldIcreatedwithcck2sjhdshjdj'] = array(array('value' => "$a7" ));

$mynode['format'] = '2'; // inputformat, format=1 means Filtered HTML,format=1 means PHP code , format=2 means Full HTML 

//You can also specify $mynode['nid'] followed by a number which will be the nid but I presume that it wont be of use to you at the moment. Not mentioning it will lead to a sequential auto generation whereas mentioning a static number will overwrite the old content each type you save the node!;

$mynode = node_submit($mynode);
node_save($mynode);

unset($mynode);

}
?>

The two most important functions in this code are node_submit and node_save. You can view how they work at the drupal api website.
http://api.drupal.org/api/function/node_save/6,
http://api.drupal.org/api/function/node_submit

Ali Hammad Raza
WordsValley

importing data into CCK created node

most likely: it would not

You can do it directly with

Afterthought

I'm gonna try it with a test

Remember to truncate cache_content table - at least in 5.1

Go for it

I've created a few handbook

Don't need my blessing :)

Handbook page created

How-to: Import data into CCK nodes (5.x)

Page not found

404, page not found

check this snippet

Thanks

I have a similar problem

Internal Server Error

Subscribing, how about

posts to CCKs

Using drupal api

User login

Contributor links