XML Schema Definition (XSD) data type support

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

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×