Techy XML question

[daniel_owen_uk]

I have an excel file with around 40 fields. I need to turn this file into an xml file.

Normally I would try to do this via excel, but unfortunately the xml I need is 3 levels deep and excel wont support that.

It needs to be like;

Quote:(header)Header info
(info1)Bit of stuff
(info2)Bit more stuff
(/info2)
(/info1)
(/header)

Anyone got any software that can do this? Without getting too involved.

(edit- those code tags are about as much use as a chocolate fireguard)

[michaelr64]

(12-28-2011 01:07 PM)daniel_owen_uk Wrote:  I have an excel file with around 40 fields. I need to turn this file into an xml file.

Normally I would try to do this via excel, but unfortunately the xml I need is 3 levels deep and excel wont support that.

It needs to be like;

Quote:(header)Header info
(info1)Bit of stuff
(info2)Bit more stuff
(/info2)
(/info1)
(/header)

Anyone got any software that can do this? Without getting too involved.

(edit- those code tags are about as much use as a chocolate fireguard)

get a trial version of stylus studio and use their visual mapper to write an xslt that does the xml parsing from the saved-as-xml xcel file.

[daniel_owen_uk]

I was with you as for as the getting trial bit

So presumably I can feed the excel file into it, map it to the schema I have and then simply output the xml file?

[michaelr64]

(12-28-2011 02:15 PM)daniel_owen_uk Wrote:  I was with you as for as the getting trial bit

So presumably I can feed the excel file into it, map it to the schema I have and then simply output the xml file?

sort of...
you'll need an xslt to get from one xml to another.
stylus studio can do that but you'll need to do the coding.
however you can use their visual mapper to do this in a drag and drop kinda way.

but as always: ymmv ..
especially if you're not xml /xslt savvy

[daniel_owen_uk]

I will definitely give it a go over the weekend. Probably a little bit of a sledgehammer to crack a nut if I am honest, and convincing the gaffer to fork out $695 could be pushing it a little.

[lordy]

Personally, if it's a one off excercise, I'd export to CSV and get a good text editor that supports macros and do it manually.

Also if you have control over the final XML format you may find it could be a PITA getting at your header info and your info1, information (which looks like the 1st text() child).
Especially when dealing with white space. Usually it is a little easier to have each thing in its own element tag (or attribute) eg.

Code:

(info1)(infodata)Bit of stuff(/infodata)
(info2)Bit more stuff(/info2)(/info1)

[daniel_owen_uk]

Text editor is a definite no no, like I say 400 lines a week 40 fields.

Code:

(?xml version="1.0" encoding="utf-8"?)
(ABWInvoice xmlns="http://services.agresso.com/schema/ABWInvoice/2007/12/24" xmlns:agrlib="http://services.agresso.com/schema/ABWSchemaLib/2007/12/24" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://services.agresso.com/schema/ABWInvoice/2007/12/24 http://services.agresso.com/schema/ABWInvoice/2007/12/24/ABWInvoice.xsd")
(Invoice)
    (InvoiceNo)8989890(/InvoiceNo)
    (Header)
        (agrlib:InvoiceDate)2011-10-30(/agrlib:InvoiceDate)
        (agrlib:DueDate)2011-11-30(/agrlib:DueDate)
        (OrderRef)49000001(/OrderRef)
        (OrderNo)0(/OrderNo)
        (agrlib:ContractId)(/agrlib:ContractId)
        (Currency)GBP(/Currency)
        (Seller)
            (agrlib:SellerNo)SUP11280(/agrlib:SellerNo)
        (/Seller)
        (Buyer)
            (Accountable)DAN(/Accountable)
        (/Buyer)
        (agrlib:ReferenceCode)
            (agrlib:Code)A3(/agrlib:Code)
            (agrlib:Value)FT(/agrlib:Value)
        (/agrlib:ReferenceCode)
    (/Header)
        (Details)
            (Detail)
            (LineNo)1(/LineNo)
            (LineTotExclTax)68.00(/LineTotExclTax)
            (TaxAmount)0.00(/TaxAmount)
            (LineTotInclTax)68.00(/LineTotInclTax)
            (agrlib:TaxCode)ZR(/agrlib:TaxCode)
            (DetailInfo)
            (/DetailInfo)
            (/Detail)
        (/Details)
    (Summary)
        (TotalExclTax)68.00(/TotalExclTax)
        (TotalTax)0.00(/TotalTax)
        (TotalInclTax)68.00(/TotalInclTax)
    (/Summary)
(/Invoice)
(/ABWInvoice)

That's an example of the output file

The input file is

Code:

General ledger code,National product code,Manufacturer's product code,EClass,Description,Requisition point code,Requisition point name,Requisition type,Username,Local reference,Requisition code,Line,Date dd/mm/yyyy,Sale price,Units of issue,Qty,VAT rate,Net total sales,VAT,Gross total sales,Financial week,Financial period,Financial year,Invoice number
,VMU033,203996 ,VHP,Underpads virgin fluff pulp 4-way sealed,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,1,01/04/2011,Sensitive,Pack of 50,12,Out of scope,101.88,0,101.88,1,1,2012,50114034
,CFP010,201510 ,CXP,All-in-one pads absorbency band D15 Slip,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,2,01/04/2011,Sensitive,Pack of 28,2,Out of scope,17.4,0,17.4,1,1,2012,50114034
,CFP159,201398 ,CXP,All-in-one pads absorbency band D17 Slip,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,3,01/04/2011,Sensitive,Pack of 28,4,Out of scope,40.24,0,40.24,1,1,2012,50114034
,CFP072,791102 ,CXP,Absorbent pull-up pants D14 Medium 100%,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,4,01/04/2011,Sensitive,Pack of 14,4,Out of scope,26.96,0,26.96,1,1,2012,50114034
,CFP073,791202 ,CXP,Absorbent pull-up pants D14 Form super l,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,5,01/04/2011,Sensitive,Pack of 14,4,Out of scope,28.72,0,28.72,1,1,2012,50114034
,FTG085,Gl880/s ,FTA,Gloves examination latex powder free non,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,6,01/04/2011,Sensitive,Box of 100,10,Out of scope,44.3,0,44.3,1,1,2012,50114034
,FTE483,Gn31/m ,FTA,Gloves examination latex powder free non,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,7,01/04/2011,Sensitive,Box of 100,20,Out of scope,84,0,84,1,1,2012,50114034
,FTG087,Gl880/l ,FTA,Gloves examination latex powder free non,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,8,01/04/2011,Sensitive,Box of 100,10,Out of scope,44.3,0,44.3,1,1,2012,50114034

[michaelr64]

(12-29-2011 10:24 AM)daniel_owen_uk Wrote:  Text editor is a definite no no, like I say 400 lines a week 40 fields.

Code:

(?xml version="1.0" encoding="utf-8"?)

That's an example of the output file

The input file is

Code:

General ledger code,National product code,Manufacturer's product code,EClass,Description,Requisition point code,Requisition point name,Requisition type,Username,Local reference,Requisition code,Line,Date dd/mm/yyyy,Sale price,Units of issue,Qty,VAT rate,Net total sales,VAT,Gross total sales,Financial week,Financial period,Financial year,Invoice number
,VMU033,203996 ,VHP,Underpads virgin fluff pulp 4-way sealed,Sensitive,Sensitive,Online Ordering,Sensitive,,59790W,1,01/04/2011,Sensitive,Pack of 50,12,Out of scope,101.88,0,101.88,1,1,2012,50114034

I thought the input file was an excel file?
If so use the save as xml option that will output it as an xml file.
Use that as an starting point to make your xslt on.

[daniel_owen_uk]

It is but I can't paste an excel file

I would use the excel output as xml but unfortunately that only supports 1 child (you can't have 3 nested tables or something).