Replace specific text in csv via commandline

Question

I have data in csv format that gets output from SQL Server. The data has some NULL and N.A. values written out which makes a column character type - it would otherwise have consisted of just integers. Is it possible via batch file statements to find and replace these values with number - say, -1 for NULL and -2 for N.A.. Here is how the data looks like now:

    Col A,  Col B,  Col C,  Col D,  Col E,  Col F,  Col G,  Col H
    NULL,   13,     11,     N.A.,   4710,   N.A.,   1,      1
    5,      NULL,   13,     7070,   N.A.,   4920,   N.A.,   1
    5,      NULL,   12,     8680,   N.A.,   9130,   N.A.,   1

I would like to convert it to this:

    Col A,  Col B,  Col C,  Col D,  Col E,  Col F,  Col G,  Col H
    -1,     13,     11,     -2,     4710,   -2,     1,      1
    5,      -1,     13,     7070,   -2,     4920,   -2,     1
    5,      -1,     12,     8680,   -2,     9130,   -2,     1

It is important to do this via batch statement as this is an intermediate output and is immediately read in by another program. I have taken a look at the findstr command (http://technet.microsoft.com/en-us/library/bb490907.aspx) but I am usure on how to write the exact syntax and perform the replace operation. Helpful inputs much appreciated!

Answer 1

Try this out:

@echo off
setLocal enableDelayedExpansion

set filename=input.txt
set originalText1=NULL
set "replacedText1=-1"
set "originalText2=N.A."
set "replacedText2=-2"

for /f "tokens=*" %%a in ('type %filename%') do (
    set "line=%%a"
    if defined line (
        call set "line=%%line:%originalText1%=%replacedText1%%%"
        call set "line=%%line:%originalText2%=%replacedText2%%%"
        echo !line!>> output.txt
    ) else (
        echo.
    )
)

This code will help you to replace all the instance of NULL to -1 and N.A. to -2. The result will then be stored in output.txt. Hope it helps.

P.S. Note that call set is needed as it will expand the variable that is passed on the same line.

Answer 2

This uses a helper batch file called repl.bat from - https://www.dropbox.com/s/qidqwztmetbvklt/repl.bat

Place repl.bat in the same folder as the batch file or in a folder that is on the path.

It should be more robust and faster.

type file.csv |repl "NULL" "-1" |repl "N\.A\." "-2" >newfile.csv

Answer 3

@echo off
    setlocal enableextensions enabledelayedexpansion

    (for /f "tokens=*" %%f in (a.csv) do if not "%%f"=="" (
            set "line=%%f"
            set "line=!line:NULL=-1!"
            set "line=!line:N.A.=-2!"
            echo(!line!
    )) > b.csv

    endlocal

This will work if, as stated by OP, the file is in the format indicated, containing only integers, NULL and N.A., or at least it does not include special characters.