Solved

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

Posted on 2013-12-01
9
514 Views
Last Modified: 2013-12-02
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
Comment
Question by:XGIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 39688960
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
 
LVL 7

Author Comment

by:XGIS
ID: 39688978
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39688986
HI

okay

Andrew
example.xlsb
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39688994
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
 
LVL 4

Accepted Solution

by:
andrew_man earned 500 total points
ID: 39689005
The result moves to sheet2 now.....
example1.xlsb
0
 
LVL 7

Author Comment

by:XGIS
ID: 39689077
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
 
LVL 7

Author Closing Comment

by:XGIS
ID: 39689090
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39689113
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
 
LVL 7

Author Comment

by:XGIS
ID: 39689299
Hello Andrew, Nothing short of spectacular...Thankyou for the extra input.

Awesome
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question