Forum Discussion
Solved
Editting a XML file in Excel, then exporting it back as XML
Is it possible to recover the original XML file after importing it to Excel, even if no changes are made to it?
If I right click the table generated after importing it and then go to XML -> Export... I get the error "Cannot save or export XML data. The XML maps in this workbook are not exportable".
Google suggests that some valid XML files can't be exported by Excel. I may be missing something, since I wasn't able to export even this sample XML below, which I took from Excel internal help page.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <Root> <EmployeeInfo> <Name>Jane Winston</Name> <Date>2001-01-01</Date> <Code>0001</Code> </EmployeeInfo> <ExpenseItem> <Date>2001-01-01</Date> <Description>Airfare</Description> <Amount>500.34</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-01</Date> <Description>Hotel</Description> <Amount>200</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-01</Date> <Description>Taxi Fare</Description> <Amount>100.00</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-01</Date> <Description>Long Distance Phone Charges</Description> <Amount>57.89</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-01</Date> <Description>Food</Description> <Amount>82.19</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-02</Date> <Description>Food</Description> <Amount>17.89</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-02</Date> <Description>Personal Items</Description> <Amount>32.54</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-03</Date> <Description>Taxi Fare</Description> <Amount>75.00</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-03</Date> <Description>Food</Description> <Amount>36.45</Amount> </ExpenseItem> <ExpenseItem> <Date>2001-01-03</Date> <Description>New Suit</Description> <Amount>750.00</Amount> </ExpenseItem> </Root>
Hi Cajuf,
thanks for your answer and the update.
Even if you map the table columns to an XML schema, Excel might still have trouble exporting it back as XML. You might see the "Cannot save or export XML data. The XML maps in this workbook are not exportable" error pop up. Basically, Excel has some limitations when dealing with certain XML structures, and it might not play nice during the export process.
Map XML elements to cells in an XML Map - Microsoft SupportUsing a valid XML schema to map the table columns helps define the XML's structure and data types, but it doesn't guarantee smooth sailing with Excel's export. If the XML file is a bit too complex for Excel's taste, you could run into this error.
So, to be safe and keep the original XML intact, it's best to use a special XML editor or a text editor with full XML support.
Excel can still be handy for analyzing and visualizing data, but for serious XML work, it's better to use XML dedicated editors.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
Hi Cajuf,
When you import an XML file into Excel and then export it back without making any changes, Excel can modify the XML structure during the import process. This can make differences between the original file and the exported one, even if you didn't edit anything.
Excel's XML export feature is designed to handle specific types of structured XML data. If the XML file contains complex structures or features that Excel doesn't fully support, it might not be exportable using Excel.
In your case, the XML file you provided has a simple hierarchical structure, which should be manageable for Excel during import and export. But due to Excel's limitations in handling certain XML structures, the exported XML may not exactly match the original file.
To avoid any unintended changes to the XML structure, especially if you frequently work with XML files, it's best to use a dedicated XML editor or a text editor that fully supports XML. These specialized tools will preserve the integrity of the XML file and ensure that the structure remains unchanged during editing and saving (popular XML editors include XMLSpy, Notepad++, and Visual Studio Code with XML extensions.).
Overview of XML in Excel - Microsoft SupportPlease click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
- Thanks for your answer. The limitations you mentioned hold even if the table columns are mapped to a XML schema? The help page also provides the schema for that XML. Even so, I wan't able to export it.
- recover the original XML file after importing it to Excel,
which kind of Excel do you want？
How you map the fields?
EmployeeInfo and ExpenseItem are different types.
why do you need to import to Excel.and export to xml.again?
