How to create a ”droplet” script in Win7 that runs a Notepad++ task on a CSV file

batch filecellscsvnotepadspreadsheet

I have recently asked a question on how to perform a neat little 'Find & Replace' task on my CSV media database files (a task i have to do at work on a daily basis). The question was answered here if anyone is interested in the details.

I now want to be able to simply drag and drop my CSV file onto a droplet on the desktop that performs the Find and Replace Notepad++ task quickly and easily, so i can delegate this task to more junior members of staff at times when i'm away.

Question:

How do I create a droplet in Win 7 that runs the following 'Find & Replace' command on any CSV file dropped onto it?

enter image description here

Text:

Find what:

^([^,]*,(\d\d)_[^,]*,[^,]*)(?<!_\d\d),

Replace with:

$1_$2,

Check: Wrap around

Check: Regular expression

Run: Replace All

I am imagining that this could be a batch script (or any script of sorts) that will receive my CSV file as the input file by simply drag dropping it onto the script.

Best Answer

Windows 7 comes with PowerShell v2 which supports Regular Expressions with negative look behinds.

If I interpret your RegEx correctly you want to apply the two digits 99_ leading the 2nd field to trail also the 3rd field, provided there isn't already a _99 number. And leave the other lines/remainder of the line as is.

A batch file serving as a drop target (also handling multiple files):

:: SO_1352996.cmd
@Echo off
:Loop
If "%~1" equ "" goto :Eof
If not exist "%~1" (shift & goto :Loop)
Ren "%~f1" "%~n1_Original%~x1"
:: Use PowerShell as a tool to do the replace.
Powershell -NoP -C "(Get-Content '%~dpn1_Original%~x1') -replace '^([^,]*,(\d\d)_[^,]*,[^,]*)(?<!_\d\d),','$1_$2,' | Set-Content '%~f1'
Shift
Goto :Loop

file.csv before

test,12_blah,blubb,anything
test,34_blah,blu_34,doesn't matter
test,56_foo,bar,nevermind

and after dropping on the above batch.

test,12_blah,blubb_12,anything
test,34_blah,blu_34,doesn't matter
test,56_foo,bar_56,nevermind

The original file is saved with the additional extension .bak

Related Question