Batch CSV handling empty tokens while using usebackq in for-loop

Question

I found many topics on the problem of handling empty tokens, but i can't get it to work on my code. The only difference i can see is that i am using usebackq which the others didn't, but if i remove it, the rest of the code won't work. Could anyone point me to the right direction?

The code is supposed to take a csv, switch the value in the 3rd column with another value that is stored in Mitarbeiter.txt and do the same with the last value and it's pair in Lohnarten.txt, then write that into the new csv:

@Echo Off 
SetLocal EnableDelayedExpansion

(Set Datei=lohn_prod.csv)
(Set MA=Mitarbeiter.txt)
(Set LA=Lohnarten.txt)
(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 "Tokens=1-2 Delims=;" %%A In ('FindStr "[0-9]*;.*" "%LA%"') Do (
    If Not Defined $[%%A] Set "_[%%A]=%%B")

For /F "Delims=" %%X In ("%Datei%") Do (
    Set Line=%%X
    Set Line=!Line:;=";"!
    (For /F "UseBackQ EOL=; Skip=13 Tokens=1-7 Delims=;" %%A In ("!Line!") Do (
        Call Echo=%%~A;%%~B;%%$[%%~C]%%;%%~D;%%~E;%%~F;%%_[%%~G]%%;)
    )
)>%Ziel%
Exit/B

I am trying to encapsulate my semicolon-delimiters in quote marks and remove them before writing everything into the new file, but that doesn't work.

Instead of getting this csv:

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;

into this desired form:

15;01.09.2016;40200;160,25;1;2100;101;
15;01.09.2016;40200;40;1;;103;
15;01.09.2016;40200;5;72;;;
15;01.09.2016;40201;184;1;2213;101;
15;01.09.2016;40201;7,25;1;;423;
15;01.09.2016;40201;16;1;;102;

i instead get this, where the last values get moved into the 2nd last position if there is an empty token in front of them and thus also won't get replaced by the values in Lohnarten.txt:

15;01.09.2016;40200;160,25;1;2100;101;
15;01.09.2016;40200;40;1;217;;
15;01.09.2016;40200;5;72;;;
15;01.09.2016;40201;184;1;2213;101;
15;01.09.2016;40201;7,25;1;213;;
15;01.09.2016;40201;16;1;216;;

There is a neat little hybrid batch/jscript utility that Dave Benham wrote that helps parse delimited files because of the known issues with the FOR command. dostips.com/forum/viewtopic.php?t=5702 — Squashman
– Squashman, Commented Oct 27, 2016 at 12:38
Thanks, will look into it, but i'd like to know why my code doesn't work, as i suspect it's just some minor syntax-error that i don't see. — Thorka Mae
– Thorka Mae, Commented Oct 27, 2016 at 12:49
Thanks for pointing out the error! I also made the other changes and it's still working. I am new to batch-scripting and got help with the code. The original code didn't have delayed expansion enabled, thus the call i guess. I had to enable it to get around the actual problem with the empty fields. I found a solution now where Set Line is used differently and moved the UseBackQ up as you already suggested and this did the trick, i don't understand though, why adding the delimiters to Set Line actually works. Could someone maybe explain it after i post the solution? — Thorka Mae
– Thorka Mae, Commented Oct 27, 2016 at 13:43
@ThorkaMae, I provided an answer now and deleted my comments, including some explanations what I did; if you need more details, just comment on the answer... — aschipfl
– aschipfl, Commented Oct 27, 2016 at 14:03

aschipfl · Accepted Answer · 2016-10-27 14:50:46Z

1

There are some problems in your code, which make me disbelieve that it produces the output you claimed (perhaps just copy-paste issues); anyway, here are the issues:

in the if defined query in the second for /F loop, there seems to be the wrong variable $[%%A] queried, I think it should read _[%%A];
instead of Set Line=!Line:;=";"!, you should write Set Line="!Line:;=";"!", so each semicolon-separated item is enclosed within ""; then the ~ modifier of the for /F variables works reliably for later removal of the quotes;
nesting of the redirection part > is wrong, you need to put parenthesis around the outer for /F loop rather than the inner one, because otherwise, every line is redirected separately, overwriting the previous ones; I put the redirection part to the beginning of the parenthesised block, just because I like it more that way, but you can put it at the end of the block either; by the way, the redirection target should be quoted like "%Ziel%";
the UseBackQ option must be stated at the outer for /F loop but not at the inner one;

Here are some additional issues:

the Set syntax should be changed so that the entire assignment expression appears in between "" to avoid trouble with special characters;
since you have delayed expansion enabled anyway, there is no need to use Call Echo %%Variable%%, you can use Echo !Variable! instead;
I changed the indent and the positions of closing parentheses so that the code becomes more legible;

Here is the fixed script:

@Echo Off
SetLocal EnableDelayedExpansion

Set "Datei=lohn_prod.csv"
Set "MA=Mitarbeiter.txt"
Set "LA=Lohnarten.txt"
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 "Tokens=1-2 Delims=;" %%A In ('FindStr "[0-9]*;.*" "%LA%"') Do (
    If Not Defined _[%%A] Set "_[%%A]=%%B"
)

> "%Ziel%" (
    For /F "UseBackQ Delims=" %%X In ("%Datei%") Do (
        Set "Line=%%X"
        Set Line="!Line:;=";"!"
        For /F "EOL=; Skip=13 Tokens=1-7 Delims=;" %%A In ("!Line!") Do (
            Echo %%~A;%%~B;!$[%%~C]!;%%~D;%%~E;%%~F;!_[%%~G]!;
        )
    )
)
Exit /B

edited Oct 27, 2016 at 14:50

answered Oct 27, 2016 at 13:56

aschipfl

35.3k12 gold badges62 silver badges106 bronze badges

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

1 Comment

Thorka Mae Over a year ago

Thanks for the help, works as intended. The Skip=13 is needed because the original csv has a 13-line header which i don't need in the new file and it seems that this and the placement of UseBackQ in the wrong For Loop were the main issues. I guess the lines in the nested for-loop are beeing worked through line by line and with Skip that line gets removed completely. Putting it into the outer For-Loop makes it then work as intended. Also thanks for pointing out the redirection part, i didn't understand it at all, but got it now and it makes the code execute much faster than in my solution.

Thorka Mae · Accepted Answer · 2016-10-27 13:53:23Z

I used aschipfls suggestions here and a solution from another question which did the trick. I don't understand though, what this line actually does:

Set Line=%%X;;;;;;;

Could someone explain it to me?

The tweaked code that now works is this (i used Line:;=;" instead of Line:;=";" at the end because else there was one quote mark after the first token that didn't get removed):

@Echo Off 
SetLocal EnableDelayedExpansion

(Set Datei=lohn_prod.csv)
(Set MA=Mitarbeiter.txt)
(Set LA=Lohnarten.txt)
(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 "Tokens=1-2 Delims=;" %%A In ('FindStr "[0-9]*;.*" "%LA%"') Do (
    If Not Defined _[%%A] Set "_[%%A]=%%B")

Type NUL > "%Ziel%"
For /F "UseBackQ Delims=" %%X In ("%Datei%") Do (
    Set Line=%%X;;;;;;;
    Set Line="!Line:;=;"!"
    (For /F "EOL=; Tokens=1-7 Delims=;" %%A In ("!Line!") Do (
        Echo=%%~A;%%~B;!$[%%~C]!;%%~D;%%~E;%%~F;!_[%%~G]!;)
    )
)>>%Ziel%
Exit/B

Collectives™ on Stack Overflow

Batch CSV handling empty tokens while using usebackq in for-loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related