Struggling With More Complex Operations

phaynes

• July 04, 2012 00:00

• Answered

Follow

Hi,

I'm new to ETL and am evaluating a bunch of different tools to see what fits our needs. I have made some good progress but have now run into a bit of a roadblock and need help :)

One of the things I'm trying to do is modify a mySQL file from Wordpress, but so far I'm running into some issues.

I have the file loaded in via DBInputTable. The data in the table looks as follows:


meta_id   post_id   meta_key   meta_value
 
 53   21   _wp_page_template   tpl-myaccount.php 
 54   22   _wp_page_template   tpl-articles.php 
 55   23   _wp_page_template   tpl-contact.php 
 56   24   _wp_page_template   tpl-checkout.php 
 57   25   _wp_page_template   tpl-callback.php 
 58   26   type   article 
 59   26   image   article.jpg 
 60   27   type   article 
 61   27   image   article.jpg 
64   28   SKU   001 
 65   28   hits   102 
 66   28   price   30 
 67   28   old_price   49.99 
 68   28   image   http://ecx.images-amazon.com/images/I/41PbLTMtFgL.jpg 
 69   28   images   http://ecx.images-amazon.com/images/I/41PbLTMtFgL.jpg,http://ecx.images-amazon.com/images/I/41258sEWRLL.jpg, 
 70   28   customlist1    
 71   28   customlist2    
 72   28   customlist3    
 73   28   customlist6    
 74   28   qty   50 
 75   28   featured   yes

What I want to do is create the following output for the data highlighted in blue:


post_id   SKU   hits   price   old_price   image 
28    001   102   30    49.99   http://ecx.images-amazon.com/images/I/41PbLTMtFgL.jpg   

alt_image1
http://ecx.images-amazon.com/images/I/41PbLTMtFgL.jpg 

alt_image2
http://ecx.images-amazon.com/images/I/41258sEWRLL.jpg 

qty   featured 
50   yes 

Is there any documentation that specifically deals with how to do this type of manipulation?

Totally stuck..

Thanks .... Peter

• 

  admin

  • July 04, 2012 13:12

  0

  Comment actions Permalink

  Hi Peter,
  
  there is useful component for what you need - http://doc.cloveretl.com/documentation/ ... pivot.html
  
  * define input (meta_id, post_id, meta_key, meta_value) and output (post_id, SKU, hits, price, old_price, image, ...) metadata
  * read records in input metadata format
  * sort them via post_id
  * input them into pivot component
  * output of pivot component is in output metadata format
  
  Set pivot properties to:
  * key = post_id
  * Field defining output field name = meta_key
  * Field defining output field value = meta_value
  
  Keep in mind values in meta_key must exactly correspond to filed names in output metadata.
  
  I hope this helps.
• 

  phaynes

  • July 04, 2012 18:59

  0

  Comment actions Permalink

  Thank you for your prompt response ... I got it to work once I figured out that I had to add all the meta_key values to the output metadata :)
  
  Only one small problem, the post_id was not populated on the output file. How can I achieve this as I will need it for later processing?
  
  Thanks ... Peter
• 

  phaynes

  • July 05, 2012 19:06

  0

  Comment actions Permalink

  Hi,
  
  Please ignore my last question, I've figured it out.
  
  I set a different type (string) for post_id in the output metadata from the type (long) set in the input metadata.
  
  Thanks ... Peter
• 

  phaynes

  • July 07, 2012 19:17

  0

  Comment actions Permalink

  Hi,
  
  Can you help me please.
  
  I get the following data in the description field of my input file, and would like to use Cloveretl to strip out the html tags colored blue leaving the actual text and remove all of the html code colored green
  
  Vitamin A as Beta Carotene is a vision-enhancing nutrient was isolated in 1930, the first fat-soluble vitamin to be discovered. The body acquires some of its vitamin A through animal fats or vegetable sources.<ul><li>Vitamin A is required for night vision, and for a healthy skin.</li><li> It boosts the immune system, builds resistance to infections</li><li> keeps tissue in good health.</li></ul>Vitamin A and B2 work together to help keep mucous membranes in the gastrointestinal tract healthy.*These statements have not been evaluated by the Food and Drug Administration.  This product is not intended to diagnose, treat, cure or prevent any disease.<hr /><a href="http://www.amazon.com/gp/product/B007WSV7AU" target="_blank"><img class="aligncenter" title="New You Vitamins Amazon.com" src="http://newyouvitamins.s3.amazonaws.com/salesaids/amazonproductlink.png" alt="New You Vitamins Amazon.com" width="504" height="59" /></a><hr />
  
  Please note that I have other fields in my input file that contain html that I need to keep.
  
  I have searched the forum and documentation and can find nothing obvious on this particular topic.
  
  Is what I want to do possible in Cloveretl and, if so, how can I achieve it?
  
  Many thanks ... Peter
• 

  admin

  • July 09, 2012 09:00

  0

  Comment actions Permalink

  Hi Peter,
  
  you can either use http://doc.cloveretl.com/documentation/ ... eader.html if your file is valid xml, or use function "replace" in Reformat component. See http://doc.cloveretl.com/documentation/ ... -ctl2.html It supports regular expression so maybe it will be usable for you.

Please sign in to leave a comment.