Excel：映射的XML数据和“存储为文本的数字”错误

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

我的一位同事有一个XML文档,她在Excel 2007中打开,使用“获取外部数据/从XML数据导入”。

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

Excel会自动将XML映射到列。但是,有些元素存储数值数据,Excel拒绝将其视为数字:

<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?

目前没有XML Schema附加到文档,如果可能的话,我想避免创建一个。还有哪些其他选择?

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

P.S。:尝试不同的区域设置是不成功的。数据似乎总是被视为文本。

4 个解决方案

#1

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.

当您说“当前没有XML架构附加到文档”时,您不太正确。虽然大部分时间都是由Excel隐式创建的,但总有一个XML Schema。在提取和调整此Schema时 - 这并不太难 - 您可能会得到最简单的结果。

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

提示如何执行此操作(在此示例中,问题是日期字段):从Excel导出XML并保留日期格式

#2

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.

我相信默认情况下单元格格式适用于整个列,因此可能在您的示例中存储文本或数字的某些实例,但在“1.5”的配额中,因此默认文本格式。

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

当我测试XML数据导入时,它会将所有文本列格式化为文本,将数字列格式化为常规

#3

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

那你可以选择单元格,右键单击,格式化单元格,将格式类型更改为“数字”

EDIT

Using this test XML File

使用此测试XML文件

<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

由于初始列中的文本,所有列都显示为数字,但后两个SOME_NUMBER除外

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

格式单元格似乎有点愚蠢,因为它不会将格式应用于有错误的单元格,直到您单击进入单元格

#4

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

#1