CODEWITHSUNDEEP
xmlexcel
Tomalak
Tomalak

Reputation: 338228

Excel: mapped XML data and the "number stored as text" error

A colleague of mine has an XML document which she opens in Excel 2007, using "Get External Data / From XML Data Import".

Excel maps the XML to columns automatically. However, some elements store numerical data, which Excel refuses to treat as a number:

<SOME_NUMBER>68.12</SOME_NUMBER>

Instead, the cell shows the "number stored as text" error.

Currently no XML Schema is attached to the document, and I would want to avoid creating one, if possible. What other options are there?

P.S.: Trying different regional settings was unsuccessful. Data always seems to be viewed as text.

Upvotes: 3

Views: 14244

Answers (4)

Andreas J
Andreas J

Reputation: 546

Just saw this (old) question and thought I might still reply:

When you say "Currently no XML Schema is attached to the document" you are not quite correct. There is always an XML Schema although most of the time implicitly created for you by Excel. In extracting and adapting this Schema -- this is not too difficult -- you might get the easiest result.

A hint on how to do this (in this example the problem was a date-field): Exporting XML from Excel and Keeping Date Format

Upvotes: 2

praxeo
praxeo

Reputation:

I feel your pain, I've had the same problem for years. This is the only solution I've came up with.

Function ConvertText2Num(RangeToConvert As Range)
    RangeToConvert.ClearFormats
    RangeToConvert.TextToColumns
End Function

Upvotes: 0

Eoin Campbell
Eoin Campbell

Reputation: 44268

Well you can just select the cells, right click, format cells, Change the format type to "Number"

EDIT

Using this test XML File

<NODES>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>ASDF</SOME_NUMBER>
</A>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>4.56</SOME_NUMBER>
</A>
<A>
   <B>1.23</B>
   <C>2.34</C>
   <D>3.45</D>
   <SOME_NUMBER>4.56</SOME_NUMBER>
</A>
</NODES>

All columns appear as numbers with the exception of the second two SOME_NUMBER's because of the text in the initial column

So I select those two cells, high-light the error box (exclamation sign) and choose the second option "Convert To Number"

The format cells thing seems to be a bit stupid in that it won't apply the formatting to a cell with an error on it, until you click into the cell

Upvotes: 0

kristof
kristof

Reputation: 53834

I believe that the cell formatting by default applies for the whole column, so perhaps in your example some instances of store text or number but in quotas like "1.5" so the default text formatting.

When I tested the XML Data Import it would format all text columns to text, and numeric columns to general

Upvotes: 0

Related Questions