Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

How do I Transpose XML Rows to a Single Column using "<" as the New Row Return

From This
<STMTTRN><TRNTYPE>DEBIT<DTPOSTED>20120701<TRNAMT>-80<FITID>01JUL2012.1<MEMO>Transaction1</STMTTRN>
<STMTTRN><TRNTYPE>CREDIT<DTPOSTED>20120702<TRNAMT>280<FITID>01JUL2012.2<MEMO>Transaction2</STMTTRN>

Open in new window

To This.  Please note rows must format with no row spaces
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20120701
<TRNAMT>-80
<FITID>01JUL2012.1
<MEMO>Transaction1
</STMTTRN>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20120701
<TRNAMT>280
<FITID>01JUL2012.2
<MEMO>Transaction2
</STMTTRN>

Open in new window

0
XGIS
Asked:
XGIS
  • 4
  • 4
1 Solution
 
duncanb7Commented:
Why your code is no closing tag in XML format ?

And what do you want for transpose ?

You want the tranpose output on Excel or Website ?

If display xml doc on website, just save the following as doc.xml and run it
on website ? and it can be displayed on Excel too but not at such your format

Hope you can clarify your question more or clearly

Duncan

<?xml version="1.0" encoding="UTF-8"?>
<note>
<STMTTRN><TRNTYPE>CREDIT</TRNTYPE><DTPOSTED>20120702</DTPOSTED><TRNAMT>280</TRNAMT><FITID>01JUL2012.2</FITID><MEMO>Transaction2</MEMO></STMTTRN>
<STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20120701</DTPOSTED><TRNAMT>-80</TRNAMT><FITID>01JUL2012.1</FITID><MEMO>Transaction1</MEMO></STMTTRN>
</note>

Open in new window

0
 
XGISAuthor Commented:
Pls see the attached spreadsheet.  The pasted BOLD values are static. This is the required output. Every time it sees a "<" the XML field starts a new row. It would be beneficial if it did it on the next sheet as it will obviously be quite long depending on how many rows I generate.
This sample is designed to generate testing data for a C# program.

I found this sample of code that may help but am not sure how to implement it in Excel 2012.
I need it to read all rows on the first sheet. and migrate them to the second sheet.

Sub SplitAll()
    Dim src As Range
    Dim result As Variant
    For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
        result = Split(src, "<")
        'last cell in column M
        With Cells(Rows.Count, 3).End(xlUp)
            Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
        End With
    Next src
End Sub

Open in new window

ExcelTransposeUsingDelimiter.xlsx
0
 
andrew_manCommented:
HI

okay

Andrew
example.xlsb
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
andrew_manCommented:
Hi all,

Okay!

amended!

Sub SplitAll()
    Dim src As Range
    Dim result As Variant
    For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
        result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
        'last cell in column M
       With Cells(Rows.Count, 3).End(xlUp)
           Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
        End With
    Next src
End Sub


Andrew Man from Hong Kong
example1.xlsb
0
 
andrew_manCommented:
The result moves to sheet2 now.....
example1.xlsb
0
 
XGISAuthor Commented:
Hello Andrew_man.... it is looking pretty good... funny comments..making excel interesting
I made some macro mods to try and eliminate the blank line space between the transposed records.
Is there a value I should change or add to remove the blank line spacing between the rows
Also a way to eliminate TRIM white-space  to the left or right of each value new value. It only seems to be affecting the last row of the first transpose row </STMTTRN>
0
 
XGISAuthor Commented:
Thankyou all for your input.. Special thanks to Andrew_Man for solving this issue.
I ended up just using F5 - Special - Blanks to remove lines and Trim and Clean to tidy the output.... Cheers Aaron
0
 
andrew_manCommented:
Sub SplitAll()
    Dim src As Range
    Dim result As Variant
    For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)

        result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
        'last cell in column M
       
       For i = 1 To UBound(result)
       result(i - 1) = result(i)
       Next i
       ReDim Preserve result(UBound(result) - 1)
   
       Sheets("Sheet2").Select
       
       With Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)
       
       
           Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
       End With
       
             
       Sheets("Sheet1").Select
       
    Next src
End Sub
0
 
XGISAuthor Commented:
Hello Andrew, Nothing short of spectacular...Thankyou for the extra input.

Awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now