使用批处理脚本将CSV的特定列中的空值替换为0 [英] Use batch scripting to replace null values with 0 in a specific column in a CSV

问题描述

我需要准备一个自动生成的CSV文件，以导入到数据库中.其中一列应该包含整数，但是生成程序(我无法控制)并不总是在该列中放入任何内容.数据库导入将不接受整数列中的空值.还有其他应保留为空的字符串列.

I need to prepare an automatically-generated CSV file for import into a database. One of the columns is supposed to contain integers, but the generating program (which I have no control of) doesn't always put anything in that column. The database import won't accept null values in an integer column. There are other string columns that should stay null.

以下是存在问题的简化CSV:

Here's a simplified CSV with the problem:

"UID","Name","Comment","LicenseNo","DateEntered"
"1","Adam","Likes puppies","4451","2014-05-01"
"2","Barbara","","",2014-05-02"
"3","","Reserved","","2014-05-03"
"4","Donna","","4559","2014-05-04"

我想用"0"替换第4列中的""出现，仅 .

I'd like to replace the "" occurrences with "0" in column 4 only.

我可以隔离每一行中的列并设置一个非nil的替代变量:

I can get as far as being able to isolate the column in each row and set a non-nil substitute variable:

set inputCSV=%1
set outputCSV=%2

for /f "delims==" %%a IN (%inputCSV%) DO (
    set line=%%a
    for /f "tokens=4 delims=," %%b IN ("!line!") DO (
        if %%b=="" (
            set data="0"
        ) else (
            set data=%%b
        )
    )
)

但是，我不知道如何将校正后的行输出到新文件.我正要插入类似的内容:

However, I can't figure out how to output the corrected line to a new file. I was heading towards inserting something like:

if !data!=="0" (
for /f "tokens=1-3 delims=," %%d IN ("!line!") DO set prev=%%d,%%e,%%f
for /f "tokens=5 delims=," %%g IN ("!line!") DO set next=%%g
echo !prev!,!data!,!next! >> %outputCSV%
) else (
echo !line! >> %outputCSV%
)

但是真正的CSV有数十列，因此我将用完FOR变量，而且似乎还有一种更好的方法，我看不到...

But the real CSV has dozens of columns, so I'm going to run out of FOR variables, and it just seems like there's a better way I can't see ...

任何见识都会受到赞赏.

Any insight would be appreciated.

推荐答案

使用tokens=1-4,*

*表示第五个令牌是该行的其余部分"

* means "the fifth token is the rest of the line"

完整代码:

@echo off
setlocal enabledelayedexpansion
set inputCSV=%1
set outputCSV=%2

(for /f "tokens=1-4,* delims=," %%a IN (%inputCSV%) DO (
    if "%%d"=="""" (set "value="000"") else (set "value=%%d")
    echo %%a,%%b,%%c,!value!,%%e
))>%output.csv

编辑以获取评论中的其他信息

EDIT for the additional info in the comment

@echo off
setlocal enabledelayedexpansion
set inputCSV=%1
set outputCSV=%2

(for /f "tokens=*" %%a IN (%inputCSV%) DO (
  set column=0
  set "line="
  for %%i in ( %%a ) do ( 
    set /a column+=1
    set value=%%~i
    if !column!==4 (   
      if "!value!"=="" set "value=0"
    ) 
    set "line=!line!,"!value!"" 
  )
  echo !line:~1!
))>%outputCSV%

将4更改为正确的列号.

注意:每行字符数有限制(不记得多少，可能会影响%%a)

Attention: there is a limit for the number of characters per line (don't remember how much, could affect %%a)

还有一些特殊字符会带来麻烦.

Also some special characters will make trouble.

这篇关于使用批处理脚本将CSV的特定列中的空值替换为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！