Replace column values from a csv file with batch

Question

I have a commma seperated csv-file like this:

ID,USER_ID, COL3_STR, COL4_INT
id1,username1,exampleA, 5
id2,username1,exampleB,0
id3,username2,NULL,-1
id4,username3,,3,false,20

Each value from the 2nd column USER_ID must be replaced with testusername (except the header "USER_ID"). The values are different, so I can't search a defined string.

My idea was to use a for-loop and get the second token from each line to get the username. For example:

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET currentDir=%~dp0
SET "srcfile=%currentDir%\inputfile.csv"
SET "outfile=%currentDir%\result.csv"

for /f "tokens=2 delims=," %%A IN (%srcfile%) DO (
    ECHO %%A)

ECHO done
PAUSE

Output:

USER_ID
username1
username1
username2
username3

So the 2nd column of the (new) csv file must look like:

USER_ID
testusername
testusername
testusername
testusername

I saw another question with an helpful answer. Example: When each username is "admin":

(
  for /f "delims=" %%A in (%srcfile%) do (
    set "line=%%A"
    for /f "tokens=2 delims=," %%B in ("admin") do set "line=!line:%%B=testuser!"
    echo !line!
  )
)>%outfile%

But this works only for a defined string. It's my first batch-script and I don't know how to "combine" this for my situation. I hope sombody can help me. Must work for Windows 7 and 10.

There is no 2nd token in "admin", so nothing gets replaced. It could work when you replace "admin" with "%%A". But on the other hand, you don't need to extract the username - you throw it away anyway (see my answer for a more straightforward solution). — Stephan
– Stephan, Commented Aug 11, 2021 at 19:22

Stephan · Accepted Answer · 2021-08-11 14:02:22Z

1

You need all the tokens (for writing the modified file), not just the second one:

for /f "tokens=1,2,* delims=," %%A in (%srcfile%) do echo %%A,testuser,%%C

(where * is "the rest of the line, undelimited"). %%B would be the username, so just write the replacement string instead.

You could use an if statement to process the first line differently, or you process it separately:

<"%srcfile%" set /p header=
(
    echo %header%
    for /f "skip=1 tokens=1,2,* delims=," %%A in (%srcfile%) do echo %%A,testuser,%%C
) > "%outfile%"

answered Aug 11, 2021 at 14:02

Stephan

56.8k11 gold badges66 silver badges97 bronze badges

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

2 Comments

RuhrpottDev Over a year ago

Hey, thank you very much for your help (again), also for your comment, this helps me for understanding. It works. But I have another issue: I get each first column value at the commandline with the message (translated): "The command 'id1' is either misspelled or could not be found". How can I prevent this?

Stephan Over a year ago

doesn't happen for me. Do you use %%A without echo anywhere? Another possible reason: srcfile is not coded as ANSI and/or has non-Windows line endings.

aschipfl · Accepted Answer · 2021-08-11 20:58:36Z

The following script (let us call it repl_2nd.bat) replaces the values in the second column of a CSV file and correctly handles empty fields (where separators occur next to each other like ,,):

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "_FILE=%~1"          & rem // (path to input file; `%~1` is first argument)
set "_NVAL=testusername" & rem // (new string for second column)
set "_SEPC=,"            & rem // (separator character usually `,`)

rem // Especially handle first line:
< "%_FILE%" (
    set "HEAD=" & set /P HEAD=""
    setlocal EnableDelayedExpansion
    echo(!HEAD!
    endlocal
)
rem // Read input file line by line:
for /F usebackq^ skip^=1^ delims^=^ eol^= %%L in ("%_FILE%") do (
    rem // Store current line string:
    set "LINE=%%L"
    rem // Toggle delayed expansion to avoid loss of `!`:
    setlocal EnableDelayedExpansion
    rem /* Replace each separator `,` by `","` and enclose whole line string in `""`,
    rem    resulting in all items to become quoted, ven empty ones, hence avoiding
    rem    adjacent separators, which would become collapsed to one by `for /F`;
    rem    then split the edited line string at the first and second separators: */
    for /F "tokens=1,2,* delims=%_SEPC% eol=%_SEPC%" %%A in (^""!LINE:%_SEPC%="^%_SEPC%"!"^") do (
        rem /* Unquote the first item, then join a separator and the replacement string;
        rem    then remove the outer pair of quotes from the remaining line string: */
        endlocal & set "REPL=%%~A%_SEPC%%_NVAL%" & set "REST=%%~C"
        rem // Append the remaining line string with `","` replaced by separators `,`:
        setlocal EnableDelayedExpansion & echo(!REPL!%_SEPC%!REST:"%_SEPC%"=%_SEPC%!
    )
    endlocal
)

endlocal
exit /B

To use the script o a file in the current working directory, use this command line:

repl_2nd.bat "inputfile.csv"

To store the output to another file, use the following command line:

repl_2nd.bat "inputfile.csv" > "outputfile.csv"

Collectives™ on Stack Overflow

Replace column values from a csv file with batch

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related