How to create xml plist from Excel spreadsheet

I have a process that I've used quite a bit in the past...all manual, but goes pretty quick I think.

This is fairly easy to do as long as you have a clear idea of how your plist structure will be. Can you show me at least one record/row that is complete? If so, I can give you a detailed process to flesh out the conversion from there...

If you like, It might be easier to just send me your data and I can set it up and send it back.

You can use a spreadsheet to create the examples...
Something like this...
(original data in columns)
(headers)-->type|color|sex
(data)----->dog|brown|male

After adding columns to allow for tags before/after data, using just the column 'type':
atag|type|btag|.................
|<key>Title</key><string>Type</string><key>Text</key><string>|dog|</string>..... ............

This would give you the strings necessary for find/replace...you can either do that, or just fill down in your spreadsheet and then dump that into your plist...but only if you have a rather simple plist. If it is has a parent/child structure, you'll need to perhaps practice a bit before you can turn your editor loose on your entire data.

How I do this exactly depends on whether the target plist is a one-off or I plan on repeating the same process, in which case I'll create macros.

In some cases, I'll take time to build one plist product, and then clone that for similar topics, manually editing the data directly inside Xcode. As with so many other procedures, it depends on a combination of your skills, goals, time etc.

I can perhaps volunteer to whip up a simple beginning structure, using a sample of your data, for you to finish off if you like, but no promises.

Ok, now show me your plist structure.... Here is an example of a parent (Playback Menu) with one child (Delete):

<dict>
 <key>Rows</key>
 <array>
 <dict>
 <key>Title</key>
 <string>PLAYBACK MENU</string>
 <key>Children</key>
 <array>
 <dict>
 <key>Title</key>
 <string>Delete</string>
 <key>Children</key>
 <array>
 <dict>
 <key>Title</key>
 <string>Selected</string>
 <key>Infotxt</key>
 <string>• Playback Menu / Delete / Selected:
Pressing the button on the left will bring up the 'Delete' sub-menu.</string>
 </dict>

Well, you see, this bring us to the typical issue that most people face along the way...concept.

For a 'convertor' to change data in a spreadsheet to tagged XML requires that the XML/plist structure be already defined. Otherwise there is no connection between the original purpose of the data as it sits in a spreadsheet and how it should exist when formatted as XML for use with an iPhone app, as an example.

Since a convertor can't do that for you, you need to establish the specifics...and by the time you do that, you're on your way towards converting the data which then belies the need for a convertor.

Before you can go ahead, you need to define your XML/plist structure. And of course, that structure is wholly dependent on how your app functions in terms of interacting with the data.

First design the function (1), then format the data structure (2) then gather the data (3) and finally wrap it in XML for use as a plist (4). Where are you in that process?

Read my previous post above and then let me know what you may need to proceed.

I could write a macro to do it, but certainly there must be an existing utility to do this.

No utils. I think the resources are all busy doing language translations software 🙂

Besides, the actual work is trivial once you understand what is needed.

Writing a macro only makes sense if you're adept and intend to do this several times.

If you flatten your structure like this:
<dict><key>climbedDate</key><date>1950-06-03T00:00:00Z</date><key>height</key><i nteger>8091</integer><key>name</key><string>Mountain 7</string></dict>

...you can perhaps see where columns would be added in excel to bracket the data. You then fill down to flood the remaining rows. In your example, you would have six columns of data bracketed by a pair of tags each for a total of eighteen columns.

Grab a valid plist and insert your data structure and one item. To be sure it at least works, introduce/add this to, and run it in your xcode project, before proceeding to flood the rest of the data.

Copy/paste from your spreadsheet into the plist, being sure to locate/add the data between the proper main base tags. Apply a soft-wrap for your viewing ease if you like. Remove the initial dummy item and go back to your project.

If your plist isn't valid in basic structure, xcode will complain. If your app doesn't pull from it for whatever reason, but it likes the basics, you will see a white screen in the simulator. Again, making sure it works with just one item first, then flooding in the bulk data will help to troubleshoot if anything goes south along the way.

copying introduces random spaces.

Interesting - never had that happen. What are you using for a text editor? What type of line endings?

Everyone's tool/data environments are different, so anything is possible. Sounds like something that could be groomed by a quick grep tho.

I think I would just export the xls into a comma separated value (csv) file, then read the list into NSString, do a little parsing, load NSDictionary with the data and ask it to write a plist.

The parsing shouldn't be too bad. Here's a related thread: [http://discussions.apple.com/thread.jspa?messageID=9918703&#9918703]. If you need help with the parser, let us know. Maybe you can make the utility you asked about: Verduomo's csv to plist Converter. $1.99 *** 816 reviews. xls to plist coming soon.

\- Ray

KT - You rock! What a life saver.
Wish I had thought of this. Beats fumbling around in php or perl.

Thanks for the tip!

-Phil

Thanks for the tip!

My pleasure 🙂

This handy utility convetts from CSV to Plist

http://blog.danilocampos.com/2009/12/04/convert-a-csv-into-a-plist-file/