Convert Plist to excel

therock003

Board Regular
Joined
Jan 9, 2008
Messages
183
Plist is a kind of an xml format used by apple to store information inside its database. I tried importing the file as an .xml data source but it wont budge. I'm looking for a way to interpret the data so i can get them inside excel into a table format.

Its format is the following:

It starts twith the following header (which is irrelevant)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">

and then the data come as entries

XML:
<dict>
    <key>1.2</key>
    <dict>
        <key>BKBookmark</key>
        <array>
                        <dict>
                    <key>annotationAssetID</key>
                <string>21B8B42286D791898D90F97E6C9AF669</string>
                <key>annotationCreationDate</key>
                <integer>1374477130</integer>
                <key>annotationCreatorIdentifier</key>
                <string>com~apple~iBooks</string>
                <key>annotationDeleted</key>
                <integer>0</integer>
                <key>annotationIsUnderline</key>
                <integer>0</integer>
                <key>annotationLocation</key>
                <string>epubcfi(/6/16[id124]!/4/52/10/1)</string>
                <key>annotationModificationDate</key>
                <integer>1374477132</integer>
                <key>annotationStyle</key>
                <integer>3</integer>
                <key>annotationType</key>
                <integer>1</integer>
                <key>annotationUuid</key>
                <string>D7809B1F-21F7-4C8A-AE58-7C0C9EEAEC69</string>
                <key>futureProofing5</key>
                <string>2. The Revoker</string>
                <key>futureProofing6</key>
                <string>396169932.000000</string>
                <key>plAbsolutePhysicalLocation</key>
                <integer>0</integer>
                <key>plLocationRangeEnd</key>
                <integer>0</integer>
                <key>plLocationRangeStart</key>
                <integer>7</integer>
                <key>plUserData</key>
            </dict>

So this was one entry. Then inside another <dict></dict> comes the second entry

As you can in its entry theres <key> which should be the tables column name and <string> or <integer> where its value

So can some please help me into offering some kind of way that i can get my data into excel having such a file?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

therock003

Board Regular
Joined
Jan 9, 2008
Messages
183
I have already found this and it doesnt help, its a generic result for music playlists in itunes. My .plist file contains different information and cannot be exported in such way. I have scoured the internet for every available resource on the matter but nothing seems to be of use. It seems that there needs to be a sepcific translation of this data (VBA maybe, which i am not capable of writing) in order to get this going somehow
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,774
Office Version
  1. 365
Platform
  1. Windows
In that case I cannot help.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

could you post a link to the shared plist file with whole structure?
 

therock003

Board Regular
Joined
Jan 9, 2008
Messages
183
Been battling with this for the past hout, so it seems that plist format comes with a 2-step process. First inside the tags it mentions followed by a second set of tags that give its value. So for example we get

<key>annotationAssetID</key>
<string>21B8B42286D791898D90F97E6C9AF669</string>

Which based on the xml guidelines it should have been a one line entry like so

<annotationAssetID>21B8B42286D791898D90F97E6C9AF669</annotationAssetID>

So based on this one record i posted above, the whole entry becomes (skipped some definition that where useless to save me some time)

XML:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<dataset>
            <record>
                <annotationAssetID>21B8B42286D791898D90F97E6C9AF669</annotationAssetID>
                <annotationCreationDate>1374477130</annotationCreationDate>
                <annotationCreatorIdentifier>com~apple~iBooks</annotationCreatorIdentifier>
                <annotationLocation>epubcfi(/6/16[id124]!/4/52/10/1)</annotationLocation>
                <annotationUuid>D7809B1F-21F7-4C8A-AE58-7C0C9EEAEC69</annotationUuid>
                <futureProofing5>2. The Attraction</futureProofing5>
                <data>
                YnBsaXN0MDDWAQIDBAUGBwgMFx8gWWVuZE9mZnNldFVz
                dXBlcllzdGFydFBhdGhXZW5kUGF0aFVjbGFzc1tzdGFy
                dE9mZnNldCIAAAAA0gkFCgtXb3JkaW5hbBAHWkJLTG9j
                YXRpb26iDRTTDg8QERITWWNsYXNzTmFtZVVpbmRleFd0
                YWdOYW1lWWNhbGlicmUzOBAwUm9s0w4PEBUKFlljYWxp
                YnJlMTJTZGl2ohgc0w4PEBkaG1ljYWxpYnJlMzgQMFJv
                bNMODxAdCh5ZY2FsaWJyZTEyU2Rpdl5CS0VwdWJMb2Nh
                dGlvbiIAAAAACBUfJS83PUlOU1tdaGtyfIKKlJaZoKqu
                sbjCxMfO2NzrAAAAAAAAAQEAAAAAAAAAIQAAAAAAAAAA
                AAAAAAAAAPA=
                </data>
            </record>
                </dataset>

Which i imported on the powerquery editor and then succesfully got a table format version inside my excel file.

But i did this manually by cutting and pasting the definition inside the tags. So question now is, how do i do this for the 964 entries inside my xml, and get an appropriate xml file that plays ball with Excel.


could you post a link to the shared plist file with whole structure?

Rest of the file are more entries with same structure, it doesn matter.
 

therock003

Board Regular
Joined
Jan 9, 2008
Messages
183
To explain some more, plist seems to be an xml variant that apple uses but with a more strict database like structure. So something that would be simply written like this in xml

<name>Chris</name>
<lastname>Michael</lastname>
<age>28</age>

becomes weird looking like this

<key>name</key>
<string>Chris</string>
<key>lastname</key>
<string>Michael</string>
<key>age</key>
<integer>28</integer>

So its not a simple task of doing find and replace inside text editor, it needs something a little more complex, where it gets whats inside a key tag and makes it a tag and encapsulate the data entry that comes on the next line which is either a string or an integer
 

Watch MrExcel Video

Forum statistics

Threads
1,128,091
Messages
5,628,617
Members
416,329
Latest member
phxdan79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top