Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Import xml to Access (from itunes) (2003 sr2)

    Dear Loungers

    I use iTunes to catalogue my cd's however as with all other Jukeboxes these days it is not extendable and does not support the additional information structures that I want. I am very experienced using MSACCESS to structure and organise data (but as a clever tool using forms, queries etc and virtually no code).
    iTunes database is held as an XML file so my idea is to import it into my more comprehensive Ms Access database. The drawback is I haven't a clue about xml beyond a rudimentary understanding. I have generated an XSD file. But I don't know what to do next.

    Has anyone done this already?

    thank you.................... liz

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import xml to Access (from itunes) (2003 sr2)

    <P ID="edit" class=small>(Edited by Jezza on 31-Dec-06 18:18. Add text about Excel that I had omitted)</P>Welcome back Liz

    We have been talking to your colleague Maggie recently <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    It appears you have good knowledge what with being able to develop XSD files the idea of doing imports to Access will be slightly more complicated and will need code to allow the import. However, if you have Access 2003 you can just do a straight import from an XML file into Excel, link it to Access and create your relationships there.
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import xml to Access (from itunes) (2003 sr2)

    Jerry,

    Margie is my sister!!! She has been helping me with lots of stuff and I am astonished at what she has managed to teach herself over Xmas - with a little help from you guys, so thank you.

    I tried the excel import. However it fails, first it complain there is no map and says it will create one and then having done this it fails and imports nothing except an "*" in the first field of the first row.
    What is the relationship, if any of the excel xml map and an xsd file?
    I read somewhere that Access only supports some xml tags and characteristics, behaps this is also true of Excel.

    I have attached the xsd file I generated in case there's a clue in the structure (it was generated using atrial version of Altova XMLSPY which looks pretty sound)

    many thanks............... liz

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import xml to Access (from itunes) (2003 sr2)

    Jerry,

    It occurs to me that it might be the way iTunes has structured it's xml - could they have "appl-ised" it? This is the beginning of the file:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
    <plist version="1.0">
    <dict>
    <key>Major Version</key><integer>1</integer>
    <key>Minor Version</key><integer>1</integer>
    <key>Application Version</key><string>7.0.2</string>
    <key>Features</key><integer>1</integer>
    <key>Show Content Ratings</key><true/>
    <key>Music Folder</key><string>file://localhost/F:/Music Library/Music/</string>
    <key>Library Persistent ID</key><string>A1BE41FF21C54708</string>
    <key>Tracks</key>
    <dict>
    <key>310</key>
    <dict>
    <key>Track ID</key><integer>310</integer>
    <key>Name</key><string>Union Civica</string>
    <key>Artist</key><string>Juan d'Arienzo</string>
    <key>Album</key><string>The Rhythm Of Juan D'Arienzo</string>
    <key>Genre</key><string>Tango</string>
    <key>Kind</key><string>WAV audio file</string>
    <key>Size</key><integer>26624684</integer>
    <key>Total Time</key><integer>150933</integer>
    <key>Track Number</key><integer>1</integer>
    <key>Track Count</key><integer>23</integer>
    <key>Date Modified</key><date>2006-11-25T09:37:31Z</date>
    <key>Date Added</key><date>2006-11-25T09:36:56Z</date>
    <key>Bit Rate</key><integer>1411</integer>
    <key>Sample Rate</key><integer>44100</integer>
    <key>Persistent ID</key><string>A1BE41FF21C5471E</string>
    <key>Track Type</key><string>File</string>
    <key>Location</key><string>file://localhost/F:/Music Library/Music/Juan d'Arienzo/The Rhythm Of Juan D'Arienzo/01 Union Civica.wav</string>


    I have searched the excel forum for some clues... perhpas this is really a thread that should live there?

    liz

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import xml to Access (from itunes) (2003 sr2)

    <P ID="edit" class=small>(Edited by Jezza on 31-Dec-06 18:55. Answered already about UTF-8 posts passed in the post)</P>Hi Liz

    Scratching my head a little as it sems OK

    Try validating your XML file with this:

    XML Schema Validator

    If the data is directly imported into Access you will get data that is grouped by Node but directly to Excel you get an unnormalised table.

    Can I also assume the XML is headed by <?xml version="1.0" encoding="UTF-8"?>? ...and all elements are closed? <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Just checking <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  6. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import xml to Access (from itunes) (2003 sr2)

    Jerry,

    I used Altova to test if the XML & XSD are "well formed" and it said yes. I am not sure what a "Node" is, I'm assuming this means a generic branch in the family tree? and when you say "closed" do you mean with an end tag on all the elements ? in which case yes they are.

    If I import into Access then it only seems to bring in four fields - why? see COMMENTS below:

    <dict>
    <key>310</key> I GET THIS - its the internal track id I think
    <dict> THIS LOOKS LIKE A CHILD COLLECTION TO ME and holds the attributes of track 310 I think
    <key>Track ID</key><integer>310</integer> PERHAPS I GET THIS instead of the above?
    <key>Name</key><string>Union Civica</string> NOT THIS
    <key>Artist</key><string>Juan d'Arienzo</string> NOT THIS
    <key>Album</key><string>The Rhythm Of Juan D'Arienzo</string> NOT THIS
    <key>Genre</key><string>Tango</string> NOT THIS
    <key>Kind</key><string>WAV audio file</string> NOT THIS
    <key>Size</key><integer>26624684</integer> NOT THIS
    <key>Total Time</key><integer>150933</integer> NOT THIS
    <key>Track Number</key><integer>1</integer> I GET THIS not sure what it is though!
    <key>Track Count</key><integer>23</integer> NOT THIS
    <key>Date Modified</key><date>2006-11-25T09:37:31Z</date> I GET EITHER THIS OR THE NEXT DATE&TIME
    <key>Date Added</key><date>2006-11-25T09:36:56Z</date> SEE ABOVE
    <key>Bit Rate</key><integer>1411</integer> NOT THIS
    <key>Sample Rate</key><integer>44100</integer> NOT THIS
    <key>Normalization</key><integer>836</integer> NOT THIS
    <key>Persistent ID</key><string>A1BE41FF21C5471E</string> NOT THIS
    <key>Track Type</key><string>File</string> NOT THIS
    <key>Location</key><string>file://localhost/F:/Music Library/Music/Juan d'Arienzo/The Rhythm Of Juan D'Arienzo/01 Union Civica.wav</string> I GET THIS
    <key>File Folder Count</key><integer>4</integer> NOT THIS
    <key>Library Folder Count</key><integer>1</integer> NOT THIS
    </dict>
    <key>311</key> AND SO ON

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import xml to Access (from itunes) (2003 sr2)

    Having a bit of difficulty as the entity description are:

    <!ENTITY % plistObject "(array | data | date | dict | real | integer | string | true | false )" >

    Which appear to map against your list OK, have you tried an import to Excel?
    Jerry

  8. #8
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Import xml to Access (from itunes) (2003 sr2)

    Jerry,

    I tried the excel import. However it fails, first it complain there is no map and says it will create one and then having done this it fails and imports nothing except an "*" in the first field of the first row.
    What is the relationship, if any of the excel xml map and an xsd file?

    I have an alternative approach and hat is to use Collectorz which is a piece of CD cataloguing software I also use. The only problem is no playlists so can't import them. Have just tried bringing that in and it works perfectly. So I may pursue this for now but would like to get to the bottom of the itunes issue - I can't be the only person in the world wanting to extend their music information and itunes is so prevalent now I felt sure someone must have done this already.

    Anyway for what we have so far, thank you, any other brainwaves would be gratefully received!

    liz

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import xml to Access (from itunes) (2003 sr2)

    Liz

    I am having a very boring evening in so I have been creating a little application for you in Access from some code I have borrowed and have got it to create tables so far, I just need some sample XML. Is there any chance you can send some to me, not your whole collection of course, just a cut down version.

    My public email is jezza@magicforest.co.uk once I test it and it works I will send it to you via the Lounge, I wanted to test before the lions checked it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import xml to Access (from itunes) (2003 sr2)

    Here is my starters for 10, I have tried to use your schema from above....it will need some work but creates a table and error table per your schema

    I hope that gets you started
    Jerry

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import xml to Access (from itunes) (2003 sr2)

    How do you import the file into Access?

    Do you realise a command called Application.ImportXML is available to import XML files into Access.

    As Jezza pointed out, an XML file you are trying to import would help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •