Create Feeds parser using Sheetnode

Can you be more specific about the integration of Sheetnode with Feeds/Data? How would data flow between them? What are the integration points?

I am currently working on various parts of Feeds. One of the possible integration points with Sheetnode is to display the input data in a sheet *before* sending it to the processor. This will allow interactive data massaging.

subscribe

Subscribing

+1.

infojunkie, great work with sheetnode.

"One of the possible integration points with Sheetnode is to display the input data in a sheet *before* sending it to the processor. This will allow interactive data massaging."

for excel, often it's chosen for familiarity with the end user. it might be nice for them to also be able to edit a table by virtue of editing a sheet. i was about to get going on writing a sheetnode module with a parser to transform sheetnode.value into a data table. whenever a user clicks save for the sheetnode, the module would reload the data table. it would be great if there were a sheetnode hook that could be called whenever the user changes a cell value, that updates the corresponding data field, without having to do a full reload, given that some sheetnodes may be quite large.

note: wasn't able to get it working with phpexcel 1.7.5, works with 1.7.4

@jaypark: If I understand you correctly, you're thinking of a new Feeds Parser plugin that takes its input from a sheetnode.

If that's correct, I suggest the following: In your parser's source form, insert a spreadsheet FAPI element using code like the following:

  $form['sheet'] = array(
    '#title' => t('Spreadsheet'),
    '#type' => 'sheetfield_spreadsheet',
    '#default_value' => $sheet,
  );

When submitted, you can retrieve the raw sheetnode data via $form_state['values']['sheet']['value'], which you can then parse using socialcalc_parse_sheet (located in socialcalc.inc).

In your FeedsParser::parse method, simply set your batch items to the values of the cells that you parsed above. This scheme would work with any processor, not just Data.

Closing old issues. Reopen if needed.

Hi,

As Jaypark, I wish to transform a sheetnode in regular nodes (each line becomes a node and each cell populatesa field of that node).
In my case, each user have to populate a sheetnode each year with something like 10 columns and 100 lines (a total of ~3,000 lines per year).
Later these data will be be accessed and used as search criteria using search API or maybe faceted searches.
So, I suppose the solution is to transform sheetnodes to nodes as said above.
Unfortunately, as I am a newbie with Drupal and PHP, it's a little tricky to me.
Does anyone could give me a hand to start with this?
Code snipet would be very appreciate.

Thanks a lot.

Fred

ftrebuchet, if you are interested to sponsor the development of this feature (for Drupal 7), please contact me privately.

Here's how I would approach the code:

Create a new Feeds parser that hosts the sheetnode

You can start using the code of FeedsCSVParser which comes with the Feeds module.

Let's call the new parser FeedsSheetnodeParser.

Host the sheetnode

In FeedsSheetnodeParser::sourceForm(), create a form that contains the sheetnode element:

public function sourceForm($source_config) {
  $form['sheet'] = array(
    '#title' => t('Spreadsheet'),
    '#type' => 'sheetfield_spreadsheet',
    '#default_value' => $source_config['sheet']['value'],
  );
  return $form;
}

Import the sheetnode

In FeedsSheetnodeParser::parse(), retrieve the sheet value and create rows that are passed to the next stage of the Feeds pipeline:

public function parse(FeedsSource $source, FeedsFetcherResult $fetcher_result) {
  module_load_include('inc', 'sheetnode', 'socialcalc');
  $source_config = $source->getConfigFor($this);
  $sc = socialcalc_parse_sheet($source_config['sheet']['value']);
  $rows = array();
  for ($r = 1; $r <= $sc['attribs']['lastrow']; $r++) {
    $row = array();
    for ($c = 1; $c <= $sc['attribs']['lastcol']; $c++) {
      $cell = $sc['cells'][socialcalc_cr_to_coord($c, $r)];
      // e.g. add $cell value to $row
      $value = $cell['datavalue'];
      $row[] = $value;
    }
    $rows[] = $row;
  }
  return new FeedsParserResult($rows, $source->feed_nid);
}

NOTE: I did NOT run or test this code. It's meant for illustrative purposes only.

Hope this helps!

Hi Infojunkie,

As I'm engaged with a non profit organization with a limited budget for this project, I think it will be difficult to collect funds for developing a general response for this subject, but I'll ask, you never know (and it would be great to participate in a contribution).

However, the goal was not to get a development for free, but to get clues on how to proceed cause I thought someone else has already did that. So, thank you very much for the pointer you gave to me, I think (in fact, I hope) that transforming a sheetnode to an array was the missing piece.

I was thinking about transforming the sheetnodes to CSV files, then load the nodes from files using Migrate module or an other one, but an all-in-one solution should be better (and a valuable exercise).

Thank's again.

I'd appreciate if you could post any amendments/fixes to the above pseudocode. I might turn this into a new Sheetnode submodule.

Sure I will, as soon as I worked with it (hope during the week).
I'll also certainly post some issues or feature requests about Sheetnode module.

Hi,

I have been occupied with an other project but I'm returning to this one.
I've not created a new feed parser but used your code for a more specific solution.
I've changed some details, but the idea is yours.
Now I'll have some questions about sheetnode's configuration using a separate post.

Thanks again.

Fred

function _myfunction_insert_data_from_sheetnode($n) {
  // Load the socialcalc.inc from sheetnode module
  require_once(drupal_get_path('module', 'sheetnode') . '/socialcalc.inc');
  //
  // Some specific stuff goes here
  //
  
  // Prepare the default values for the destination data
  $values = array(
    'type' => 'destination_content_type',
    'uid' => $user->uid,
    'status' => 1,	//(1 or 0): published or not
    'promote' => 0,	//(1 or 0): promoted to front page
    'comment' => 0	// 0 = comments disabled, 1 = read only, 2 = read/write
  );

  // Parse all sheetnodes to generate nodes of destination_content_type
  // Build an array with all sheetnodes associated with saved node
  $sheets = array();
  foreach (sheetnode_get_sheetfields($n->type) as $field_name => $field) {
    foreach ($n->{$field_name}[LANGUAGE_NONE] as $item) {
      $sheets[$field['instance']['label']] = socialcalc_parse($item['value']);
      $sc = socialcalc_parse_sheet($item['value']);
      // $sc is an array of arrays representing all the cells (A1, B1, ... A2, B2, ...) 
      // For each cell, 'datavalue' contains the value entered by the user
      // The sheetnode contains 2 header lines
      for ($r = 3; $r <= $sc['attribs']['lastrow']; $r++) {
        $row = array();
        for ($c = 1; $c <= $sc['attribs']['lastcol']; $c++) {
	  $cell = (isset($sc['cells'][socialcalc_cr_to_coord($c, $r)])) ? $sc['cells'][socialcalc_cr_to_coord($c, $r)] : null;
          // Add $cell datavalue to $row (or NULLL if unset)
	  $row[] = (isset($cell['datavalue'])) ? $cell['datavalue'] : null;
        }
        // Create an element of destination_content_type and get the corresponding wrapper
        $e = entity_create('node', $values);
        $ew = entity_metadata_wrapper('node', $e);
        // Set the entity fields
        $ew->field_one = is_float($row[ 1]) ? (int) $row[ 1] : null;
        $ew->field_two = is_float($row[ 2]) ? (int) $row[ 2] : null;
	// And so on... then save the row of detailed data
        $ew->save();
      }
    }
  }
}

Good to see progress. Are you still going ahead with a Feeds plugin?

For now I'll stick with a more specific solution that will meet my needs.
Later, perhaps something more generic could be interesting.
For example, a solution to describe the mapping rules (which target entity, which column goes in which field) and control values ​​(ranges of values ​​for numeric fields, etc.) would certainly help.
At the moment, for me everything is hardcoded.
One day maybe ...
In any case, thank you again for your help. It was very useful to me and sheetnode is definitively adopted for my use case.

Fred