6

I have a huge Excel spreadsheet that contains records of Customers where each column is a field. There's a field called Demographics which contains survey results of Customers and it's entirely in XML format. That is each Customer has a survey result on their demographic info like Gender, Marital Status, Income, Age, etc. which is given in XML format. It is notable that the whole XML is like a big chunk of text in a cell of the spreadsheet which I can't use to analyze the data.

The problem is now I want to extract the demographic data of each Customer and present it as fields in the same spreadsheet, i.e., each Customer has an Age, Gender, etc. How can I do this? I have tried to Google this but it seems like nobody has the same problem. I'm using Excel 2007/2010.

Improve this question
2
  • Can you think of writing macro that might be able to run XPAT query on the XML and put it in some cell? Commented Mar 29, 2012 at 12:20
  • Have you looked at writing your own XSLT stylesheet? XSLT stylesheets are the standard for transforming XML documents and it would be straight forward to write an XPATH statement in the stylesheet to extract what you want. Commented Mar 29, 2012 at 13:30

3 Answers 3

Reset to default
8

If the XML has a flat structure like this:

enter image description here

you could just convert the "XML" column into an XML file and re-import it into your excel sheet. To do so, just

copy & paste the XML column into a text editor,

<customer><age>34</age><gender>m</gender></customer>
<customer><age>38</age><gender>f</gender></customer>

add a opening/closing root element,

<customers>
    <customer><age>34</age><gender>m</gender></customer>
    <customer><age>38</age><gender>f</gender></customer>
</customers>

and save as customers.xml.

Now. import it back into excel, using XML-> Import from the Developer Tools Ribbon. Based on the "flat" XML structure, Excel will create columns for the single values:

enter image description here

Of course this will work only if the XML structure is suitable, but if it does, it works without writing a macro or parsing the "XML" text content manually.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Open one of the xml form - go to file menu - share - export to excel - follow the instructions. In the export to excel you will find that, you can add all the xml forms if they are identical and export the data into one excel file in one go.

Improve this answer

Comments

-1

name="pref_allow_the_use_of_shortcut" value="false"/> vi {"locale":"vi","tags":[{"searchterm":"xin lỗi","path":"https://api.tenor.com/v1/search?

Improve this answer

1 Comment

Please don't put paste code without any context. Explain what your answer does and how it would solve the problem.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.