I need to convert excel file to XML file but keep getting an error "Cannot save or export XML data. The XML maps in this workbook are not exportable".

jodyreid
jodyreid used Ask the Experts™
on
I have an 2010 excel spreadsheet with 250 columns off data that i need to convert to XML file but keep getting an error "Cannot save or export XML data. The XML maps in this workbook are not exportable". Cannot find out why I keep getting this error. Can someone help me. I will upload the excel file and the schema XML file.
Please can someone help me with this problem. Thank you
Schema.xml
Test.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Did you already look for the cases that Excel can't handle? If not, take a look at the section called "The XML Maps in your workbook can't be exported" in this Microsoft article.  There are a few conditions for which it just can't be done.

https://support.office.com/en-us/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360?CorrelationId=b45b7815-c3b9-4a19-b53e-1601e462d362&ui=en-US&rs=en-US&ad=US
Michael FowlerSolutions Consultant

Commented:
Based on the previous questions you have asked the schema you trying to export to is not supported by excel.
An XML Map can't be exported if the mapped element’s relationship with other elements can't be preserved.
https://support.office.com/en-us/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360

Based on this the only way to get what you are after will be to a create a VBA script. To do this we would need a full copy of the excel spreadsheet (dummy data is fine) so we can see all the headers and how it will fit the schema supplied
jodyreidIT Manager

Author

Commented:
Ok, I uploaded to Schema.XML file and the excel worksheet file. Hope you can help with this.
Thank you.
3584-1st.xlsx
nhpbj_2_00_0.xml
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Michael FowlerSolutions Consultant

Commented:
OK I have made a start on this. So far it writes out the XML with the header. Will work on this further when I have some more time

I do have a couple of questions
Is it one XML per worksheet and so header information can be simply read from the first data row
No termination date column? Are we missing any columns in the example?
3584-1st.xlsm
jodyreidIT Manager

Author

Commented:
No we will be adding 250 people's data to the 1 XML file and we do not need a termination date column as we can logon to the program and enter that when needed. We have to upload 1 XML file every month with all employee's times for that month.
Thank you.
Michael FowlerSolutions Consultant

Commented:
I am doing some more work on this but need an example excel sheet so that I know what each row will look like. I do not need actual data but I do need to be able to see what exactly the format will be so could you please post a sheet with 20-30 rows with a couple of different employee numbers
Michael FowlerSolutions Consultant

Commented:
@jodyreid I am still happy to finish off this but still need a more complete example to work from as the original spreadsheet does not contain enough data for me to see how I need to code this. Please post a sheet with 20-30 rows of data.
jodyreidIT Manager

Author

Commented:
Thank you. Sorry i did not get back to you sooner but was in Ireland for a few days. Here is a sample
3584.xlsx
jodyreidIT Manager

Author

Commented:
Just checking on this problem again. I still need to up load this data by way of .XML file. Can anyone help with this.
Thank you.
jodyreidIT Manager

Author

Commented:
I've requested that this question be deleted for the following reason:

Did not get an answer
Michael FowlerSolutions Consultant

Commented:
@Jodyreid

Sorry got sent to KL for work. Will have some time thus weekend
jodyreidIT Manager

Author

Commented:
Thank you.
Michael FowlerSolutions Consultant

Commented:
Hi @Jodyreid

Please find attached the completed workbook

If you look into the script (ALT+F11) you will see a module named constants which allows you to easily edit options such as the columns and attribute values.

To start with you will need to update the FILE_PATH to point to an appropriate location to save the completed XML as well as choosing the XML file name. At the moment it is set to "C:\TestXML.xml" which may cause an "Access is denied" error on your machine
3584-1st.xlsm
TestXML.xml
jodyreidIT Manager

Author

Commented:
Thank you but it is Access is denied and I can not change any thing as it is read only.
Thank you.
Solutions Consultant
Commented:
Hi Jodyreid

The workbook has macros and so excel will commonly make these readonly. After opening the workbook use save as to make a copy (ensure read only is not checked), close Excel and then open this copy. When you open this copy you will normally be prompted to enable macros, you need to select yes to enable them
jodyreidIT Manager

Author

Commented:
Thank you. I will see if that works. If it does not can i have you add some comments to this question as i want to make sure you get the points  I have asked this before but can someone help with converting excel to XML
jodyreidIT Manager

Author

Commented:
Hi Michael74. It looks like it is almost there. It is working but the format for the dates keeps change it to mm/dd/yyyy and I need to keep them at yyyy-mm-dd. Is this some thing I can change to get this working. I also need to give you all the points for you help. Thank you.
jodyreidIT Manager

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial