It is important to understand how XML Schema Definition (XSD) data types are handled when you import or export XML data, so that the data is appropriately converted according to your needs.
What do you want to do?
Learn how Excel handles XSD data types when you import XML data
Important: If an XML schema file (.xsd) does not define a specific data type attribute for an element (such as xsd:decimal), then Excel formats the cell as text by default when the XML data is imported. Formatting as text ensures that the characters stored in the cell are exactly the same as the data that is stored in the XML file (.xml). For example, leading zero (0) values in an ID or credit card field are removed when they are formatted as a number, but not removed when they are formatted as text. However, data that is formatted as text does not evaluate. If you want the data to evaluate because it contains a formula, you must explicitly provide a numeric data type attribute, such as xsd:decimal or xsd:integer.
The following table lists the display formats that are applied when an item with a particular XSD data type is imported into an Excel worksheet. Data with an XSD format listed in the Unsupported formats column are imported as text values.
|
XSD data type |
Excel display format |
Unsupported formats |
|---|---|---|
|
time |
h:mm:ss |
hh:mm:ssZ Hh:mm:ss.f-f |
|
dateTime |
m/d/yyyy h:mm |
yyyy-mm-ddThh:mm:ssZ yyyy-mm-ddThh:mm:ss+/-hh:mm yyyy-mm-ddThh:mm:ss.f-f Years outside of the range 1900 to 9999 |
|
date |
Date *3/14/2001 |
yyyy-mm-ddZ yyyy-mm-dd+/-hh:mm Years outside of the range 1900 to 9999 |
|
gYear |
Number, no decimals |
yyyy+/-hh:mm Years outside of the range 1900 to 9999 |
|
gDay gMonth |
Number, no decimals |
|
|
gYearMonth |
Custom mmm-yy |
yyyy-mm+/-hh:mm Years outside of the range 1900 to 9999 |
|
gMonthDay |
Custom d-mmm |
|
|
anytype anyURI base64Binary duration ENTITIES ENTITY hexBinary ID IDREF IDREFS language Name NCName NMTOKEN NMTOKENS normalizedString NOTATION QName string token |
Text |
|
|
boolean |
Boolean |
|
|
decimal float double |
General |
Leading and trailing zeros (0) are dropped. Negative (-) and positive (+) signs are respected, although only negative signs are displayed. Excel stores and calculates with 15 significant digits of precision. |
|
byte int integer long negativeInteger nonNegativeInteger nonPositiveInteger positiveInteger short unsignedByte unsignedInt unsignedLong unsignedShort |
General |
Learn how Excel display formats map to XSD data types when you export XML data
When you export XML data, the exported data will match the data that is displayed in the worksheet, under the following circumstances:
-
The Excel display format is Text.
-
You have not changed the display format of the data.
If you change the display format of a cell that has numeric, date, or time data, then the underlying value of the cell is exported. For example, if you apply a Percentage display format to a cell that results in the display of 51.50%, the cell's value will be exported as .515.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
