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