通过命令行替换csv中的特定文本 [英] Replace specific text in csv via commandline

问题描述

我有csv格式的数据，该数据从SQL Server获取输出.数据具有写出的NULL和N.A.值，这些值构成了列字符类型-否则它将仅由整数组成.是否可以通过批处理文件语句查找并用数字替换这些值-例如，对于NULL为-1，对于N.A.为-2.数据如下所示:

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

我想将其转换为此:

    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

通过批处理语句执行此操作很重要，因为这是中间输出，并且立即被另一个程序读入.我看过findstr命令( http://technet.microsoft .com/en-us/library/bb490907.aspx )，但我对如何编写准确的语法并执行替换操作感到满意.有用的输入，不胜感激！

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

如果按照OP所述，文件采用所示格式，仅包含整数，NULL和N.A.，或者至少不包含特殊字符，则此方法将起作用.

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.

这篇关于通过命令行替换csv中的特定文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！