Batch file replace string in csv with list from another file

Question

i need to replace the numbers in a csv-column with the help of a list in another file. I just started to learn about batch processing and found a script that i adjusted to my needs.

This is the code i use and to solve this problem first, i just take the 3rd column, exchange the numbers and write the old one and the new one into a new file:

@echo off & setlocal enabledelayedexpansion

set "Datei=lohn_prod.csv"
set "MA=Mitarbeiter.csv"
set "Ziel=eGecko.csv"

set "t=%temp%\text.tmp"
if exist "%t%" del "%t%"
for /f "tokens=3 delims=;" %%i in ('findstr /n $ "%Datei%"') do set "Line=%%i" & call :ProcessLine
move "%t%" "%Ziel%"
goto :eof

:ProcessLine
for /f "tokens=1 delims=" %%i in ("%Line%") do set "L=%%i"
if not defined L >>"%t%" echo\& goto :eof
echo %L%
for /f "usebackq tokens=1,2 delims=;" %%i in ("%MA%") do set "L=!L:%%i=%%j!"
>>"%t%" echo %Line%, %L%
goto :eof

The old csv (lohn_prod.csv) looks like this:

15;01.09.2016;105;160,25;1;2100;210;
15;01.09.2016;105;40;1;;217;
15;01.09.2016;105;5;72;;;
15;01.09.2016;107;184;1;2213;210;
15;01.09.2016;107;7,25;1;;213;
15;01.09.2016;107;16;1;;216;
15;01.09.2016;108;241,25;1;3200;210;
15;01.09.2016;108;21,25;1;;222;
15;01.09.2016;113;174,75;1;2111;210;
15;01.09.2016;113;16;1;;216;
...

The other file (Mitarbeiter.csv) i use to replace the 3rd column looks like this:

The problem though is, that it seems to iterate through L again from the beginning on each loop, because i get this result:

105, 4040226
105, 4040226
105, 4040226
107, 4040227
107, 4040227
107, 4040227
108, 40202
108, 40202
113, 40203
113, 40203
115, 40204
170, 40219
170, 40219
172, 4040228
172, 4040228
172, 4040228
185, 40221
185, 40221
185, 40221
...

As it seems, it first finds 105 and replaces it with 40200, then on the next loop it finds the 200 in 40200 and replaces it with 40226, same with 107 and 201 in 40201. Since there is no number 202 in Mitarbeiter.csv, 108 gets correctly replaced with 40202.

Compo · Accepted Answer · 2016-10-26 18:02:39Z

1

This should do it:

@Echo Off
(Set Datei=lohn_prod.csv)
(Set MA=Mitarbeiter.csv)
(Set Ziel=eGecko.csv)
For /F "Tokens=1-2 Delims=;" %%A In ('FindStr "[0-9]*;.*" "%MA%"') Do (
    If Not Defined $[%%A] Set "$[%%A]=%%B") 
(For /F "UseBackQ EOL=; Tokens=1-3* Delims=;" %%A In ("%Datei%") Do (
    Call Echo=%%A;%%B;%%$[%%C]%%;%%D))>%Ziel%
Exit/B

edited Oct 26, 2016 at 18:02

answered Oct 26, 2016 at 16:52

Compo

39k5 gold badges32 silver badges46 bronze badges

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

5 Comments

Thorka Mae Over a year ago

Ok wow, it works like a charm and is so much shorter, gotta study the code tomorrow, thanks very much!

Compo Over a year ago

Just a small warning if Mitarbeiter.csv does not hold every possible third column item, then the third column in eGecko.csv will not contain what you would have wanted.

Thorka Mae Over a year ago

I think i understood your code now, you created a dictionary (in python terms) with $[%%A]=%%B, great solution! And thanks for the info, i have to make that thing foolsproof anyway, so i'll see if i can put in a check for missing items and give out a warning in that case.

Compo Over a year ago

Correct. BTW, I have changed the FindStr pattern to ensure that only lines containing the correct match are found in the 'dictionary' file.

dbenham Over a year ago

Good, though I would prefer delayed expansion instead of CALL with doubled percents.

user6811411 · Accepted Answer · 2016-10-26 19:23:24Z

0

your approach wasn't totally wrong, and with redundancy/flaws removed it isn't much longer.

@echo off
set "Datei=lohn_prod.csv"
set "MA=Mitarbeiter.csv"
set "Ziel=eGecko.csv"
Type NUL > "%Ziel%"
for /f "tokens=1-3* delims=;" %%A in ('Type "%Datei%"'
  ) do call :ProcessLine %%A %%B %%C "%%D%"
goto :eof
:ProcessLine
for /f "tokens=2 delims=;" %%i in ('Findstr /B "%3;" %MA%'
  ) do >>"%Ziel%" echo %1;%2;%%i;%~4
goto :eof

The call may also be eliminated

@echo off
set "Datei=lohn_prod.csv"
set "MA=Mitarbeiter.csv"
set "Ziel=eGecko.csv"
Type NUL > "%Ziel%"
for /f "tokens=1-3* delims=;" %%A in ('Type "%Datei%"'
  ) do for /f "tokens=2 delims=;" %%i in ('Findstr /B "%%C;" %MA%'
  ) do >>"%Ziel%" echo %%A;%%B;%%i;%%D

That culminates in the question what to prefer: readability vs executiontime vs memoryconsumption etc.

answered Oct 26, 2016 at 19:23

user6811411

3 Comments

Thorka Mae Over a year ago

Thanks for this solution, i read somewhere, you could not use the variables of a for loop in another encapsulated for loop, so i didn't waste to much time on trying that approach. Good to see that i was wrong. I'll use the other solution though because of the exectiontime and it keeps the header of the csv intact which i hadn't even posted above.

user6811411 Over a year ago

@Thorka MAe Aber der Header der Spalte MitarbeiterNummer wird in eGecko.csv leer sein.

Thorka Mae Over a year ago

Ich weiss, das ist auch das Ziel. Wir haben einen neuen Dienstleister, dessen csv-Format ganz anders aufgebaut ist, daher das ganze Theater.

Collectives™ on Stack Overflow

Batch file replace string in csv with list from another file

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related