3

I have an Excel spreadsheet file that has 5 or so columns and hundreds of lines. I need to convert this (export these data) to an XML file. I'm interested in three of the columns and they correspond to these XML tags, where info1 can be followed by info2, info3, etc...

<?xml version="1.0" encoding="UTF-8" ?>
<list>  
<info1>  
<id>111</id>   
<value>222</value>   
<des>333</des>  
</info1>
</list>

If possible, I would like to avoid building this XML manually. It wouldn't be too much trouble to rearrange the Excel file such that the three columns I'm interested in were in their own file. But then I would need to export those data into an XML file of the above format. Any ideas?

  • 2
    I'm sure there's a way to do this with excel macros, but I'm at a complete loss for how to do it, because I don't have excel. At the least, it's a place to start looking. – Babu May 20 '10 at 5:20
  • 1
    After I posted this question, I did run into some literature with the same approach. I will start there and hopefully some other great ideas can come from SU. Thanks! – nicorellius May 20 '10 at 5:48
3

This is kind of hacky, but what you can do is insert rows between each column so that it starts like this:

A        B        C
1111     2222     3333

and finishes like this:

A        B        C        D        E        F        G        H        I
<list>
<info1>  1111     </info1> <info2>  2222     </info2> <info3>  3333    </info3>
...
</list>

Select the columns you added, Fill>Down.

Then trim the spaces with a tool like digdb (there is a free 15 day trial). Copy/paste the area into notepad and you're done.

It's a little hacky, but it should work.

  • 1
    Clever idea... If I hadn't already found a decent solution I would try this for sure... Thanks! – nicorellius May 20 '10 at 17:47
  • @nicorellius: any time. What was your solution? – Steven Evers May 20 '10 at 21:10
  • Did you not see my answer above? I answered my own question before you posted your answer... It worked well. – nicorellius May 20 '10 at 22:17
2

I used Excel Macros to solve this problem. Thanks, Babu, for the suggestion, and www.meadinkent.co.uk for the VBA Macro for the module. With a little tweaking, I got it to do exactly what I wanted. If anyone else needs this assistance, here is the link that helped me:

Excel to XML Help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.