Forum Discussion

Copper Contributor

Jul 20, 2023

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 -> Expo...

developer

excel

LeonPavesic
Jul 20, 2023
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 Support

Using 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

Cajuf

Copper Contributor

Jul 20, 2023

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.

peiyezhu

Bronze Contributor

Jul 20, 2023

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?

Cajuf

Copper Contributor

Jul 20, 2023

Need to edit values for several elements. The new values are in a workbook. So being able to edit it using excel formulas and export again to XML would be the most user friendly way of doing this. But another user made it clear that isn't possible or safe do to so.

peiyezhu

Bronze Contributor

Jul 21, 2023

grp detail

8	2001-01-01	Airfare	500.34
13	2001-01-01	Hotel	200
18	2001-01-01	Taxi Fare	100.00
23	2001-01-01	Long Distance Phone Charges	57.89
28	2001-01-01	Food	82.19
33	2001-01-02	Food	17.89
38	2001-01-02	Personal Items	32.54
43	2001-01-03	Taxi Fare	75.00
48	2001-01-03	Food	36.45
53	2001-01-03	New Suit	750.00
	Jane Winston	2001-01-01	0001

 create temp table aa as 
select fillna(iif(f01 like '%<ExpenseItem>%',RowID,'')) grp,iif(f01 like '%<ExpenseItem>%',RowID,'') grp2,regexp2('>([^>]+)<\/',f01,1) val,udf_htmlentities(f01)  from ConvertXMLToTable;
select grp,group_concat(val,'</td><td>') detail from aa where val!='' group by grp;
//where grp2!='';

How about using sql to convert XML to Sheet?

Cajuf
Copper Contributor
Jul 21, 2023
The question is how to get back to the XML after making changes to it.