将XML中的数据字段提取到Excel中 [英] Extract data fields from XML into Excel

问题描述

我有一个巨大的Excel电子表格，其中包含客户记录，其中每一列都是一个字段.有一个名为人口统计"的字段，其中包含客户"的调查结果，并且完全采用XML格式.也就是说，每个客户都有其XML格式的人口统计信息(如性别，婚姻状况，收入，年龄等)的调查结果.值得注意的是，整个XML就像电子表格单元格中的一大堆文本一样，我无法用来分析数据.

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.

现在的问题是我想提取每个客户的人口统计数据，并将其作为字段显示在同一电子表格中，即每个客户都有一个年龄，性别等.我该怎么做?我已经尝试过使用Google，但是似乎没有人遇到同样的问题.我正在使用Excel 2007/2010.

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.

推荐答案

如果XML具有这样的扁平结构:

If the XML has a flat structure like this:

您可以将"XML"列转换为XML文件，然后将其重新导入到Excel工作表中.为此，只需

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

复制和将XML列粘贴到文本编辑器中，

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>

，另存为customers.xml.

现在.使用Developer Tools功能区中的XML-> Import将其导入回excel.基于扁平" XML结构，Excel将为单个值创建列:

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:

当然，只有在XML结构合适的情况下，此方法才有效，但如果合适，则无需手动编写宏或解析"XML"文本内容即可.

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.

这篇关于将XML中的数据字段提取到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！