Struggling With More Complex Operations

CloverDX Platform
1

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 :slight_smile:

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

2

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.

3

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 :slight_smile:

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

4

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

5

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.

  • Vitamin A is required for night vision, and for a healthy skin.
  • It boosts the immune system, builds resistance to infections
  • keeps tissue in good health.
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.
New You Vitamins Amazon.com

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

6

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.