[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Automated way to invert data of a Tag in A XML like file.

I have a OFX file that was downloaded from a financial instution. The data structure resembles XML.

I am looking for an automated way of inverting the data of Memo and Name fields. I believe this can be accomplished using a UNIX shell script and sed, grep, etc but my experience with these tools is limited.

Thanks in advanced for the assistance.
-Original
Heading Removed 
------------------------
 
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>12345678
<TRNAMT>-10.00
<FITID>5555
<NAME>Name Field             
<MEMO>Memo Field
</STMTTRN>
 
 
-Outcome
Heading Removed 
------------------------
 
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>12345678
<TRNAMT>-10.00
<FITID>5555
<NAME> Memo Field            <<<<< These two fields have been inverted
<MEMO>Name Field            <<<<<
</STMTTRN>

Open in new window

0
Doc54963
Asked:
Doc54963
  • 2
  • 2
1 Solution
 
cminearCommented:
The use of sed and grep are probably insufficient, without jumping through a lot of unnecessary hoops.  My solution is a simple perl script, added below.  These days, perl is readily available on many UNIX systems.

Assuming your OFX file is named "download.ofx", and the code below is saved to 'name-memo-swap.pl' you would run the command like this:
  perl name-memo-swap.pl < download.ofx > new-download.ofx

The modified OFX is written to new-download.ofx.

The script assumes that '<NAME>' and '<MEMO>' always start a new line, and that they are always uppercase.  The script also assumes that those tags will appear in a STMTTRN block together; if they don't, then the script will start doing some bad things.  (I could modify the script to be more robust, but this is a start.)
#!/usr/bin/perl
 
while (<STDIN>) {
  if (/^\074NAME\076(.*)/) {
    $name_field = $1;
  } elsif (/^\074MEMO\076(.*)/) {
    $memo_field = $1;
  } else {
    print $_;
  }
 
  if (defined($name_field) and defined($memo_field)) {
    print "<NAME>$memo_field\n<MEMO>$name_field\n";
    $name_field = $memo_field = undef;
  }
}

Open in new window

0
 
Doc54963Author Commented:
cminear

Thanks for the help.

I looked at the OFX file to verify your assumptions but they are not correct. The <NAME> and <MEMO> don't always appear together in the STMTTRN block.
0
 
cminearCommented:
OK, try the following script.  Use the same assumptions in my prior solution.  This will look for the ending "</STMTTRN>" tag, at which point if it has seen the "NAME" and "MEMO" lines, it will swap their values; if it has only found one or the other, that one line will be outputed unchanged.  

The potential downside is that the NAME and MEMO lines will be "moved" to just ahead of the closing STMTTRN tag.  In your example, that is already how they appear, so it isn't a big issue.  And it may not matter if lines appear ahead of NAME and MEMO when they initially appeared behind them.  But it is something to be aware of.
#!/usr/bin/perl
 
while (<STDIN>) {
  if (/^\074NAME\076(.*)/) {
    $name_field = $1;
  } elsif (/^\074MEMO\076(.*)/) {
    $memo_field = $1;
  } elsif (m|^\074/STMTTRN\076|) {
    if (defined($name_field) and defined($memo_field)) {
      print "<NAME>$memo_field\n<MEMO>$name_field\n";
    } elsif (defined($name_field)) {
      print "<NAME>$name_field\n";
    } elsif (defined($memo_field)) {
      print "<MEMO>$memo_field\n";
    }
    $name_field = $memo_field = undef;
    print $_, "\n";
  } else {
    print $_;
  }
}

Open in new window

0
 
Doc54963Author Commented:
That worked like a charm thank for the assistance.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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