Use batch scripting to replace null values with 0 in a specific column in a CSV

Question

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.

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"

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

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%
)

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.

Stephan · Accepted Answer · 2014-06-08 18:30:19Z

0

use tokens=1-4,*

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

Complete code:

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

change the 4 to the correct column number.

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.

edited Jun 8, 2014 at 18:30

answered Jun 8, 2014 at 5:38

Stephan

56.8k11 gold badges66 silver badges97 bronze badges

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

3 Comments

Drakesden Over a year ago

If I only had 5 columns this would work -- but as I said above, the actual CSV has dozens of columns (actually over 100). I should have clarified that the column that needs changing is around 30. I appreciate it, though.

Stephan Over a year ago

how many colums, doesn't matter, because * takes all the rest until the end of the line. However that the column to change is that late in the line does matter. You could get around this by using another for and a counter that parses one line provided by the first for

Drakesden Over a year ago

Thank you -- the revision works for exactly the problem I asked, and it's superfast. I have some special characters to watch out for (e.g. commas in the middle of a comment) but by prefixing some string substitutions I was able to get this to work. I also was able to turn the column into a variable, & a slight tweak allowed me to use it to take commas out of a column. Much appreciated!

foxidrive · Accepted Answer · 2014-06-08 06:02:53Z

0

This should be a robust and quick way to do it:

This uses a helper batch file called repl.bat (by dbenham) - download 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.

@echo off
type "file.csv" | repl "^(.*?,.*?,.*?),\q\q,(.*)" "$1,\q0\q,$2" x >"newfile.csv"

answered Jun 8, 2014 at 6:02

foxidrive

41.4k11 gold badges61 silver badges71 bronze badges

1 Comment

Drakesden Over a year ago

Thanks -- it looks like this would work, but I was hoping not to add a helper batch file into the mix, and with the number of columns I'm working with, I'd have to add about 30 .*?'s, so I'm going with @Stephan 's answer.

Collectives™ on Stack Overflow

Use batch scripting to replace null values with 0 in a specific column in a CSV

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related