This discussion is archived
6 Replies Latest reply: Jul 21, 2010 8:41 AM by forumKid2 RSS

parsing .xls(excel) file and creating a .xdat(xml) file out of it

807580 Newbie
Currently Being Moderated
Hi All,

need some tips on a task i am trying accomplish for some days now.

I have a excel file, in which I have 10 columns with 40-50 rows of data.

I have a xml structure in mind, in which I want to put all these data from excel file. But till now I haven't understood exactly how I should go about it step by step.

Should I first parse the excel file and save each row (with the column names) in a list, and then read through each row and insert them in the xml format i have planned?

And how do I open a .xdat data and tell my program to insert the data in the sequence of sets I want them to be saved?

I know it's a pretty newbie question ... but will appreciate any help and tips provided. Would help me a lot to learn this new type of task.

Thank you.

with best regards,
Newbie
  • 1. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    forumKid2 Explorer
    Currently Being Moderated
    I would parse the xls via POI and then if you say your xdat is xml, build or modify it using JAXB.

    From a high level I dont see a need to store it in a List unless you need to run some business rules over the data.
  • 2. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    807580 Newbie
    Currently Being Moderated
    ok. I am not gonna pull any business rules on the collected data. Just reading each row and putting them in the relevant block inside the xml.

    could u refer me some example for creating .xdat in xml, and inserting data in a structured format (which can be any structure I want to have)!
  • 3. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    masijade Explorer
    Currently Being Moderated
    IOW "gimme teh codez".

    NO!
  • 4. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    807580 Newbie
    Currently Being Moderated
    Excuse me ... but I didn't ask for code. I am asking for tips, cos I want to learn the logic, not how to do copy pasting.... would appreciate if u don't put comments irrelevant to my topic. If u can help, then welcome and write some tips.
  • 5. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    807580 Newbie
    Currently Being Moderated
    Hi,

    yes I tried working with the JAXB .... opened the new file and all.

    if a xml structure is as following:

    <?xml version = "1.0"?>
    <books>
    <book>
    <Title>
    .... .... ....
    </Title>
    <ISBN>
    ... .... ....
    </ISBN>
    <Publisher>
    ... ... ...
    </Publisher>
    </book>
    <book>
    ...
    </book>
    <book>
    ...
    </book>
    ...
    ...
    ...

    </books>

    And I have the infos from a excel file ... how should I insert them in this format? I think what I am trying to ask is that... is there any special functions to fill the xml structure? or should I just insert the data in the .xml file depending on which data (Title, ISBN or Publisher) I am reading from the excel file row and column.

    Thank you.
  • 6. Re: parsing .xls(excel) file and creating a .xdat(xml) file out of it
    forumKid2 Explorer
    Currently Being Moderated
    If you are using JAXB you unmarshall to read the xml. Then you marshall to write.

    So what you do is:

    1) Unmarshall your xml document. This means you now have your xml as Java objects.
    2) Read through the Java objects using loops, etc making any changes to the values. I think here you want to add values, so you can set your values
    3) Now in memory you have your new xml thats been updated, so you can marshall it (save it)

    I'd recommend a JAXB tutorial. But the basic steps are:
    1) Create an xml file and insure its valid
    2) Use a free online utility (http://www.hitsw.com/xml_utilites/) to convert the xml into an xml schema (xsd)
    3) Use xjc from the jaxb jar and run it over the xml schema (the command i use is xjc myxmlfile.xsd -p com.example
    4) Step 3 above creates all the java classes for you to use so then you can unmarshall. Process. Then marshall

    This is just a high level. I might have missed something, but the jaxb tutorial is really good and that's how I learned the process.