Writing an XML outfile file from an Excel or text input file

jmohsin used Ask the Experts™

1. I have an Excel file with 20+ columns.

2. I need to write an XML (output) file which is based on the Excel (input).

3. The SAME (selected) columns from EACH row provide blocks for the XML file.

4. What would be the best tool for this job? AWK? Perl? VBA?

5. If need be, I can also start with a text file (instead of Excel).

Once this question is answered, I will open a follow on question with actual code / script requests.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Application Consultant
VBA is cool,

Code below will translate a whole excel.sheet into XML and will use the first line as TAG'S
(so remove spaces and special characters from the first line :-)

Sub BuildXml()

Dim Xml As Object

    XmlFile = "c:\MyTestData.xml"
    Rc = Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Rows.Count
    Cc = Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Columns.Count

    Set Xml = CreateObject("ADODB.Stream")
    Xml.Type = 2 'Specify stream type - we want To save text/string data.
    Xml.Charset = "utf-8" 'Specify charset For the source text data.
    Xml.Open 'Open the stream And write binary data To the object
    Xml.WriteText "<?xml version=""1.0"" encoding=""utf-8""?><Lines>" & vbCrLf
    For r = 2 To Rc
    Cells(r, 1).Select
    tg$ = ""
        For c = 1 To Cc
            If Cells(r, c) <> "" Then
            tg$ = tg$ & "<" & Cells(1, c) & ">" & Cells(r, c) & "</" & Cells(1, c) & ">"
            End If
        Next c
        If tg$ <> "" Then
            tg$ = "<Line>" & tg$ & "</Line>"
            Xml.WriteText tg$ & vbCrLf
        End If
    Next r
    Xml.WriteText "</Lines>"
    Xml.SaveToFile XmlFile, 2 'Save binary data To disk
    Cells(1, 1).Select

End Sub

Open in new window

It's certainly possible to do similar in perl.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial