My scenario: We are using Excel spreadsheets to keep track of the 2000+ semi-trailers in our yards. We want to switch to Drupal to track this data. However, updating Drupal Nodes is far more time-consuming for our data entry personnel than editing a spreadsheet.

So here is my thought: Why not have Drupal import the data from a CSV file? This way, the data entry personnel can continue updating a spreadsheet, yet the data will be brought into Drupal on a cron basis using FeedAPI and CSV parser.

Each semi-trailer in our inventory has its own Drupal node, with various CCK fields attached to it. I would like to have this node's CCK values updated with data from the CSV file each time that trailer appears in the file.

How do I accomplish this? What modules should I use? How would I configure them?

ANY ideas or suggestions are welcomed and appreciated. Thanks!

Comments

aitala’s picture

Take a look at...

http://drupal.org/project/feeds

Eric

__________
Eric Aitala - ema13@psu.edu
Earth and Space Science Partnership

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

lunk_rat’s picture

Awesome. I had not seen this module. Thanks for the tip!

lunk_rat’s picture

Looks like the Feeds project will work for my needs. I notice that only URL and GUID can be a "unique target" for Feeds CSV imort. I have a CCK field that is unique to each node (enforced via the Unique field module). Can I make that a 'unique target' for my CSV import?

Each of my nodes has a unique 'unit id' CCK field. I would like Feeds to (1) look at my CSV file and find the column mapped to the unique target ('unit ID'), (2) match that to the unique 'unit ID' CCK field of an existing node, then (3) update that node with the rest of the CSV data in that row that is mapped to the node's CCK fields.

This will allow me to achieve my goal of having data entry personnel update a CSV and have that data imported into my Drupal nodes via feeds.

Thanks for the help!

aitala’s picture

Not a clue - I did not dig into the module that far...

Eric

__________
Eric Aitala - ema13@psu.edu
Earth and Space Science Partnership

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

sapark’s picture

You could skip the feed step and write a module to take the data from csv on cron. You could read the csv file for the unique id, query the database for the node id of the unique id, load the node by node id, then update its fields and save the node.

Here's starting code:
http://drupal.org/node/552676#comment-1952220

Except I would change the code to use hook_cron inside a module rather than editing the cron.php that comes with Drupal. http://api.drupal.org/api/function/hook_cron/6 And this would I think also require staff or automated procedure to export the Excel spreadsheet to a csv file daily or however often you need it.

sapark’s picture

Here's starting code to go into updatedata.module:

<?php
/***********************************************************
*
* Implementation of hook_cron.
*
* Updates nodes from a comma separated values (CSV) file.
* Place the CSV file in the root of the Drupal site.
* Install Devel module to get names of your fields; Devel module instructions and
* source of code: acquia.com/blog/migrating-drupal-way-part-i-creating-node.
* Also source of code: drupal.org/node/67887.
***********************************************************/
function updatedata_cron() {
 
$filename = 'import.csv';
 
$fp = fopen($filename, "r");         // Open the CSV file read only.

 

while ($line = fgetcsv($fp)) {     // Read a line of the file and update a node from it.
    // Get the unique id.
   
$myuniqueid = $line[0];
   
// Query the database for the nid of the unique id.
    // If unique id is numeric use this:
   
$result = db_result(db_query("SELECT nid
                                        FROM {content_type_mytype}
                                        WHERE field_myuniqueid_value = %d"
, $myuniqueid));
    
// Otherwise if unique id is alphanumeric use this:
    //$result = db_result(db_query("SELECT nid
    //                                  FROM {content_type_mytype}
     //                                 WHERE field_myuniqueid_value = '%s'", $myuniqueid));

    // Load the node from the nid
   

if ($result) {
     
$node = node_load($result);
   
     
// Update the fields.
      // CCK fields.
     
$node->field_myfield1[0]['value']         = $line[1];  
     
$node->field_myfield2[0]['value']         = $line[2];
     
$node->field_myfield3[0]['value']         = $line[3];  
     
     
node_save($node);   // Save node, also bypass required fields.
   
}
   
// Optionally, if there is no nid for this unique id, create a new node instead.
    //else {
    //$node = new stdClass();            // Construct the new node object.
    //$node->type = 'document';       // Your specified content type.
    //node_object_prepare($node);   // Fill in default values for uid, status, promote,
                                                           // sticky, created, changed, and revision properties.
    // Update the fields.
   
    //node_save($node);   // Save node, also bypass required fields.

    //}
 

}
 
fclose ($fp);         // Close CSV file.
}
?>

And here's what goes in the updatedata.info file:
; $Id
name = Update data
description = Update data from a CSV file on cron.
core = 6.x
version = "6.x-1.0"

These files go in sites/all/modules/updatadata. Hope that helps.

lunk_rat’s picture

AWESOME! Thank you for this! So far, apart from theming, I am a "point-and-click" Drupal guy; I have not written modules or used much PHP.

But this looks promising, I understand it, and it looks like it will do exactly what I need it to. I just have to get it in there and test it . . .

Thanks again sapark, you rock!

sapark’s picture

Maybe not, didn't see custom unique ids.

aitala’s picture

Would you want to sanitize the data coming from the CSV file in any way?? Might be a good idea just in case.

Eric

__________
Eric Aitala - ema13@psu.edu
Earth and Space Science Partnership

224b8605113373e086cb27708ff301ba18ce394db1996e7e22928e4555e0d20b1b6cecc7f67c9bd9e536cb915779c485

rcrowe’s picture

Pardon my ignorance as I'm a newbie to Drupal and not very skilled at php or server administration.

I am trying to get this script to work but am having no luck. I have this module installed in /sites/all/modules/updatedata and it is enabled on the Modules page. I upload the file "mth_update.php" to the root of my Drupal installation. (It is a multi-site install, and I will only use this module on this site - should I put it in /sites/[siteurl]/modules and upload the mth_update to the root of that site?) Then I go to http://[siteurl]/cron.php, I get a WSOD, then go back to http://[siteurl] and everything looks fine but the update to the nodes did not happen. I believe the module is running on hitting cron.php because I was getting errors about field names, but then used the Devel module to get the names of the CCK fields. I have cleared the cache between each try. There are no apache errors nor errors in the watchdog list but it is not updating the nodes.

Here is my version of the script.

<?php
/***********************************************************
*
* Implementation of hook_cron. Is run each time cron.php is run.
*
* Updates nodes from a comma separated values (CSV) file.
* Place the CSV file in the root of the Drupal site.
* Install Devel module to get names of your fields; Devel module instructions and
* source of code: acquia.com/blog/migrating-drupal-way-part-i-creating-node.
* Also source of code: drupal.org/node/67887.
***********************************************************/
function updatedata_cron() {
 
$filename = 'mth_update.csv';     //put in root of Drupal site.
 
$fp = fopen($filename, "r");         // Open the CSV file read only.

 
while ($line = fgetcsv($fp)) {// Read a line of the file and update a node from it.
    // Get the unique id.
   
$mthid = $line[0];
   
// Query the database for the nid of the unique id.
    // If unique id is numeric use this:
   
$result = db_result(db_query("SELECT nid
                                        FROM {content_type_mth_members}
                                        WHERE field_mth_id_value = %d"
, $mthid));
    
// Otherwise if unique id is alphanumeric use this:
    //$result = db_result(db_query("SELECT nid
    //                                  FROM {content_type_mytype}
     //                                 WHERE field_myuniqueid_value = '%s'", $myuniqueid));

    // Load the node from the nid
   
if ($result) {
     
$node = node_load($result);
  
     
// Update the fields.
      // CCK fields.
     
$node->field_mth_last_name_value[0]['value'] = $line[1]; 
     
$node->field_mth_first_name_value[0]['value'] = $line[2];
     
$node->field_mth_company_value[0]['value'] = $line[3];
     
$node->field_mth_address_value[0]['value'] = $line[4];
     
$node->field_mth_city_value[0]['value'] = $line[5];
     
$node->field_mth_state_value[0]['value'] = $line[6];
     
$node->field_mth_zip_value[0]['value'] = $line[7];
     
$node->field_mth_county_value[0]['value'] = $line[8];
     
$node->field_mth_expiration_value[0]['value'] = $line[9]; 
    
     
node_save($node);   // Save node, also bypass required fields.
   
}
  
  }
 
fclose ($fp);         // Close CSV file.
}
?>

I'd seriously appreciate any hints as to why it's not working.

sapark’s picture

Could it be you need to upload the filename mth_update.csv instead of mth_update.php?

Bartezz’s picture

subscribe...

________________
Live fast die young

beauz’s picture

If anyone else is looking at this still I suspect the problem is

$node->field_mth_last_name_value[0]['value'] = $line[1];

should be

$node->field_mth_last_name[0]['value'] = $line[1];

update to reflect your field names obviously...

veeray’s picture

This is a great piece of code. I am wondering if it is a viable option for updating over 10,000 nodes. Or if node_save would time out at that level and I need to be looking at feeds or the batch api.

prabhatjain01’s picture

Hi I have successfully added values ,taxonomy to node by using node_save .

But I am looking to your solution .
I have created a separate column in excel named unique id and also add a field in my content type.
And if both id match then update node otherwise create a new node,

Prabhat Jain
Drupal Developer
Mail to:prabhatjain01@gmail.com
Contact No:+91 9960130111