Um, firstly that xml contains personal information including names, addresses and emails, so unless it's dummy data ... anyone know if there's a way we can remove it from the list archives at least?

As to the question:
* Excel can import xml data (and once you can get it into Excel at all it should be possible to manipulate how you want) through Data > Get Data > From file > XML
* But it's very unhappy with this particular xml file. Looking at it in Notepad++ you need to move the first XML declaration (currently in line 2) to line 1 (above <root>), and then delete every other XML declaration.
* Then Excel will load it, but of course you need to help it expand out the data you want from all the nestings. When you get the option after loading it, click on Transform  data and you'll get to Power Query Editor.

At that point it's beyond me but there are probably tutorials online or you could play around with it until it submits, depend on your preference! Or someone may know of a friendlier tool.

Deborah

-----Original Message-----
From: Code for Libraries <[log in to unmask]> On Behalf Of Salisbury, Mackenzie
Sent: Thursday, 28 July 2022 10:09 am
To: [log in to unmask]
Subject: [CODE4LIB] Help with a xml to excel file?

Caution: This email originated from outside our organisation. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Hi all!
I am working on an alternative way to batch upload materials from ProQuest to Digital Commons. As some of you may know, they have a new feature "Batch Upload File Manager" that allows you to upload pdf's and then it generates an excel sheet with pdf urls (see attached example). The downside of this is that you then have to add all the corresponding metadata (title, author name, etc.)

I have a way to download the corresponding xml files and combined them all, but I don't have an easy way to transform the xml file to the formal of the excel file.

Does anyone know of an online converter or free software that can help me create this? Or alternatively, have a good macro guide? I know that all of this is possible, I am just way out of my league when it comes to skillset. Our IT Director left last year and I've been scrambling ever since!

I attached two file examples (the xml + the excel spreadsheet that I need it to align with)

Best,
Mackenzie

________________________________

"The contents of this e-mail (including any attachments) may be confidential and/or subject to copyright. Any unauthorised use, distribution, or copying of the contents is expressly prohibited. If you have received this e-mail in error, please advise the sender by return e-mail or telephone and then delete this e-mail together with all attachments from your system."