5

I get a lot of information on spreadsheets that must be uploaded to Tridion (usually one line per component). Currently I am using Core Service to upload this data but it's a time consuming job defining the xml schema templates each time (I have a lot of schema definitions). I can't help thinking that this is a common requirement and someone has already come up with a good solution.

Can anyone suggest a solution?

Here's a scenario

I am a content editor, I am good at organising data but I don't know how to write software (I don't really know what XML is).

A third party company provides me with data about the 1000 richest people in the world. Fields may be Name, Age, Country, Estimated Wealth. I create a RichPerson Schema reflecting these fields with the Country being a component link. Now I need to put all this data into Tridion, I can't do it manually, it would take me about a week and I'll probably make a mistake.

So, I convert all the countries in the spreadsheet to tcms of existing components. I then use some program to upload all this data as a particular schema. As long as I name the field titles correctly the program works out where to put the data.

  • 2
    That used to be a feature of Content Porter, to import from an Excel sheet, unfortunately it is not there anymore in the current versions, the Core Service is marked as a replacement to my knowledge (but that does require you to build your own tool indeed). Let's see if anybody has build something generic that they are willing to share. – Bart Koopman May 23 '13 at 10:45
  • Excel itself has an XML add-on to create .xsd definitions, but it would take some work to get it to match a Tridion schema. And I'm not sure it'll fit the one row per component setup. – Alvin Reyes May 24 '13 at 0:14
  • It sounds like knowing what the schema should be would be the hard part. If you simply want a text field per column, that should be easy enough to code up, but it's unlikely that will make sense for real life cases. Speaking of which, why are you doing this in the first place? Is the data different every time? – Dominic Cronin May 24 '13 at 19:17
  • @DominicCronin I guess what I was really asking is if there was a program out there already that allowed uploading data from spreadsheets. It looks like there isn't. I'll have to make one in the next few weeks. The reason why I'm doing this is to allow non-developers the ability to upload large amounts of data. I can't expect them to modify the code every time a new schema is added to Tridion. That's why I need something generic. The user specifies the schema id along with all the fields on an excel spreadsheet and the program will convert it into something Tridion understands. – Kevin Brydon May 25 '13 at 12:02
  • Why would people want to upload large amounts of more or less unstructured data to Tridion? – Dominic Cronin May 26 '13 at 13:14
4

i think you can connect the spreadsheets as data source and than map to the Component Schema field from component. and this can be done in a process, let me know if you any question.

  • Interesting. In Excel that'd be Data > From Other Sources > From XML Data Import. In terms of "process," are you thinking of some type of automation? – Alvin Reyes May 25 '13 at 3:02
  • I read this when you posted and didn't really understand what you mean. I still don't really understand what you mean. Could you elaborate on the process? – Kevin Brydon May 26 '13 at 19:39
  • yes, you are right, also excel can be windows data source, and than you can automate the import process into Tridion from coreservice :) – Lucas Liu May 31 '13 at 1:20
2

Thinking out of the box: write something that exports XML files out of each line from the xls and upload with WebDAV. Sounds far-off but could be best solution in some case (as you did not tell us if this is a once off, or if the structure is always the same etcetc) and writing conversion from XLS to XML files is a plain .Net job, nothing to do with Tridion development (as long as you give them the schema xsd), save to disk and have some automated copy batch to the WebDAV folder if you like :)

  • Isn't that pretty much what I'm doing just now, only using WebDAV instead of Core Service? I was hoping someone had come up with a tool that, given a spreadsheet (in a particular format) and schema name/id would create components automatically. No messing around with XML. – Kevin Brydon May 23 '13 at 12:10
  • as far i know, you can not put metadata using WebDaV, if you have any requirement like that, you have to use other approach – Raj Kumar May 23 '13 at 12:59
  • When I read this post earlier today, that was exactly the thought I had. Webdav could work very well here, although I suspect the difficult part remains the creation of arbitrary(?) schemas. @Raj, you can configure the webdav cartridge to add default metadata. – Dominic Cronin May 24 '13 at 19:14
  • @DominicCronin for any schema? Edit: ok, nevermind, found it on SDL Live Content (requires login). – Alvin Reyes May 25 '13 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.