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

jmohsin
jmohsin used Ask the Experts™
on
Hi,

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Application Consultant
Commented:
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"
    Range("A1").Select
    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
    Xml.Close
    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