XML Schema Definition (XSD) data type support - Excel

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

XML Schema Definition (XSD) data type support

Applies to: Microsoft Office Excel 2007

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

Learn how Excel display formats map to XSD data types when you export XML data

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 Microsoft Office Excel 2007 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 is 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

Top of Page

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.

Top of Page