Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2673
  • Last Modified:

batch file to read csv files, replace text

I need to read all the csv files in a folder and get rid of some characters...

PO#
PO# (there is a space at the end)

I dont want to rename the files, just want to go through them and get rid of those characters. This is on a W2008 Server 64 bit. Thank you.
0
pkromer
Asked:
pkromer
  • 7
  • 4
  • 2
  • +3
1 Solution
 
knightEknightCommented:
You can use this command-line utility to replace (or remove) strings from multiple files in one command:

   http://www.paulslore.com/utils/chgstr.zip

example:

  chgstr.exe  "PO#"  ""  *.csv  /s
0
 
TWCGuyCommented:
This would be easily scripted in autohotkeys.  Check out www.autohotkey.com.  I find it a lot more flexible and useful than batch file processing.  You would need a File reading loop and a regexreplace commmand to find and remove the undesired characters.  If you want to take this approach and need some help look me up here or ask on the AHK forums (after reading the command documentation first of course) :-D
0
 
Kamaraj SubramanianApplication Support AnalystCommented:
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Bill PrewCommented:
I highly recommend the Funduc Search & Replace tool, it can do pretty much anything you want from a replace tool.  Costs a few bucks, but well worth it.

http://www.funduc.com/

Two other tools I have used with sime success, but not as powerful as the above tool.

http://www.wingrep.com/
http://www.powergrep.com/grep.html

Hope this helps.

~bp
0
 
pkromerAuthor Commented:
The problem is i need to give this to someone that won't be able to use a seperate utility. It needs to be as simple as possible, like just hitting a batch file beore he opens the csv files. It's going to need to be used a few times a day, hence the need for a one-click operation.
0
 
pkromerAuthor Commented:
and, it's ALWAYS going to find those characters. The logic will never change.
0
 
Bill PrewCommented:
Could certainly be done with a BAT file, can you provide a sample of the data?

~bp
0
 
pkromerAuthor Commented:
sure...

085856,,SO# 170545,PO# 30537,F/C,1,0000000
085674,,SO# 232455,PO# 4356,F/C,1,0000000

etc. What needs to go away is the SO# and the PO# , leaving just the numbers that follow. The folder will have multiple csv files in it, so the batch file will run through all of them and do this, leaving the names intact. Is that possible?
0
 
pkromerAuthor Commented:
itkamaraj:
I tried that and it works well, but it is meant for one file at a time. Is there any way to modify it so it goes through ALL the csv files in a folder?
0
 
Kamaraj SubramanianApplication Support AnalystCommented:
Hope this will work for you
@echo off 
setLocal enabledelayedexpansion 
for %b in (*.csv) do
for /f "tokens=* delims=" %%a in (%b) do ( 
set tl=%%a 
Set tl=!tl:search=replace! 
echo !tl!>>tmp.rtf ) 
del %b & ren tmp.rtf %b

Open in new window

0
 
Steve KnightIT ConsultancyCommented:
Give this a try.  Could probably tidy it up a bit more, and add some more error checking if needed but works for me.

@echo off  
Setlocal enabledelayedexpansion  
rem cd /d c:\startpath
:: %%f gets set to filename in a loop using output of dir command
:: Runs down each line of the files, setting %%L to each line in turn, replaces the two strings in each line
:: into a temporary CSV file and copies it back over the original filename

for /f "tokens=*" %%f in ('dir /b /a-d *.csv') do (
  echo Working on %%f
  del csv.tmp 2>NUL
  for /f "tokens=*" %%L in ('type "%%f"') do (  
    set line=%%L
    set line=!line:PO# =!  
    set line=!line:SO# =!  
    echo !line!
   )>>csv.tmp
   if exist csv.tmp (
     copy csv.tmp "%%f" /Y >NUL
   ) ELSE (
     echo No output found for "%%f"
   )
)
del csv.tmp 2>NUL

Open in new window

0
 
pkromerAuthor Commented:
That just blinks a cmd window. I tried a pause on the next line after the (*.csv) do, but it won't. It looks like it's not acceptiong the wildcard, maybe.
0
 
Steve KnightIT ConsultancyCommented:
is that my one or the other? I tested mine on a sample of your data. as it stands run it from the same dir as the csv files - will check the code posted ok shortly.  Do you get any output?

The other batch file posted has a couple of errors in it as it stands ( % instead of %% etc) so maybe that is the one you ran?

Steve
0
 
Steve KnightIT ConsultancyCommented:
Aside from the fact I remmed out the cd command to take you to the path you want (so it currently works on the current dir) it should do its stuff:

cd /d c:\startpath
.  Try this one with some more prompts (only changes).  I get this output:

C:\utils\scripts\searchreplace\>removechars.cmd
10:03:56.45 Started

10:03:56.45 Working on 1.csv
10:03:56.45   Copying temp file back over 1.csv - errorlevel = 0
10:03:56.45 Working on d f x.csv
10:03:56.45   Copying temp file back over d f x.csv - errorlevel = 0
10:03:56.45 Working on x.csv
10:03:56.45   Copying temp file back over x.csv - errorlevel = 0

10:03:56.54 Removing temp file
10:03:56.54 Finished

@echo off  
Setlocal enabledelayedexpansion  
cd /d c:\startpath
:: %%f gets set to filename in a loop using output of dir command
:: Runs down each line of the files, setting %%L to each line in turn, replaces the two strings in each line
:: into a temporary CSV file and copies it back over the original filename

echo %time% Started
echo.

for /f "tokens=*" %%f in ('dir /b /a-d *.csv') do (
  echo %time% Working on %%f
  del csv.tmp 2>NUL
  (for /f "tokens=*" %%L in ('type "%%f"') do (
    set line=%%L
    set line=!line:PO# =!
    set line=!line:SO# =!
    echo !line!
   ))>csv.tmp
   if exist csv.tmp (
     copy csv.tmp "%%f" /Y >NUL
     echo %time%   Copying temp file back over %%f - errorlevel = !errorlevel!
   ) ELSE (
     echo No output found for "%%f"
   )
)
echo.
echo %time% Removing temp file
del csv.tmp 2>NUL
echo %time% Finished

Open in new window

0
 
pkromerAuthor Commented:
That is friggin beautiful. Thanks very much, its perfect.
0
 
Steve KnightIT ConsultancyCommented:
No problem, glad you liked it :-)

Steve
0
 
pkromerAuthor Commented:
I have another question about this, totally new direction, will post seperate.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now