8
$\begingroup$

I am trying to generate an Excel-compatible XML file from Mathematica. First, I created a test XML file by saving a Microsoft Excel workbook as "XML Table". Then I Imported this file:

XMLdata = Import["test.xml", "IncludeNamespaces" -> True]

Now I am Exporting this back to XML:

Export["test_exported.xml", XMLdata, "AttributeQuoting" -> "\""]

The generated file cannot be opened by Excel. Comparing it with the original file shows that most of namespace prefixes are dropped although they were present in XMLdata. Why does this happen? How can I correctly Export an XML file which will be compatible with Excel?

Improve this question
$\endgroup$
8
  • 2
    $\begingroup$ Could you provide more details about the XML export out of Excel? I see two options there, save as Spreadsheet 2003 XML and save as Data XML. The simple spreadsheet that I tried can only be save using the former and not the latter format (Excel complains about missing XML mappings). I can't get Mathematica to import the spreadheet 2003 XML file using your Import line. I can import it as text and then importing that using ImportString. $\endgroup$
    – Sjoerd C. de Vries
    Commented Jul 3, 2012 at 19:57
  • 1
    $\begingroup$ The simple spreadsheet wasn't simple enough. It contained a few names and formulas. If I simplify it so that it only contains a 2x3 set of numbers, it imports fine, but the exported version cannot be read by Excel. I either get errors or Excel just hangs depending on the options I choose. $\endgroup$
    – Sjoerd C. de Vries
    Commented Jul 3, 2012 at 20:12
  • $\begingroup$ @Sjoerd I use Excel 2003 and save a very simple spreadsheet (containing only one cell with number formatted as "Text") as "XML Table". On my system (Windows XP 32 bit, Mathematica 8.0.4) the file is imported without errors. I think of this file as of a template for generating such XML tables by Mathematica for Excel. $\endgroup$
    – Alexey Popkov
    Commented Jul 4, 2012 at 4:53
  • 1
    $\begingroup$ I'm using Excel 2010 and don't see that XML option. Do you have reasons not to export from mma using Excel's native xls format? $\endgroup$
    – Sjoerd C. de Vries
    Commented Jul 4, 2012 at 6:07
  • $\begingroup$ @Sjoerd I think that "Spreadsheet 2003 XML" is the same as my "XML Table" (I use localized version of MS Office and it is backward translation). The reason to use XML format is explained in the UPDATE section here. $\endgroup$
    – Alexey Popkov
    Commented Jul 4, 2012 at 6:19

1 Answer 1

Reset to default
7
$\begingroup$

I have found the solution. From the Documentation,

Some documents use names in a non-namespace-compliant fashion, because the XML namespace recommendation, which extends XML, was made after the initial XML recommendation. "IncludeNamespaces"->"Unparsed" is provided to allow parsing of these documents. The name is always represented as the exact single string that appears in the XML file. Unless absolutely necessary, this option value should not be used.

With the option "IncludeNamespaces"->"Unparsed" everything is nice and even the "AttributeQuoting" -> "\"" option is not needed:

XMLData = Import["test.xml", "IncludeNamespaces" -> "Unparsed"];
Export["test-from MMa.xml", XMLData]

The exported file is opened by Excel without warnings.

Improve this answer
$\endgroup$
3
  • $\begingroup$ I'm glad you found a solution. Thanks for sharing it! $\endgroup$
    – Mr.Wizard
    Commented Aug 18, 2012 at 5:19
  • $\begingroup$ The final file format is a XML file that Excel can open. I tryed to rename it to XLS or XLSX with no success (Excel can't recognize it as a XLS file). Do you know some way to convert this XML to a XLSX? Or some change in the structure of XML that can make it recognized? I noted that XLSX is a Zip file with a lot of XML, but no success in figure out how to transform the XML into XLSX. For me it's necessary for file distribution. Tks $\endgroup$
    – Murta
    Commented Jan 5, 2014 at 14:55
  • $\begingroup$ @Murta XML is not identical to XLS but AFAICT it is equivalent: XML is a textual format while XLS is binary format. Probably only Excel can convert XML to XLS or XLSX. But I do not think that it is a big problem since on my machine the "XML Table" files saved from Excel are automatically opened by it when I double-click on them in Windows Explorer. $\endgroup$
    – Alexey Popkov
    Commented Jan 6, 2014 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.