0

I have multiple csv files that I need to search for Special characters like the exclamation mark! if the character is found delete the information between the commas with a .bat file. the email address always seems to be where people screw up. example: 233dd123dde3,Valid,boxer,Nov-13,Philip Smith,andrew!@myaxxus.net,16666

fulltiltphil
  • 5
  • 2

2 Answers2

3

suggestion with sed for Windows:

sed -i.bak "s/[^,]*![^,]*//" *.csv
Endoro
  • 34,892
  • 8
  • 45
  • 61
  • 1
    +1, Oooh, nice. I didn't realize that the greedy match would work so well until I saw your answer. The same search works with my REPL.BAT solution. – dbenham Sep 10 '13 at 03:44
1

That seems like a drastic measure to completely drop the entire value if a single character exists, but it can be done.

Note that the you must account for the fact that the first value does not have a leading comma, and the last value does not have a trailing comma.

This solution will not properly handle quoted values containing commas.

I'm using a hybrid JScript/batch utility called REPL.BAT that performs a regex search and replace on stdin and writes the result to stdout. It is pure script that works on any modern Windows from XP onward - no 3rd party executebable required. Full documentation is embedded within the utility.

Assuming that REPL.BAT is in your current directory, or better yet, somewhere within your path:

@echo off
for %%F in (*.csv) do (
  type "%%F" | repl "(^|,)[^,]*![^,]*(,|$)" "$1$2" >"%%F.new"
  move /y "%%F.new" "%%F" >nul
)


EDIT

Now that I see Endoro's sed solution, I realize that the default greedy match means you don't have to explicitly match the commas. The following simpler regex works just as well:

@echo off
for %%F in (*.csv) do (
  type "%%F" | repl "[^,]*![^,]*" "" >"%%F.new"
  move /y "%%F.new" "%%F" >nul
)
Community
  • 1
  • 1
dbenham
  • 119,153
  • 25
  • 226
  • 353
  • the ! could be a 1 or not needed at all but without speaking to the person the customer they're not receiving an email at all up to this point. that's the reason removing the data from that delimiter position would be the only answer. I'll look at the repl.bat to see how that would work. – fulltiltphil Sep 10 '13 at 13:24