# Excel to XML File



## computerman29642 (Dec 4, 2007)

Does anyone know how to convert an Excel file to a XML file?


----------



## OBP (Mar 8, 2005)

Anything wrong with the Save As version?


----------



## computerman29642 (Dec 4, 2007)

I need the XML file in a certain format/schema. The 'Save As' does not allow me to do that format/schema.


----------



## OBP (Mar 8, 2005)

I am not sure that the VBA version allows that either.
Have you looked at the SaveAs in the VBA Editor Help?
it show XML as a FileFormat in the expression
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)


----------



## computerman29642 (Dec 4, 2007)

No, I have not looked at that. Would it help if I attached a file with the format I need?


----------



## OBP (Mar 8, 2005)

Not for me as Excel is not my strong point and I have never used XML, you could post it and hope Zack, Bomb, Rollin or one of the other Excel regulars comes along.


----------



## jimr381 (Jul 20, 2007)

So you do not have an XSD file setup already? XSD are schema or structure files. Think of it as the shell of the database. XSL are the style of the output.


----------



## computerman29642 (Dec 4, 2007)

Sounds good. Thanks for the suggestions.


----------



## computerman29642 (Dec 4, 2007)

Yes, I do have a XSD file.


----------



## jimr381 (Jul 20, 2007)

Please check out the previous post.


----------



## jimr381 (Jul 20, 2007)

OK do you know how to attach the XSD file to the Excel file to get it it's structure to save as?


----------



## jimr381 (Jul 20, 2007)

OK check this out. Like most applications I know a little bit about it, slightly enough to be deadly with it. Go to "Data">>"XML">>"XML Source" from the menu bar. Click on the "XML" maps button and then click "Add." Switch the file type up to XSD files and it should load a map for you. Then basically you assign data from your spreadsheet to the map.


----------



## computerman29642 (Dec 4, 2007)

Let me give it a go. Thanks.


----------



## computerman29642 (Dec 4, 2007)

How do you assign the data to the map?


----------



## computerman29642 (Dec 4, 2007)

I believe I have figured out how to assign the data. Thank you all for the help and suggestions.


----------



## jimr381 (Jul 20, 2007)

Not a problem anytime. I never use XML and it has been over a year since I taught how to manipulate it within Excel.


----------



## computerman29642 (Dec 4, 2007)

Is there a way to setup the excel spreadsheet with XSD file, and then save the excel file to add data later. When I try to do thsi, I keep getting the "XML data is not exportable".


----------



## jimr381 (Jul 20, 2007)

I want to say that you can still save it as an Excel spreadsheet with the attached schema. But you will have to have the schema go along with the excel file if you send it out to someone else. You are past my realm on this one. Probably past a lot of people's since not too many use XML within Excel.


----------



## computerman29642 (Dec 4, 2007)

It does not have to be in Excel. I am just trying to figure out how to get data in this specific format within XML. I agree....I know I am out of my realm.


----------



## jimr381 (Jul 20, 2007)

Why does it have to be in an XML format?


----------



## computerman29642 (Dec 4, 2007)

That is the format the client wants the data in.


----------



## jimr381 (Jul 20, 2007)

Gotcha. Once you attached the schema and assigned the fields to the you should been able to export it as XML right?


----------



## computerman29642 (Dec 4, 2007)

I click the "Verify Map for Export" within the 'XML Source' window, and I get an error message that the data is not exportable. I have no clue as to what that means. It gives a list of things that are wrong....

List of lists
Denormalized data
A mapped element's relationship with other elements cannot be preserved

I have no idea what those mean either.


----------



## jimr381 (Jul 20, 2007)

Did you select the data and assign it individually to the fields within the Schema.


----------



## computerman29642 (Dec 4, 2007)

I dragged the schema field to each column header in the excel spreadsheet.


----------



## jimr381 (Jul 20, 2007)

Do you mind posting or emailing me the files that you are working with so I can take a look at them. This probably will not happen until tomorrow though. I can take fake as long as I have the schema to work with.


----------



## jimr381 (Jul 20, 2007)

Was there supposed to be an Excel file in there as well?


----------



## computerman29642 (Dec 4, 2007)

I have attached the Excel file.


----------



## jimr381 (Jul 20, 2007)

I just did the export and it worked fine for me. You need to make sure you map the fields from the left to the right.


----------



## computerman29642 (Dec 4, 2007)

Could you attach the files? I did do the fields in Excel from left to right.

You were able to save the Excel file as an XML Data file?


----------



## jimr381 (Jul 20, 2007)

Ok I will attach them, I am just a little caught up in work at the moment.


----------



## computerman29642 (Dec 4, 2007)

That's fine. I just want to see what you did differently.


----------



## slurpee55 (Oct 20, 2004)

Just to give some feedback - I had to convert Excel data to XML a lot previously (thanks goodness that contract expired!) and found the Save As option practically useless, for MS insists on adding a lot of junk to the schema.
However, once I had it built (and rebuilt and rebuilt - the customer kept changing the specs) I found Excel a very easy program for this task - just apply the schema to the data.


----------



## computerman29642 (Dec 4, 2007)

What do you mean by apply the schema to the data? I have tried many things, but I keep geeting the error "The data is not exportable".


----------



## jimr381 (Jul 20, 2007)

Send me a mail message with your email address and I will e-mail the data to you.


----------



## computerman29642 (Dec 4, 2007)

I have sent my email through a PM.


----------



## jimr381 (Jul 20, 2007)

Can you post for me what fields marry to what from the XSD to the Excel file please? I randomly mapped what I thought was right which worked, but I want to verify before I send you anything.


----------



## computerman29642 (Dec 4, 2007)

I really wish I could do that for you. I am reading through a 60 page document now trying to match the fields together. That is why I was hoping that I would be able to just move the fields from the XSD to an empty Excel file, and just copy/paste when it is time to send the data.


----------



## jimr381 (Jul 20, 2007)

The excel file that I have now has certain fields setup. I just need to know what the columns are called in Excel and what they match up to in the XSD file.


----------



## computerman29642 (Dec 4, 2007)

I am not fully sure right now. The Excel file I attached is not a full file. I will end up needing all the fields within the XSD file. I am not sure right now all of the fields in Excel that match the fields from the XSD. I am reading through a document from the client trying to figure that out now.


----------



## jimr381 (Jul 20, 2007)

OK after you open up the Schema you will want to drag the fields from the pane on the right to the corresponding Excel field on the left. You will want to work from the left to the right in the Excel spreadsheet and then click on the "Export to XML" on the XML toolbar.

If you see multiple blue boxes around the data then it did not group them together well, so you will want to do it again.


----------



## computerman29642 (Dec 4, 2007)

Does every field in the XSD have to be used within a group? I will give that a shot.


----------



## computerman29642 (Dec 4, 2007)

I followed your instructions, but when I clicked the export option from the XML menu, I got the attached error message.


----------



## jimr381 (Jul 20, 2007)

Can you give me a screenshot or email me one with what the data looks like when you have assigned the fields to it. There should be one big blue box surrounding all the fields after you assign them to the Excel data.


----------



## computerman29642 (Dec 4, 2007)

I have attached a screenshot as you requested.


----------



## computerman29642 (Dec 4, 2007)

Was the screenshot helpful?


----------



## jimr381 (Jul 20, 2007)

Not really. I forgot to post this morning. Can you give me the exact steps you are doing? I do not see why you are having issues and I am not when I load the schema.


----------



## computerman29642 (Dec 4, 2007)

I am opening the excel file that holds the data. 
I then go to the XML option under the Data Menu, and select XML Source. 
I add a new source by the schema. 
Once the schema has been added, I drag the fields from the XML source to the corresponding fields on the Excel spreadsheet. 
The blue outline box borders all the fields on the Excel sheet, but when I try to export the data I get the "Data not Exportable" error message.


----------



## jimr381 (Jul 20, 2007)

and you are clicking on the "Export to XML" via the button on the toolbar? Also did you try messing with the fields maybe you are trying to place text data in a numeric field.


----------



## computerman29642 (Dec 4, 2007)

Yes, I am using the button on the toolbar. I will double check the text data in a numeric field.


----------



## computerman29642 (Dec 4, 2007)

> Also did you try messing with the fields maybe you are trying to place text data in a numeric field.


I tested this possibility, but I am still unable to export the data.


----------



## computerman29642 (Dec 4, 2007)

Here is what I noticed. When I go to assign the XML Source field for the Date field on the Excel spreadsheet, that is when the export data stops working. 

What field from the XML source did you assign to the date field on the Excel spreadsheet?


----------



## jimr381 (Jul 20, 2007)

The one that says date on the end of the time. There is a time field adjacent to it as well.


----------



## computerman29642 (Dec 4, 2007)

I believe I know why you can get it to work and I cannot. It appears that you selected the fields from the XML Source all from the same group (Ex: SampleIdentification). I am trying to use fields from different groups.


----------



## jimr381 (Jul 20, 2007)

I thought of the groups as tables and wanted to make sure I used that same section. Is that not the case?


----------



## computerman29642 (Dec 4, 2007)

The data in the Excel file could corresponnd with the fields in the XML Source from different groups/sections.


----------



## jimr381 (Jul 20, 2007)

Well hopefully we will get someone to mosey on over that actually does XML programming


----------



## computerman29642 (Dec 4, 2007)

I hope. I have not used XML before at all, so this is a completely new adventure for me.


----------



## jimr381 (Jul 20, 2007)

Aye it does not seem like people are beating down the door to answer the post hehe.


----------



## computerman29642 (Dec 4, 2007)

LOL! Looks like I may be up a creek without a paddle.


----------



## computerman29642 (Dec 4, 2007)

I have determined that the errors that are occurring are coming from the repeating objects/data. If I eliminate the repeating objects/data, then I am able to export the Excel data to XML just fine.


----------



## slurpee55 (Oct 20, 2004)

You should be able to have repeating data, just not repeating field names unless they are organized as subsets (e.g. <company name> could repeat under the set of <clients>). If it was a listing of transactions, the same company name could show up multiple times. Or your company name could show in every set.


----------



## computerman29642 (Dec 4, 2007)

Right now I have a design of something like this.....

<Test1>
<CompanyName>ME<CompanyName/>
<Company Type>Resell<CompanyType/>
</Test1>

<Test2>
<Client>
<CompanyName/>
<CompanyType/>
</Client>
</Test2>


----------



## slurpee55 (Oct 20, 2004)

You should have a standard pattern across your tests, so that even if there is no data for <client>, insert "None" in there.


----------



## computerman29642 (Dec 4, 2007)

slurpee55 said:


> You should have a standard pattern across your tests, so that even if there is no data for <client>, insert "None" in there.


Would I just type "None" in the start(<) and end (/>) brackets?


----------



## computerman29642 (Dec 4, 2007)

If I attach a sample file, would you see if you could get it to work properly in Excel?


----------



## slurpee55 (Oct 20, 2004)

Be glad to try...


----------



## computerman29642 (Dec 4, 2007)

I have attached a sample file. I really appreciate the help. I have been working on this for weeks.

For some reason, I am unable to get the file to upload.


----------



## slurpee55 (Oct 20, 2004)

try zipping it....


----------



## computerman29642 (Dec 4, 2007)

I tried zipping the file. I keep getting an "Upload of file" error.


----------



## slurpee55 (Oct 20, 2004)

Hmmm, send me an email via my profile and I will respond...


----------



## computerman29642 (Dec 4, 2007)

Email has been sent.


----------



## slurpee55 (Oct 20, 2004)

and replied to!


----------



## computerman29642 (Dec 4, 2007)

I have determined that it is the repeating objects/data that is causing the errors in Excel. I keep getting the follwoing two errors....



> "List of lists"





> "A mapped element's relationship with other elements cannot be preserved."


Does anyone know how to fix these errors?


----------

