[Solved] Export TV's content into custom DB table : Ditto + CSV = simple + easy

[Guest]

Welcome, Guest. Please login or register.
Did you miss your activation email?

1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[davidm]

I am working on a project which requires me to export the data contained in several TVs into a single table with several fields. This might also become a bounty (if I manage to sell this to the client) to develop a module to automate such an export (not immediate need but I try to look forward...). I'll also probably need to work on importing custom DB tables into TVs but let's talk about this later

This will take some work from me as I am not a MySQL wizard (though I have started toying more and more with MySQL, importing data from other systems and custom tables into modx_site_content with success), and before I start I'd like either some feedback from people who did it and possibly advice about the process.

I am surprised this has never has been raised (or at last, I didn't find the threads !)

The first thing I plan to do is graphically "map" the fields I need in the custom DB table to the MODx fields which contains the data. Of course, given the way TVs are stored, it will mean mapping fields from several tables (modx_site_content, modx_site_tmplvar_contentvalues for content, and ) with fields from a single table with each field representing a TV.

From modx_site_tmplvar_contentvalues, I need those fields :

tmplvarid -> that's the value I need to tie TVs to the fields I'll create in the custom table
contentid -> that's the value I need to tie modx_site_tmplvar_contentvalues to modx_site_content
value -> that's the content I'll need to move from TVs to the fields I'll create in the custom table

From modx_site_content, I need those fields :

id -> that's the value I need to tie modx_site_content to modx_site_tmplvar_contentvalues
pagetitle -> that's the content I'll need to import into the title field of my custom table
parent  -> that's the field I'll need to sort which documents I need to "extract" from the MODx DB
content -> not used in my case, all the data I need is stored in TVs.

[Bruno17]

Perhaps you can use something from this one for reading the TVs (xdbfilter):

http://modxcms.com/forums/index.php/topic,23570.0.html

here a little demo : http://www.meine-goldschmiede.de/index.php?id=34

@INHERIT is not implemented but Im working on it

[davidm]

Thanks Bruno but I fail to see how it relates to what I am trying to achieve ?

If you need more information, I need to export TVs into a custom DB table for my client to exploit it outside the context of the web.

[ganeshXL]

Code:

SELECT
sc.id,
sc.pagetitle,
tv.value

FROM
modx_site_content AS sc,
modx_site_tmplvar_contentvalues AS tv

WHERE sc.parent=55
AND sc.id=tv.contentid
AND tv.tmplvarid=10

GROUP BY sc.id

ORDER BY sc.id ASC

This is an example of exporting one particular TV type (gigLocation = identifier 10), where the document parent is 55, along with the doc id + pagetitle.

It won't automagically insert that data into your custom DB table, and things get a bit more complicated when you want to export several TVs in one go.

Personally, I'd extract the values with PHP, build an multidimensional array, and finally do one INSERT or UPDATE statement for your custom DB table.
I would probably keep the unique ids from the modx-document, to make bughunting etc. easier. Or create one field "original_modx_id" for reference.

You could even use Ditto to spit out a tab-delimited list of all values with your custom tpls, and then use this for import via phpMyAdmin.

[davidm]

Thanks a lot that's exactly what I am looking for !
Great insight and advices

Never occured to me to have Ditto spit out a csv list, awesome idea !
You might just have made this a LOT easier for me, off to experiment !!!

Sure, using PHP would make things way easier and make automating this process possible but I am still short on PHP to achieve that
I might give it a try anyway, might be a way to learn along the way...

[Bruno17]

This snippet has functions which read all fields from an document and the tvs in an array. I think you can modify this to write the array in a new or existing db.

[davidm]

Ok sorry I didn't understand this, where can I download this snippet ?

Found it : http://modxcms.com/forums/index.php/topic,23570.msg145591.html#msg145591

I'll look at this right now !

[davidm]

You could even use Ditto to spit out a tab-delimited list of all values with your custom tpls, and then use this for import via phpMyAdmin.

Sometimes the simplest ideas are just the brightest !
I just managed to import my TV's data into the custom DB table

All in 30 minutes... woooot, I love Ditto !

I'll document the process, as it opens many doors to exporting data for clients...

If you come to Paris, I'll buy you diner and a nice wine !

[ganeshXL]

If you come to Paris, I'll buy you diner and a nice wine !

entendu! 

[Bruno17]

Hey, thats realy a cool idea!
And if you want only a part of the rows you can use xdbfilter to select the needed ones.

[davidm]

I really need to take more time to get into xdbfilter, remember I am no dev... but there is big potential for that snippet !

Anyway, yesterday I did a small succesful test but today I exported / imported into my custom DB all the records (125 of them, 20 fields) with no errors   It took some tweaking of the CSV parameters but it works fine : really powerful !

I'll write up a tutorial to post in Tips and Tricks and the Wiki later next week...