Hi,

I'm new to batch processing and am really STRUGGLING with this problem. I've a txt file which is created by the below bcp command:

bcp "Select field1,field2 from database_table" queryout c:\OrigFile.txt -c -t, -T

which creates a txt file with the below sample data:
Paddy,O'Culleton
Frank,McPhillips
Patrick J,Hall
T,Martin
,Markey Ltd
Elizabeth,O'Meel
T,Conlan

I then NEED to edit the txt file to remove the occurances of ' in it e.g. O'Culleton should changed to be OCulleton so I put together the following batch script:
@echo off > c:\NewFile.txt & setLocal enableDELAYedeXpansion

for /f "tokens=* delims=," %%a in (c:\OrigFile.txt) do (
set str=%%a
echo !str:^'=! >> c:\NewFile.txt
)

If I run the bcp and then run the above script it does create a new txt file but only outputs the following lines:
Paddy,O'Culleton
Frank,McPhillips
Patrick J,Hall
T,Martin

I have tested loads of different things and I KNOW that if I run the bcp command and then open the actually OrigFile.txt that's created and just manually save this file the above script will work perfectly and output all the records into the NewFile.txt with no problems, however if I run both scripts straight after each other I only get the about 4 records outputed to the NewFile.txt.

I'm thinking the problem is caused by the delims because the first field in the problem line ( ,Markey Ltd) is a space and it's seeing it as delimiter as the default delimiter are and . Is there anyway of limiting the delims command to just use a comma as a delimiter and not a space? Or is there a better way of doing this?

Thanks.
I would try changing the query to directly extract the data as you want it and totally forget about post-processing the file.
So in your case:

bcp "Select replace(field1,'''',''), replace(field2,'''','') from database_table" queryout c:\OrigFile.txt -c -t, -TCode: [Select]import java.util.Scanner;
import java.io.*;
public class ReplacePattern {
public static void main(String[] args) throws FileNotFoundException, IOException{
if ( args.length!=2 ){
System.out.println("Usage: java ReplacePattern [pattern] [filename]");
System.exit(1);
}
Scanner sc = new Scanner(new File(args[1]));
String INPUT=null;
while( sc.hasNext() ){
input = sc.nextLine().replaceAll(args[0],"") ;
System.out.println(input);
}
}
}


use java to run the class file. To replace single quotes,
Code: [Select]java ReplacePattern "'" file



[recovering disk space - OLD attachment deleted by admin]This little ditty might help.

Code: [Select]@echo off > c:\NewFile.txt
setlocal enabledelayedexpansion

for /f "tokens=* delims=" %%a in (c:\OrigFile.txt) do (
set str=%%a
set str=!str:'=!
echo !str! >> c:\NewFile.txt
)

Good luck. Thanks a mil for your all replies, working like a treat now!

Went in the end with the replace SQL command, (had never heard of it before!) as now I only need to run one batch job.
Very happy chappie here! remove '

Paddy,O'Culleton
Frank,McPhillips
Patrick J,Hall
T,Martin
,Markey Ltd
Elizabeth,O'Meel
T,Conlan


C:\test>sed s/'//g OrigFile.txt
Paddy,OCulleton
Frank,McPhillips
Patrick J,Hall
T,Martin
,Markey Ltd
Elizabeth,OMeel
T,Conlan


donpage is Billrich. Ignore.
well, the only thing we can't ignore is he provided a legit solution, so there's nothing really "wrong" with that. Quote from: ghostdog74 on January 09, 2011, 04:55:48 AM

well, the only thing we can't ignore is he provided a legit solution, so there's nothing really "wrong" with that.