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
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.
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
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
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.
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
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.