text copy to following lines and tab delimited

text copy to following lines and tab delimited

8
NewbieNewbie
8

    Jul 07, 2005#1

    Hello,

    I have a flatfile (pos.txt) of Purchase Orders that need to modify for Integration purposes.

    Example

    P12345 Customer1
    Line Item 1 $30 Pst Gst
    Line Item 2 $45 Pst Gst
    P12344 Customer344
    Line Item 1 $345 Pst Gst
    P14432 Customer224
    Line Item 1 $32 Pst Gst
    Line Item 2 $67 Pst Gst
    Line Item 3 $889 Pt Gst
    Line Item 4 $443 Pst Gst

    I need the macro to do a loop to end of the flat file that will put the PO number to the beginning of each line below it until it reaches the next PO number. The result I am looking for is:

    P12345 Customer1
    P12345 Line Item 1 $30 Pst Gst
    P12345 Line Item 2 $45 Pst Gst
    P12344 Customer344
    P12344 Line Item 1 $345 Pst Gst
    P14432 Customer224
    P14432 Line Item 1 $32 Pst Gst
    P14432 Line Item 2 $67 Pst Gst
    P14432 Line Item 3 $889 Pt Gst
    P14432 Line Item 4 $443 Pst Gst

    Note - each PO will have a minimum of 1 Line item but could have be up to 15 or 20.

    Also after each PO number inserted, I also need to insert a Tab delimited character.

    Any help would be appreciated.

    Thanks
    JOhn
    206
    MasterMaster
    206

      Jul 07, 2005#2

      Try this. Make sure the last line in your text file has a carriage return.



      InsertMode
      ColumnModeOff
      HexOff
      UnixReOn
      Bottom
      "P "
      Top
      Clipboard 1
      Loop
      Find RegExp "^P[0-9]+ "
      IfNotFound
      ExitLoop
      EndIf
      Copy
      Key DOWN ARROW
      Key HOME
      StartSelect
      Find RegExp "^P"
      Key HOME
      Key UP ARROW
      Key END
      Find "Line"
      Replace All SelectText "^cLine"
      EndSelect
      Key DOWN ARROW
      Key HOME
      EndLoop
      Bottom
      DeleteLine
      Top
      Clipboard 0
      Software For Metalworking
      http://closetolerancesoftware.com
      61
      Advanced UserAdvanced User
      61

        Jul 07, 2005#3

        This is what I came up with ... the macro only looks at the first letter in each line, though:

        Code: Select all

        InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
Loop 
IfCharIs "P"
StartSelect
Key Ctrl+RIGHT ARROW
Key LEFT ARROW
Copy 
EndSelect
Key HOME
Key DOWN ARROW
Else
IfCharIs "L"
Paste 
" "
EndIf
Key HOME
Key DOWN ARROW
EndIf
EndLoop
        The checkbox "Show Cancel dialog for this macro" should NOT be checked.

          Jul 07, 2005#4

          Oops ... I think we both missed the "tab delimited" bit...

          If you want a TAB character (i.e. ASCII 9), then this should work:

          Code: Select all

          InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
Loop 
IfCharIs "P"
StartSelect
Key Ctrl+RIGHT ARROW
Key LEFT ARROW
Copy 
EndSelect
Key HOME
Key DOWN ARROW
Else
IfCharIs "L"
Paste 
"^t"
EndIf
Key HOME
Key DOWN ARROW
EndIf
EndLoop
          I just replaced the space character with a tab: "^t"

          HTH!
          8
          NewbieNewbie
          8

            Jul 08, 2005#5

            Thanks you for your replys. I have tried the Macros, however I need to modify my condition slightly.

            My LineItem # are really account number codes, for example,

            P12345 Customer1
            7221-5-5 $30 Pst Gst
            7221-5-5 $45 Pst Gst
            P12344 Customer344
            7220-3-5 $345 Pst Gst
            P14432 Customer224
            7220-4-5 $32 Pst Gst
            7220-4-5 $67 Pst Gst
            7220-4-5 $889 Pt Gst
            7220-4-5 $443 Pst Gst

            The account codes are always numbers and they will always start with 72##-#-#

            Your help again, would be appreciated. I also see that when I look at the file after it inserts a ^t, I see it as text. When I open another flatfile of mine with Invoices and tabdeliminted spaces, these spaces don't show. Is the ^t supposed to show as text?

            Thanks
            61
            Advanced UserAdvanced User
            61

              Jul 08, 2005#6

              I would really like to help you ... unfortunately, we are leaving this weekend for the holidays. Maybe you could study up on macros and figure out how to modify what has been given to you?
              261
              Basic UserBasic User
              261

                Jul 08, 2005#7

                John,

                Some interesting solutions. I think I can take elements from each and give you what you want:

                Code: Select all

                InsertMode
ColumnModeOff
HexOff
UnixReOn
Top
Loop 
IfEof
ExitLoop
EndIf
IfCharIs "P"
SelectWord 
Copy 
Else
Key LEFT ARROW
Find RegExp "^"
Replace "^c^t"
EndIf
Key HOME
Key DOWN ARROW
EndLoop
                Note that I'm using Unix Style RE, so the Find "^" means find the beginning of a line. I then replace it with the contents of the clipboard (^c) and the tab character (^t).

                I noted that the original PO line does not have a tab after the PO. That may be just because of cut and paste, so I hope it turns out the way you want it.

                Dave
                ASTTMan
                I'm not a Texan, I just live here.
                8
                NewbieNewbie
                8

                  Jul 11, 2005#8

                  Hi all,

                  The Macro worked beautifully. Thank you all for your input.

                  John