Can Macro save CTRL-V keystroke instead of pasting the value pasted during recording?

Felix A

Hi. I am trying to record a macro that can be run on many files of the same structure. I am trying to copy a value from the file and paste it at the end of each line as the value of a new column. But when I run it on other files, it pastes the value from when the macro was being recorded instead of the value found in the current file. Is there a way to record the paste command in the macro without the value being pasted?

Claudia Frank

Hello @Felix-A,

I think the only save way to do it with macro
would be using regular expressions

Let’s say we have the following text

value=1     text=rt1    old=12     new=
value=2     text=rt2    old=13     new=
value=3     text=rt3    old=14     new=
value=4     text=rt4    old=15     new=
value=5     text=rt5    old=16     new=
value=6     text=rt6    old=17     new=
value=7     text=rt7    old=18     new=
value=8     text=rt8    old=19     new=
value=9     text=rt9    old=20     new=
value=10    text=rt10   old=21     new=

and we want to get the value of each line and add it to the end,
to get the result like

value=1     text=rt1    old=12     new=1
value=2     text=rt2    old=13     new=2
value=3     text=rt3    old=14     new=3
value=4     text=rt4    old=15     new=4
value=5     text=rt5    old=16     new=5
value=6     text=rt6    old=17     new=6
value=7     text=rt7    old=18     new=7
value=8     text=rt8    old=19     new=8
value=9     text=rt9    old=20     new=9
value=10    text=rt10   old=21     new=10

we would do

start recording macro
press CTRL+h
find what : (.*?=)(.+?)(\s+.+)$
replace with: \1\2\3\2
press replace all
press close
save macro with meaningful name

The regex, of course, needs to be modified to match your data structure.

Cheers
Claudia

Felix A

Claudia, thanks so much for your input. Hopefully this will clarify my question and then you can tell me if part of your method still applies.

Say that I have created a csv file from a regular text file successfully, using a bunch of macros with regular expressions. At the end I have a file with a header like this with data as follows:

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
Green Car,0145,8,
Blue Car,0245,4,
Yellow Bus,0345,2,

I need to run another macro to accomplish the following on multiple files:
(the SHIPPED column remains blank)

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
Green Car,0145,8,94608342
Blue Car,0245,4,94608342
Yellow Bus,0345,2,94608342

If I record a macro to do this, it doesn’t work on other files b/c I don’t know how to tell it to record just the paste command needed at the end instead of pasting 94608342. Of course, the other files will not have the same value after PO so the file will be incorrect if it ends up with the wrong PO number in that new column.

The first part of the macro grabs the value 94608342 which is living inside the header that begins with QTY. Then it adds “,PO” and arrows down
Then CTRL-H does: find \r and replace with “\r,94608342”

But it’s there that I wonder if I can tell it find \r and replace with “\r,[Paste whatever is in the clipboard]”

By the way, I will be learning something pretty nifty from method regardless!

Felix A

For some reason the “blank” SHIPPING column did not appear in the post.

The end result would look something like this: (adding a space b/w commas to force it to show).

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
Green Car,0145,8, ,94608342
Blue Car,0245,4, ,94608342
Yellow Bus,0345,2, ,94608342

Claudia Frank

Hello Felix,

I don’t think that this can be achieved by using macros.
A simple python script could do it.

Assuming the following lines as input

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
Green Car,0145,8,
Blue Car,0245,4,
Yellow Bus,0345,2,

would result in

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
Green Car,0145,8,,94608342
Blue Car,0245,4,,94608342
Yellow Bus,0345,2,,94608342

The script it self looks like this

editor.beginUndoAction()                                    # set undo start mark

header_array = editor.getLine(0).rstrip('\r\n').split(',')  # get the header elements
qty_array = header_array[2].split(' ')                      # get the QTY elements
pos = editor.getLineEndPosition(0)                          # get the end position of first line
editor.insertText(pos, ','+ qty_array[2])                   # insert a comma and the PO at the end of the line


max_lines = editor.getLineCount()                           # get max. lines
for i in range(1, max_lines):                               # for each line
    _text = editor.getLine(i)                               # get the text
    if _text.count(',') == 3:                               # and check if it has 3 commas (to test if this is a valid text)
        pos = editor.getLineEndPosition(i)                  # if valid, get the end position of that line
        editor.insertText(pos, ','+ qty_array[3])           # and insert 3rd element of what we got from header
        
editor.endUndoAction()                                      # set undo end mark

Cheers
Claudia

Felix A

Wow, I was not expecting to branch out like this but willing to learn, especially if it can stay inside notepad(?) since the macros/scripts are run by clerical personnel on about 30 files per day. They process the files really fast actually, but adding columns of data like this will allow me to do more with them. Thank you very much. I will experiment.

Claudia Frank

if it can stay inside notepad(?)

you need to have the python script plugin installed to make this work.
As the plugin manager sometimes has some problems to correctly
install it I would propose to use msi installer instead.

Cheers
Claudia

guy038

Hello Felix A and Claudia,

Of course, the Claudia’s Python script is the ideal solution :-)

However, in the particular case ( and ONLY in that case ! ) where the number of lines, under the header ( ITEM…SHIPPED ), is fixed , throughout all your files ( 3 in your example ), you can, simply, use the regex Search/Replacement, below :

Find what    :  (?-i)(^ITEM.+PO (\d+) .+)\R(.+)\R(.+)\R(.+)\R

Replace with :  \1,PO\r\n\3,\2\r\n\4,\2\r\n\5,\2\r\n

NOTES :

There are TWO spaces, one BEFORE and one AFTER the block (\d+), in the search regex
If you usually work with Unix files, change the form \r\n by the simple syntax \n, instead, in the replacement part !

For instance, with that S/R, the list below :

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
Green Car,0145,8,
Blue Car,0245,4,
Yellow Bus,0345,2,

ITEM,SKU,QTY #891 PO 987 02-10-2016,SHIPPED
Green Car,0145,8,
Blue Car,0245,4,
Yellow Bus,0345,2,

ITEM,SKU,QTY #891 PO 12345 02-10-2016,SHIPPED
Green Car,0145,8,
Blue Car,0245,4,
Yellow Bus,0345,2,

would become :

ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
Green Car,0145,8,,94608342
Blue Car,0245,4,,94608342
Yellow Bus,0345,2,,94608342

ITEM,SKU,QTY #891 PO 987 02-10-2016,SHIPPED,PO
Green Car,0145,8,,987
Blue Car,0245,4,,987
Yellow Bus,0345,2,,987

ITEM,SKU,QTY #891 PO 12345 02-10-2016,SHIPPED,PO
Green Car,0145,8,,12345
Blue Car,0245,4,,12345
Yellow Bus,0345,2,,12345

Felix, if this S/R suits you, I could give you some explanations, about it, if necessary :-)

Best Regards,

guy038

Felix A

Hi Guy038: Thanks very much. This is my 2nd reply. Hopefully this one will post. I will definitely take a look. Since the number of lines will vary from file to file, it may not apply for this, but I imagine I will be able to use this for other projects as well. Would be amazing if a Macro could record a keystroke in the middle of a regex search replace operation but that is apparently not doable and thanks very much for confirming. What a great community.

Felix A

Hi Guy, just curious if there is an easy way to make the regex flexible where it can to the insertion of the text on all lines be there 3, 6, or a 100?