Excel to XML

[smackababy]

I know this isn't exactly programming, but it is of a more technical nature and I figured this was the best place. If not, please move it to the appropriate section (hopefully, not the deleted section =3 )

I am having trouble getting an Excel document formatted properly into an XML. Now, I've done this before, but this is particularly difficult. The trouble I am having (besides Excel being terrible) is that the data is line data from a mainframe. How I got chosen to do this was literally "Hey, you're a developer and know XML! Do this please." It has to be in a very specific format to upload to a program and I've created schemas for the rest of the objects, but this particular one is giving me trouble.

The XML has to be in this format:

<root>
        <report name="report1">
             <permission user="user1" />
             <permission user="user2" />
        </report>
        <report name="report2">
             <permission user="user1" />
             <permission user="user3" />
        </report>
</root>

The issue is my data is returned as such:

[Report Name]        [User]
report1                  user1
report1                  user2
report2                  user1
report2                  user3

Now, there is some other information, but those are the fields I am having trouble with. Any ideas how to format this Excel to make mapping to XML easier? Or a special way to do the XML schema? The data is around 55 thousand lines, and that isn't a full export. Some reports have hundreds of IDs and some have one or two.

Thanks in advance.

 

[Ken g6]

Some reports have hundreds of IDs and some have one or two.

Are you saying some reports are nested hundreds deep, or just that some have hundreds of different names?

If it's just the latter, and there are always just two columns, I'd do a simple formula with an if to prepend the report tag. Something like:

=if(A47==A46,"","</report><report name=""" & A47 & """>") & "<premission user=""" & B47 & """ />"

Then I'd copy/paste the entire column, and clean up any tags at the beginning and end manually. XML shouldn't care about whitespace between tags.

 

[smackababy]

It is always two columns. The issue is that the parse multiple lines, all with the same report ID, and a single user per line. This is data coming from a flat file. Thankfully, it is spaced enough making columns is simple.

I need to consolidate each line, for each report, into a single report object in XML, with each user being one a new permission object and the data being added to the fields for each.

 

[veri745]

I'm confused. Your title says Excel to XML, but then in the OP you're talking about a flat file with spacing and such.

Is it just that Excel is the only tool you know how to use?

For converting a flat file to XML like this, I would definitely opt for a scripting language like Python or Ruby.

*edit* if you only have an XLS, or that's the only tool you want/have to use, then sort by report name and do what Ken_g6 said.

 

[smackababy]

Well, the data is from a flat file, but I was only given the Excel. I suppose I could get the data in any way.

I just ask about Excel, because for the rest of the data I've made XML for it worked well enough. If there is something other I could use, I'd prefer that. Unfortunately, I am unsure how to work my question in a way succinct enough to put into google.


Also, I should be clear, I am using the export to XML function on Excel. I have a schema to base this off of, which looks like what I posted (the format I require it in).

 

[Ken g6]

Also, I should be clear, I am using the export to XML function on Excel. I have a schema to base this off of, which looks like what I posted (the format I require it in).

Ah, I may not have been clear in my suggestion. I was suggesting you create a new column next to the other two columns and populate it with a formula like the one I posted (fix the line numbers). Then copy the entire column with the formula, paste it into a text file, clean up the beginning and end (though you can make tweaks in the column to make this automatic), and save the text file as .xml. No export stuff involved.

 

[Tweak155]

You could write a VBA function that does this pretty easily, but I don't know of a way to use the built in features for what you're looking for.

 

[tfinch2]

Personally, I would use something like http://poi.apache.org/

 

[oynaz]

C# has an Excel API which is supposedly quite easy to use. That might solve it.

 

[smackababy]

I've convinced them to not import this information in this way. The updated data I got was even more cumbersome (rather than 50,000 rows, it ended up being well over 500,000).