Parsing text file to make a CSV

[Squashman]

So I have an Overall standings file that is just a plain text file but I want to turn it into a csv.

Input looks like this. The fields from left to right are:
Place,Name,Rider Number,Team name,Points

Code: Select all

1 Peter Schmitt (1133) Brown Cty Comp 1350
2 Michael Gierlach (1108) Independent 1311
3 Johnathan Van Lanen (1134) West Bend HS 1267
4 Richard Bailey (1100) Forestville Combined 1188
5 Tyler Johnson Schneider (1111) Sun Prairie Comp 900

Need the output to be

Code: Select all

1,Peter Schmitt,1133,Brown Cty Comp,1350
2,Michael Gierlach,1108,Independent,1311
3,Johnathan Van Lanen,1134,West Bend HS,1267
4,Richard Bailey,1100,Forestville,Combined,1188
5,Tyler Johnson Schneider,1111,Sun Prairie Comp,900

Now it is easy enough to split out the Place, Name and rider number using two for loops.

Code: Select all

@echo off
FOR /F "TOKENS=1-3 delims=()" %%G IN (overall.txt) DO (
   FOR /F "TOKENS=1* delims= " %%J IN ("%%~G") DO (
      >>Overall.csv ECHO %%J,%%K,%%H,%%I
   )
)

But that leaves me with this.

Code: Select all

1,Peter Schmitt ,1133, Brown Cty Comp 1350
2,Michael Gierlach ,1108, Independent 1311
3,Johnathan Van Lanen ,1134, West Bend HS 1267
4,Richard Bailey ,1100, Forestville Combined 1188
5,Tyler Johnson Schneider ,1111, Sun Prairie Comp 900

I still need to get the Team Name separated from the riders total points. And there is a trailing space after the name and a trailing space before the Team Name. I think I am finally going to be forced to use REPL.

[aGerman]

The following will fail if the Team Name contains numbers.

Code: Select all

@echo off &setlocal DisableDelayedExpansion
>"overall.csv" (
  for /f "usebackq tokens=1,2* delims=()" %%g in ("overall.txt") do (
    set "ridernum=%%h"
    set "points=%%i"
    for /f "tokens=1*" %%j in ("%%g") do (
      set "place=%%j"
      set "name=%%k"
    )
    for /f "delims=1234567890" %%j in ("%%i") do (
      set "teamname=%%j"
      setlocal EnableDelayedExpansion
      echo !place!,!name:~0,-1!,!ridernum!,!teamname:~1,-1!,!points:%%j=!
      endlocal
    )
  )
)

Regards
aGerman

[Aacini]

The code below does not have restrictions on the number of words that comprise the Name or the Team name; the only requirement is that the Rider Number be enclosed in parentheses, and that the Points be the last field.

Code: Select all

@echo off
setlocal EnableDelayedExpansion

(for /F "tokens=1-3 delims=()" %%a in (overall.txt) do (
   set "output="
   for %%d in (%%a) do (
      if not defined output (
         set "output=%%d,"
      ) else (
         set "output=!output!%%d "
      )
   )
   set "output=!output:~0,-1!,%%b,"
   set "token="
   for %%d in (%%c) do (
      if defined token set "output=!output!!token! "
      set "token=%%d"
   )
   echo !output:~0,-1!,!token!
)) > overall.csv

Output:

Code: Select all

1,Peter Schmitt,1133,Brown Cty Comp,1350
2,Michael Gierlach,1108,Independent,1311
3,Johnathan Van Lanen,1134,West Bend HS,1267
4,Richard Bailey,1100,Forestville Combined,1188
5,Tyler Johnson Schneider,1111,Sun Prairie Comp,900

Antonio

[Squashman]

Antonio, that is quite ingenious. Wish I would have thought of that.

[foxidrive]

Here's a repl.bat solution too:

Code: Select all

type overall.txt|repl "(.*?) (.*?) \((.*?)\) (.*) (.*)" "$1,$2,$3,$4,$5"

This uses a helper batch file called `repl.bat` (by dbenham) - download from: https://www.dropbox.com/s/qidqwztmetbvklt/repl.bat

[Squashman]

I am a bit confused by this output.

Code: Select all

@echo off

type overall.txt|repl "(.*?) (.*?) \((.*?)\) (.*) (.*)" "$1,$2,$3,$4,$5"
echo.
>>overall.csv type overall.txt|repl "(.*?) (.*?) \((.*?)\) (.*) (.*)" "$1,$2,$3,$4,$5"
type overall.csv

pause

output

Code: Select all

1,Peter Schmitt,1133,Brown Cty Comp,1350
2,Michael Gierlach,1108,Independent,1311
3,Johnathan Van Lanen,1134,West Bend HS,1267
4,Richard Bailey,1100,Forestville Combined,1188
5,Tyler Johnson Schneider,1111,Sun Prairie Comp,900

1 Peter Schmitt (1133) Brown Cty Comp 1350
2 Michael Gierlach (1108) Independent 1311
3 Johnathan Van Lanen (1134) West Bend HS 1267
4 Richard Bailey (1100) Forestville Combined 1188
5 Tyler Johnson Schneider (1111) Sun Prairie Comp 900

[Squashman]

Ok, I just captured the output with the FOR command. Not understanding why the redirection is not working without the FOR command.

Code: Select all

FOR /F "delims=" %%G IN ('type overall.txt ^|repl "(.*?) (.*?) \((.*?)\) (.*) (.*)" "$1,$2,$3,$4,$5"') DO >>overall.csv echo %%G

[penpen]

The redirection is working, but the redirection has a higher precedence than the pipe. Your command is equal to this one, so nothing is piped, and repl gets no input:

Code: Select all

(>>overall.csv type overall.txt)|...

penpen

[foxidrive]

Yeah! I wouldn't have encountered that.

A solution is to enclose the command itself in parentheses.

Code: Select all

@echo off
>>overall.csv (type overall.txt|repl "(.*?) (.*?) \((.*?)\) (.*) (.*)" "$1,$2,$3,$4,$5")
pause