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!

I don't have a Windows machine in front of me to be able to hack at this, but I imagine you'll need to use the for command to iterate over the lines in the file and modify the tokens as you output them. You'll also want to be mindful of how EnableDelayedExpansion affects behavior inside the loop. It'd be a much easier task if you could just modify the SQL query to produce the results you desire. Also, since you're working from Windows command line consider Windows Scripting Host running cscript — Marc
– Marc, Commented Dec 16, 2013 at 7:13

MC ND · Accepted Answer · 2013-12-16 09:42:14Z

5

@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.

answered Dec 16, 2013 at 9:42

MC ND

71.1k8 gold badges95 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sriramn Over a year ago

This one is definitely faster than @Dale's version.

foxidrive · Accepted Answer · 2013-12-16 11:48:58Z

5

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

answered Dec 16, 2013 at 11:48

foxidrive

41.4k11 gold badges61 silver badges71 bronze badges

2 Comments

sriramn Over a year ago

This file does the replacement really really fast!!

foxidrive Over a year ago

It's actually using Jscript via Windows Scripting Host in a hybrid batch file so it is a lot faster than batch code, and doesn't suffer from poison characters - but is still easy to use. Aacini devised findrepl.bat which is a grep-like tool using the same technique. dbenham wrote repl.bat

Dale · Accepted Answer · 2013-12-16 07:20:19Z

2

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.

answered Dec 16, 2013 at 7:20

Dale

1,9111 gold badge16 silver badges24 bronze badges

1 Comment

LS_ᴅᴇᴠ Over a year ago

+1 for right direction, but check @MCND code performing single > write, instead of multiple >> and avoiding type command.

Collectives™ on Stack Overflow

Replace specific text in csv via commandline

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related