Can Macro save CTRL-V keystroke instead of pasting the value pasted during recording?
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?
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.
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
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
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!
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
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.split(' ') # get the QTY elements pos = editor.getLineEndPosition(0) # get the end position of first line editor.insertText(pos, ','+ qty_array) # 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) # and insert 3rd element of what we got from header editor.endUndoAction() # set undo end mark
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.
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.
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
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\nby 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 :-)
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.
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?