XML -> Excel Data Formatting

Question

I shall start by listing my XML structure.

<root>
  <person>
    <birthday>
      03/24/1989
    </birthday>
    <money>
      $24.00
    </money>
  </person>
  <person>
    <birthday>
      03/24/1989
    </birthday>
    <money>
      $24.00
    </money>
  </person>
</root>

When I import this into excel, all the birthdays and money values are formatted as text. I want to be able to apply filters to my table, such as birthdays between x & y or money values less than $13.00. I can select a column and set the formatting, but it still doesn't enable the proper filter options.

Is there a way to make XML/Excel import data as anything other than text? If not, is there a way to easily format the text to the desired format?

Select the column and choose Format cells once it's imported. There's no easier way to format the text. — Ken White
– Ken White, Commented Jan 11, 2016 at 17:24

JustAPup · Accepted Answer · 2016-01-11 17:25:05Z

0

If your XML file follows a particular and well-defined XSD (i.e., birthday as date, and money as currency), I think that will automatically take care of the correct data import. I try opening an XML file that I have with Excel, and everything is as should be.

Other than that, formatting the columns seems to be the way to go. I'm not sure what you mean by easily formatting, so maybe it will help if you can clarify a little bit more.

answered Jan 11, 2016 at 17:25

JustAPup

1,79013 silver badges19 bronze badges

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

2 Comments

Neve12ende12 Over a year ago

By easily formatting, I mean the method described by Ken White in the comment above. However, doing what he has described does not apply the formatting.

JustAPup Over a year ago

Please try: import the data, then format the data (note: DO NOT format the headers), then apply filters

Collectives™ on Stack Overflow

XML -> Excel Data Formatting

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related