Find and Replace fields in a column of a CSV file

[shaswat]

Hi Team,

I have a requirement where I need to change some of the values of a column of a file. I want SALE to be changed as SELL and PURCHASE to be changed as BUY only in a specific column. Please find my input file and desired output file for clear understanding of my requirement. Please suggest me a batch script that can perform this task. I want the output to be a new file. Thanks a lot.

Input File

Code: Select all

"ABC","SALE","08/13/2015","08/18/2015","-15000.00","GLOBAL SALE","FIXED INCOME - SALE"
"TITAN","PURCHASE","08/12/2015","08/17/2015","55000.00","LOCAL PURCHASE","FIXED INCOME - PURCHASE"
"HUN","SHORT PURCHASE","08/12/2015","08/17/2015","60000.00","GLOBAL SHORT PURCHASE","FIXED INCOME - SHORT PURCHASE"
"WMB","PURCHASE","08/13/2015","08/18/2015","5000.00","GLOBAL PURCHASE","FIXED INCOME - PURCHASE"
"","SHORT SALE","08/13/2015","08/14/2015","-89235.90","LOCAL SHORT SALE","FIXED INCOME - SHORT SALE"
"XYZ","SALE","08/13/2015","08/18/2015","-633.00","GLOBAL SALE","FIXED INCOME - SALE"

Desired Output File

Code: Select all

"ABC","SELL","08/13/2015","08/18/2015","-15000.00","GLOBAL SALE","FIXED INCOME - SALE"
"TITAN","BUY","08/12/2015","08/17/2015","55000.00","LOCAL PURCHASE","FIXED INCOME - PURCHASE"
"HUN","SHORT PURCHASE","08/12/2015","08/17/2015","60000.00","GLOBAL SHORT PURCHASE","FIXED INCOME - SHORT PURCHASE"
"WMB","BUY","08/13/2015","08/18/2015","5000.00","GLOBAL PURCHASE","FIXED INCOME - PURCHASE"
"","SHORT SALE","08/13/2015","08/14/2015","-89235.90","LOCAL SHORT SALE","FIXED INCOME - SHORT SALE"
"XYZ","SELL","08/13/2015","08/18/2015","-633.00","GLOBAL SALE","FIXED INCOME - SALE"

[Squashman]

We have given you several examples over the last few months of how to read a CSV file. Could you please post the code you have tried so far.

[shaswat]

Could you please post the code you have tried so far.

Hi, Please find the code below and the resulted output. Please suggest the changes required in my code to get the desired output. Thanks..!!
CODE:

Code: Select all

setLocal EnableDelayedExpansion
For /f "tokens=1-2 delims= " %%a in (INPUT.csv) do (
Set str1=%%a
set str1=!str1:SALE=SELL!
echo !str1!>>OUTPUT1.csv
Set str2=%%b
set str2=!str1:PURCHASE=BUY!
echo !str2!>>OUTPUT.csv
del OUTPUT1.csv
)
PAUSE

Output from my above script

Code: Select all

"ABC","SELL","08/13/2015","08/18/2015","-15000.00","GLOBAL
"TITAN","BUY","08/12/2015","08/17/2015","55000.00","LOCAL
"HUN","SHORT
"WMB","BUY","08/13/2015","08/18/2015","5000.00","GLOBAL
"","SHORT
"XYZ","SELL","08/13/2015","08/18/2015","-633.00","GLOBAL

[Squashman]

Why are you not using 7 tokens and a comma as the delimiter. Your examples shows that you are only changing those values in the 2nd field. So you should only be assigning the token %%b to an environmental variable and doing string replacement on that environmental variable. Then you will echo out all the tokens and the environmental variable to an output file.

Also your example shows that you are only changing those values when the field is those specific values and does not just contain that value, so you will need to check if that field is equal to that value before you change it.

[shaswat]

Why are you not using 7 tokens and a comma as the delimiter. Your examples shows that you are only changing those values in the 2nd field. So you should only be assigning the token %%b to an environmental variable and doing string replacement on that environmental variable. Then you will echo out all the tokens and the environmental variable to an output file.

Also your example shows that you are only changing those values when the field is those specific values and does not just contain that value, so you will need to check if that field is equal to that value before you change it.

Sorry for the delayed response as I was bit busy with some other tasks. I really want to thank you as your inputs helped me a lot to reach very near to my goal.
Please have a look at my code, input and output files. Currently the issue I am facing is, I can't convert the SALE to SELL and PURCHASE to BUY in a single script. Doing so I am getting very absurd output. But if I convert them individually then I am getting the perfect output, but in that way the work is becoming double. So could you please suggest me how can I merge both the scripts to get the output in a single run.

Currently I am running one script 1st and taking the output as the input for the other script.
Input File:

Code: Select all

"ABC","SALE","08/13/2015","08/18/2015","-15000.00","GLOBAL SALE","FIXED INCOME - SALE"
"TITAN","PURCHASE","08/12/2015","08/17/2015","55000.00","LOCAL PURCHASE","FIXED INCOME - PURCHASE"
"HUN","SHORT PURCHASE","08/12/2015","08/17/2015","60000.00","GLOBAL SHORT PURCHASE","FIXED INCOME - SHORT PURCHASE"
"WMB","PURCHASE","08/13/2015","08/18/2015","5000.00","GLOBAL PURCHASE","FIXED INCOME - PURCHASE"
"","SHORT SALE","08/13/2015","08/14/2015","-89235.90","LOCAL SHORT SALE","FIXED INCOME - SHORT SALE"
"XYZ","SALE","08/13/2015","08/18/2015","-633.00","GLOBAL SALE","FIXED INCOME - SALE"

Script for SALE to SELL conversion:

Code: Select all

setLocal EnableDelayedExpansion
for /f "tokens=1-7 delims=|" %%a in ('parseCSV.bat "/o:|" ^<INPUT.csv') do (
Set str1=%%b
set str1=!str1:SALE=SELL!
echo "%%~a",!str1!,"%%~c","%%~d","%%~e","%%~f","%%~g">>OUTPUT_SELL.csv
)

Output for SALE to SELL conversion:

Code: Select all

"ABC","SELL","08/13/2015","08/18/2015","-15000.00","GLOBAL SALE","FIXED INCOME - SALE"
"TITAN","PURCHASE","08/12/2015","08/17/2015","55000.00","LOCAL PURCHASE","FIXED INCOME - PURCHASE"
"HUN","SHORT PURCHASE","08/12/2015","08/17/2015","60000.00","GLOBAL SHORT PURCHASE","FIXED INCOME - SHORT PURCHASE"
"WMB","PURCHASE","08/13/2015","08/18/2015","5000.00","GLOBAL PURCHASE","FIXED INCOME - PURCHASE"
"","SHORT SELL","08/13/2015","08/14/2015","-89235.90","LOCAL SHORT SALE","FIXED INCOME - SHORT SALE"
"XYZ","SELL","08/13/2015","08/18/2015","-633.00","GLOBAL SALE","FIXED INCOME - SALE"

Script for PURCHASE to BUY conversion: Here I'm taking output of the SALE to SELL conversion script as input in this script.

Code: Select all

setLocal EnableDelayedExpansion
for /f "tokens=1-7 delims=|" %%a in ('parseCSV.bat "/o:|" ^<OUTPUT_SELL.csv') do (
Set str1=%%b
set str1=!str1:PURCHASE=BUY!
echo "%%~a",!str1!,"%%~c","%%~d","%%~e","%%~f","%%~g">>OUTPUT_BUY.csv
)

Final Output:

Code: Select all

"ABC","SELL","08/13/2015","08/18/2015","-15000.00","GLOBAL SALE","FIXED INCOME - SALE"
"TITAN","BUY","08/12/2015","08/17/2015","55000.00","LOCAL PURCHASE","FIXED INCOME - PURCHASE"
"HUN","SHORT BUY","08/12/2015","08/17/2015","60000.00","GLOBAL SHORT PURCHASE","FIXED INCOME - SHORT PURCHASE"
"WMB","BUY","08/13/2015","08/18/2015","5000.00","GLOBAL PURCHASE","FIXED INCOME - PURCHASE"
"","SHORT SELL","08/13/2015","08/14/2015","-89235.90","LOCAL SHORT SALE","FIXED INCOME - SHORT SALE"
"XYZ","SELL","08/13/2015","08/18/2015","-633.00","GLOBAL SALE","FIXED INCOME - SALE"

Finally I am getting the desired output but in 2-Steps. Could you please help and suggest the required changes needed in my script so that I don't need 2-Steps to achieve the desired output.

Thanks..!!
Shaswat

[Squashman]

You are going to kick yourself for not seeing how obvious this is.

Code: Select all

setLocal EnableDelayedExpansion
for /f "tokens=1-7 delims=|" %%a in ('parseCSV.bat "/o:|" ^<INPUT.csv') do (
Set str1=%%b
set str1=!str1:SALE=SELL!
set str1=!str1:PURCHASE=BUY!
echo "%%~a",!str1!,"%%~c","%%~d","%%~e","%%~f","%%~g">>OUTPUT.csv
)

[shaswat]

You are going to kick yourself for not seeing how obvious this is.

Code: Select all

setLocal EnableDelayedExpansion
for /f "tokens=1-7 delims=|" %%a in ('parseCSV.bat "/o:|" ^<INPUT.csv') do (
Set str1=%%b
set str1=!str1:SALE=SELL!
set str1=!str1:PURCHASE=BUY!
echo "%%~a",!str1!,"%%~c","%%~d","%%~e","%%~f","%%~g">>OUTPUT.csv
)

Oh Man... Seriously.. Kicked myself...!! Such a silly miss...!!

[Compo]

Here's a quickly thrown together powershell file example:
TestThis.ps1

Code: Select all

$cols = Import-CSV .\input.csv -Header A,B,C,D,E,F,G
foreach ($col in $cols)
{
   $col.B = $col.B -replace('^SALE$','SELL')
   $col.B = $col.B -replace('^PURCHASE$','BUY')
}
$cols | ConvertTo-CSV -notype | Select -Skip 1 | Out-File .\output.csv -Force -Encoding ASCII

just keep TestThis.ps1 and input.csv in the same place if you want to try it, (with appropriate security permissions).

Note
the following version should be sufficient should the desired output have changed to that in your last post showing output.

Code: Select all

$cols = Import-CSV .\input.csv -Header A,B,C,D,E,F,G
foreach ($col in $cols)
{
   $col.B = $col.B.replace('SALE','SELL')
   $col.B = $col.B.replace('PURCHASE','BUY')
}
$cols | ConvertTo-CSV -notype | Select -Skip 1 | Out-File .\output.csv -Force -Encoding ASCII