CODEWITHSUNDEEP
xmlexcel
Flynn
Flynn

Reputation: 193

Importing XML data to excel

I was trying to create a roster for a game using exported data from the game for a spreadsheet. Problem is that when excel imports the XML data, it won't let me use a single coma delimited field for all the data. Here is my example.

Roster.xml

<?xml version="1.0" encoding="UTF-8"?>
<Document>
  <N T="t" K="tRosterData">
    <N F="1" T="s" V="Realm, Guild, , Name1, 39, Member, Stalker, Soldier, Online, 0" />
    <N F="2" T="s" V="Realm, Guild, , Name2, 3, Cupcake, Stalker, Soldier, 1 week ago, 9.0911350250244" />
    <N F="3" T="s" V="Realm, Guild, Lvl 50, Name3, 50, Cupcake, Spellslinger, Explorer, 13 hours ago, 0.54366040229797" />
  </N>
  <N K="tHeaders" T="t">
    <N F="1" T="s" V="Server,Guild,Forum Name,Player Name,Level,Rank,Class,Path,Last Online,Days Offline" />
  </N>
  <N K="strPlayerName" T="s" V="Leader"/>
  <N K="nTimeLastExported" T="n" V="1402336371"/>
  <N K="strGuild" T="s" V="Guild"/>
</Document>

So I'm trying to import that XML to my spreadsheet, but it's import wont let me just use the coma delimited variable. Anyone know how I can just import the variables labeled "V"? I want to have the header with the server, guild, etc, and then have each attribute under that, but in separate cells with one player entry per row.

Upvotes: 1

Views: 912

Answers (1)

Mark Balhoff
Mark Balhoff

Reputation: 2356

Copy your XML text. Open Excel. Paste into Excel. When I do that, I see all the data in Column A (Range A1:A14). Use the Excel Text to Columns function (on the Data tab) with delimiters of Tab, Space and an Equals sign. Make sure "Treat consecutive delimiters as one" is checked and Text qualifier is a double quote character. All of your V variables now show up in column H for me.

Upvotes: 2

Related Questions