Feeds support for importing prices

I have something worked out, but you need to set a separator for multiprice support into a single field with unlimited values, for the case below is pipe separated values, for importing a sample of data like this "2,56|1|19";"2,33|20|-1" where the first number is the price amount, then the min qty, next the max qty being -1 considered by price table as unlimited.

Code example:

/**
 * Implements hook_feeds_processor_targets_alter().
 */
function mymodule_feeds_processor_targets_alter(&$targets, $entity_type, $bundle_name) {
  foreach (field_info_instances($entity_type, $bundle_name) as $name => $instance) {
    $info = field_info_field($name);
    if ($info['type'] == 'commerce_price_table') {
      $targets[$name] = array(
        'name' => $instance['label'],
        'callback' => 'mymodule_feeds_set_target_price_table',
        'description' => t('The table price for @name field.', array('@name' => $instance['label'])),
        'real_target' => $name,
      );
    }
}

/**
 * Callback for mapping. Here is where the actual mapping happens.
 *
 * When the callback is invoked, $target contains the name of the field the
 * user has decided to map to and $value contains the value of the feed item
 * element the user has picked as a source.
 */
function mymodule_feeds_set_target_price_table($source, $entity, $target, $value, $mapping) {
  if (empty($value)) {
    return;
  }

  // Handle non-multiple value fields.
  if (!is_array($value)) {
    $value = array($value);
  }

  // Iterate over all values.
  $info = field_info_field($target);
  $field = isset($entity->$target) ? $entity->$target : array();
  $i = isset($field[LANGUAGE_NONE]) ? count($field[LANGUAGE_NONE]) : 0;
  foreach ($value as $v) {
    if (!is_array($v) && !is_object($v)) {
      list($amount, $min_qty, $max_qty) = explode('|', $v);

      // Conversion from comma to decimal point.
      $amount = str_replace(',', '.', $amount);

      // always put the default currency
      $currency_code = commerce_default_currency();
      $field[LANGUAGE_NONE][$i]['currency_code'] = $currency_code;
      $field[LANGUAGE_NONE][$i]['amount'] = $amount;
      $field[LANGUAGE_NONE][$i]['min_qty'] = $min_qty;
      $field[LANGUAGE_NONE][$i]['max_qty'] = $max_qty;
    }
    if ($info['cardinality'] == 1) {
      break;
    }
    $i++;
  }
  $entity->{$target} = $field;
}

@pcambra @thill_ Which patch has been successful? And what file are you patching it to?

Note that since #1322714: Odd Pricing Table Issue landed the

$field[LANGUAGE_NONE][$i]['amount'] = $amount;

part in #1 needs to be

$field[LANGUAGE_NONE][$i]['amount'] = commerce_currency_decimal_to_amount($amount, $currency_code);

otherwise you'll get very wrong amounts displayed afterwards (0.16 instead of 16.30, for example).

This works as advertised! I had to use feeds tamper for it to work as thill pointed out.

FYI - I needed to add a closing curly bracket in hook_feeds_processor_targets_alter() to keep it from breaking the site. Will update after I test an import

Working Code

Got this code to work (wonderfully!) with some minor adjustments and a careful setup:
1) Added the closing curly bracket per my last comment
2) Removed the lines to convert the comma to a decimal on prices (my prices ARE in decimal format)
3) Made the change in #4
My code is here:

/**
 * Implements hook_feeds_processor_targets_alter().
 *
 * Needed for importing Price Table data
 */
function MYMODULE_feeds_processor_targets_alter(&$targets, $entity_type, $bundle_name) {
  foreach (field_info_instances($entity_type, $bundle_name) as $name => $instance) {
    $info = field_info_field($name);
    if ($info['type'] == 'commerce_price_table') {
      $targets[$name] = array(
        'name' => $instance['label'],
        'callback' => 'MYMODULE_feeds_set_target_price_table',
        'description' => t('The table price for @name field.', array('@name' => $instance['label'])),
        'real_target' => $name,
        );
      }
    }
}

/**
 * Callback for mapping. Here is where the actual mapping happens.
 *
 * When the callback is invoked, $target contains the name of the field the
 * user has decided to map to and $value contains the value of the feed item
 * element the user has picked as a source.
 *
 * Needed for importing Price Table data
 */
function MYMODULE_feeds_set_target_price_table($source, $entity, $target, $value, $mapping) {
  
  if (empty($value)) {
    return;
  }

  // Handle non-multiple value fields.
  if (!is_array($value)) {
    $value = array($value);
  }

  // Iterate over all values.
  $info = field_info_field($target);
  $field = isset($entity->$target) ? $entity->$target : array();
  $i = isset($field[LANGUAGE_NONE]) ? count($field[LANGUAGE_NONE]) : 0;
  foreach ($value as $v) {
    if (!is_array($v) && !is_object($v)) {
      list($amount, $min_qty, $max_qty) = explode('|', $v);

      // always put the default currency
      $currency_code = commerce_default_currency();
      $field[LANGUAGE_NONE][$i]['currency_code'] = $currency_code;
      $field[LANGUAGE_NONE][$i]['amount'] = commerce_currency_decimal_to_amount($amount, $currency_code);
      $field[LANGUAGE_NONE][$i]['min_qty'] = $min_qty;
      $field[LANGUAGE_NONE][$i]['max_qty'] = $max_qty;
    }
    if ($info['cardinality'] == 1) {
      break;
    }
    $i++;
  }
  $entity->{$target} = $field;
}

Add this to an existing module, a new one, or your own "glue" module... be sure to change the MYMODULE to whatever your module is actually named. If you want to hack Commerce Price Table, you can replace MYMODULE_ with commerce_price_table_ like the example in comment #2... of course, this is inadvisable as you will just lose this code on your next update of the module

Format for data

This is actually pretty straightforward, but I will leave a note her for those that may come behind. Basically, you want to create a delimited group of values (price, min qty, max qty) that is further delimited within a single field. Confused? Its sort of like making a nested array. Basically we want a single slot in the CSV to hold information about each additional entry in the table price field... and each of those entries is in turn composed of 3 separate pieces of info.

Ok - real example. Each of the 3 items for the table entries are expected by code to be separated by a pipe (|). And assuming that you are using commas in your CSV, then we want some OTHER character to delimit within the entry.. so I will use a semicolon. If we want to end up with table prices like this:

• $40 for 0-10 items (40.00|0|10)
• $30 for 11-25 items (30.00|11|25)
• $20 for 24-50 items (20.00|25|50)
• $10 for 51+ items (10.00|51|-1)

Then we need a single CSV entry of something like this:
40.00|0|10;30.00|11|25;20.00|25|50;10.00|51|-1

So - a line in your CSV showing the title, sku, price and price table, would look something like this:
Product title,sku-12345,40.00,40.00|0|10;30.00|11|25;20.00|25|50;10.00|51|-1

Tamper settings

Now - the code above WILL take care of parsing through each entry for the 3 items (as delimited by the pipe), but it WON'T break the single field into an array. Thats what the Feeds Tamper is for. Once you create your Feeds importer settings and your mapping, just click the "Configure Feeds Tamper" on the mapping screen. Then, add a plugin for the field that pull sin the price table data. You wan to choose the "Explode" plugin and select your delimiter (in this case, a semicolon).

That should do it! If your CSV is properly formatted, then you should be able to "/import" and make it happen. You may have a slightly different setup, or be working with something other than a CSV... but this should get you started.

Perhaps this could be added as a submodule of Commer Price Table with a readme to explain how it works? I am sure there are LOTS of people using Feeds to import and update products, and if they are wanting to use this module, then this would be handy to have.

#8 - Wonderful post. Thanks!

rlnorthcutt - great post many thx, also many thx to the module creator. Ive just managed to import 3,200 qty price breaks of various types all done in about 2 hours including setting this up. It all worked first time as well!

One thing ive noticed is that when re-importing the prices dont get updated, even after setting skip hash check. I ended up truncating the field_revision_field_quantity_pricing table to clear out the data.

Hi Folks,

I've created a module for this piece of functionality here:
https://github.com/markconroy/commerce-price-table-feeds

I'd put it as a module on this site, but I don't know enough about feeds and don't feel in a comfortable position to maintain it.

If anyone thinks it should be a module here, I can create it and give them maintainer access, or you can create it from the module I have posted on github.

Just to note: from my testing, this works perfectly - just make sure you follow the instructions in #8 - well done rlnorthcutt, a credit to you.

Just set this up today. Worked like a charm!

I'm using Feeds extensible parser to import an XML file via XPath.

Here's how I managed to use the patch here to import multiple prices and quantities, in case anyone else has the same challenge.

First, here's a sample of the pricing for one product:

<prices>
	<price>
		<quantity>1000</quantity>
		<price>0.38</price>
	</price>
	<price>
		<quantity>3000</quantity>
		<price>0.14</price>
	</price>
	<price>
		<quantity>5000</quantity>
		<price>0.1</price>
	</price>
	<price>
		<quantity>10000</quantity>
		<price>0.07</price>
	</price>
	<price>
		<quantity>20000</quantity>
		<price>0.07</price>
	</price>
</prices>

In the XML Xpath parser settings, I set up multiple parsers like this:

• price1 > pricing/prices/price[1]/price
• price2 > pricing/prices/price[2]/price
• price3 > pricing/prices/price[3]/price
• price4 > pricing/prices/price[4]/price
• price5 > pricing/prices/price[5]/price
• quantity1 > pricing/prices/quantity[1]/quantity
• quantity2 > pricing/prices/quantity[2]/quantity
• quantity3 > pricing/prices/quantity[3]/quantity
• quantity4 > pricing/prices/quantity[4]/quantity
• quantity5 > pricing/prices/quantity[5]/quantity

I mapped each one to a temporary target. I also mapped a Blank Source to my Price Table field (field_price_table).

I then used Feeds Tamper PHP to rewrite the Price Table field like so:

$price1=$item['price'];
$price2=$item['price2'];
$price3=$item['price3'];
$price4=$item['price4'];
$price5=$item['price5'];
$min1=1;
$max1=$item['quantity'];
$min2=$max1+1;
$max2=$item['quantity2'];
$min3=$max2+1;
$max3=$item['quantity3'];
$min4=$max3+1;
$max4=$item['quantity4'];
$min5=$max4+1;
$max5=$item['quantity5'];

$output = $price1 . '|' . $min1 . '|' . $max1 . ';' ;
$output .=  $price2 . '|' . $min2 . '|' . $max2 . ';' ;
$output .=  $price3 . '|' . $min3 . '|' . $max3 . ';' ;
$output .=  $price4 . '|' . $min4 . '|' . $max4 . ';' ;
$output .=  $price5 . '|' . $min5 . '|' . $max5;

return $output;

This put the data for all prices and quantities in the format described in #8, including adding 1 to the maximum value for each band for use as the minimum in the next band.

0.38|1|1000;0.14|1001|3000;0.1|3001|5000;0.07|5001|10000;0.07|10001|20000

Finally, I used an Explode tamper on the Price Table field, using ; as the delimiter to split it into the 5 price lines for my price table.

After an import, all prices and min/max prices matched the data in the source XML.