Changing value of a specific field based on a diff field

[mid_life_crisis]

I am trying to read the 9th, 11th, 13th, 15th and 17th fields in each line and if they are blank, remove the value from the previous field. I have numerous bugs here, the most obvious of which is that I can't figure out how to change the value associated with the token and write it back to the line variable before writing the line to the output file. I need help, and a lot of it.

Code: Select all

for /f "tokens=*" %%a in (infile.csv) do (
   set line=%%a
setlocal enableDelayedExpansion
   for /f "tokens=8-17 delims=," %%h in ("!line!") do (
      if "%%i"== set "%%h"=
      if "%%k"== set "%%j"=
      if "%%m"== set "%%l"=      
      if "%%o"== set "%%n"=
      if "%%q"== set "%%p"=
  )
      echo !line!>>outfile.txt
)

This isn't actually code but I didn't want to lose the formatting. It is a small file sample. As you can see, there is a header line, which the test would ignore because there are no blank values in it.

Code: Select all

Co Code,Batch ID,File #,Pay #,Shift,Reg Hours,O/T Hours,Hours 3 Code,Hours 3 Amount,Hours 4 Code,Hours 4 Amount,Hours 4 Code,Hours 4 Amount,Hours 3 Code,Hours 3 Amount,Hours 4 Code,Hours 4 Amount
5G6,40,101165,1,2,40,4.9,D,,V,,H,,O,,B,12:00
5G6,40,10047,1,2,40,3.9,D,,V,6:00,H,,O,10:00,B,
5G6,40,133,1,2,40,5.4,D,4:00,V,,H,8:00,O,,B,

[aGerman]

Untested:

Code: Select all

@echo off &setlocal
for /f "tokens=* delims=" %%a in (infile.csv) do (
  set "line=%%a"
  setlocal enableDelayedExpansion
  for /l %%I in (2,1,17) do set "field%%I= "
  set "rest="
  for /f "tokens=1-17* delims=," %%b in ("!line:,=, !") do (
    set "field1=%%b"
    if "%%c" neq "" set "field2=%%c"
    if "%%d" neq "" set "field3=%%d"
    if "%%e" neq "" set "field4=%%e"
    if "%%f" neq "" set "field5=%%f"
    if "%%g" neq "" set "field6=%%g"
    if "%%h" neq "" set "field7=%%h"
    if "%%i" neq "" set "field8=%%i"
    if "%%j" neq "" set "field9=%%j"
    if "%%k" neq "" set "field10=%%k"
    if "%%l" neq "" set "field11=%%l"
    if "%%m" neq "" set "field12=%%m"
    if "%%n" neq "" set "field13=%%n"
    if "%%o" neq "" set "field14=%%o"
    if "%%p" neq "" set "field15=%%p"
    if "%%q" neq "" set "field16=%%q"
    if "%%r" neq "" set "field17=%%r"
    if "%%s" neq "" set "rest=%%s"
    if defined rest set "rest=!rest:, =,"
    if "!field9!"==" " set "field8= "
    if "!field11!"==" " set "field10= "
    if "!field13!"==" " set "field12= "
    if "!field15!"==" " set "field14= "
    if "!field17!"==" " set "field16= "
  )
  >>outfile.txt echo !field1!,!field2:~1!,!field3:~1!,!field4:~1!,!field5:~1!,!field6:~1!,!field7:~1!,!field8:~1!,!field9:~1!,!field10:~1!,!field11:~1!,!field12:~1!,!field13:~1!,!field14:~1!,!field15:~1!,!field16:~1!,!field17:~1!,!rest!
  endlocal
)

Note: the 1st field must never be empty.

Regards
aGerman

[mid_life_crisis]

All I really wanted was a good point in the right direction, but I certainly won't complain that you wrote something for me that works perfectly. As usual, I'm learning stuff from reading the code you give me, and I appreciate the help and the education.

Do you know of a good tutorial for the part of the code where you define fields with tildes and other symbols? That is very interesting but rather difficult to figure out without a guide of some sort.

Thank you again.

[Acy Forsythe]

That's a good solution aGerman, and the same one I came up with working on this a few weeks ago: viewtopic.php?f=3&t=2058

As far as where to learn, here is a great place, just ask what !field2:~1! means and you'll get an answer. It's removing the 1st character from the value !field2! which happens to be a space because of this part: ("!line:,=, !"). See multiple commas would be treated as a single comma by the FOR /F loop using , as the delimeter so you'd lose your empty fields.

What aGerman did to resolve this was to change your commas (,) into comma space (, ) so that you could maintain your field delimeter and still use the , as a FOR delimeter. At the end you have to get rid of the spaces though unless you want them in there.

I use a different method, but it's along the same lines. I add a " at the beginning and end of my string and then replace my commas with "," so I end up with each field being double-quoted then I just use the ~ with my FOR variables %%~a, %%~b, %%~c, etc... to remove the quotes.
So I do something like this:

Code: Select all

...
set "line="%%a""
set "line=!line:,=","!"
...

Then this line can simply be:

Code: Select all

  for /f "tokens=1-17* delims=," %%b in (!line!) do (

instead of

Code: Select all

  for /f "tokens=1-17* delims=," %%b in ("!line:,=, !") do (

And all of your set "fieldx=%%y" lines would be:

Code: Select all

    if "%%c" neq "" set "field2=%%~c"

The upside is that field1 no longer has to contain a value, because if it doesn't it's going to = "" instead of <space>


Back to the question on learning... FOR /? and SET /? will both help you out tremendously with parsing files and you can always post your code here and ask questions most folks here are happy to help.

EDIT (again): Here's a thread discussing the substring/replacement being used here: viewtopic.php?f=3&t=2115