Windows Batch to read file and parse lines into tokens and variables

Question

I've made a good deal of headway by searching this site and learning the ridiculous language that is Windows batch scripting, but I'm now at a point where I'm stuck. I have a text file with a variable number of lines, each of which looks something like:

AA8315,"United States",N777AN,"American Airlines",AAL98,B772,"Boeing 777-223",AAL,"2013-06-11 23:30:47.923","2013-06-12 00:01:14.459"

My batch file:

set THEDATE=2013-06-12
set THEDATABASE=c:\Kinetic\BaseStation\Basestation.sqb
set THECSVFILE=c:\Flights.csv
set THEOUTPUTFILE=c:\FlightsNew.csv
set THISLINE=""

if exist %THECSVFILE% del %THECSVFILE%
if exist %THEOUTPUTFILE% del %THEOUTPUTFILE%

:: allow time for the csv file to be deleted
timeout /t 2 /nobreak

c:\sqlite3.exe -header -csv %THEDATABASE% "select Aircraft.ModeS, Aircraft.ModeSCountry as Country, Aircraft.Registration as Reg, Aircraft.RegisteredOwners as Owner, Flights.Callsign, Aircraft.ICAOTypeCode as Type, Aircraft.Type as Model, Aircraft.OperatorFlagCode as 'Op Flag', Flights.StartTime as 'First Seen', Flights.EndTime as 'Last Seen' from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) where Flights.EndTime like '%THEDATE% %%' order by Flights.EndTime DESC;" >> %THECSVFILE%

::allow time for the csv to be written to file
timeout /t 5 /nobreak

::read %THECSVFILE% and loop through each line
for /F "usebackq tokens=* delims=" %%A in (%THECSVFILE%) do (
    set the_line=%%A
    call :process_line
)

:process_line
for /F "usebackq tokens=1,2,3,4,5,6,7,8,9,10 delims=[,]" %%1 in (%the_line%) do (
    set hexcode=%%1
    set country=%%2
    set reg=%%3
    set owner=%%4
    set callsign=%%5
    set planetype=%%6
    set model=%%7
    set opflag=%%8
    set firstseen=%%9
    set lastseen=%%10
    set THISLINE=%hexcode%,%country%,%reg%,%owner%,%callsign%,%planetype%,%model%,%opflag%,%firstseen%,%lastseen%
    echo %THISLINE% > %THEOUTPUTFILE%
)

(I'm assigning the tokens to variables because I will be doing additional validation and formatting of them later. I need to get this part working first!)

When executed, the script does indeed loop through each line of the file, however it does not seem to be assigning %%1 to the variable hexcode.

The output of the executed command looks like this:

C:\>for /F "usebackq tokens=1,2,3,4,5,6,7,8,9,10 delims=[,]" %1 in (AA8315 "United States" N777AN "American Airlines" AAL98 B772 "Boeing 777-223" AAL "2013-06-11 23:30:47.923" "2013-06-12 00:01:14.459") do (
set hexcode=%1
 set country=%2
 set reg=%3
 set owner=%4
 set callsign=%5
 set planetype=%6
 set model=%7
 set opflag=%8
 set firstseen=%9
 set lastseen=%10
 set THISLINE=,"United States" ,N807FD ,"Fedex Express" ,FDX1378 ,,"Airbus A310-324" ,FDX ,"2013-06-12 22:56:54.639" ,"2013-06-12 23:05:31.822"
 echo ""  1>c:\FlightsNew.csv
)
The system cannot find the file AA8315.

Any help is greatly appreciated!

+1 for doing your own research and trying to create a solution on your own before asking questions, and for including your code in your question! — James L., Jul 03 '13 at 16:47
HOWEVER - I believe youd be better-off by passing the entire line as a parameter to the procedure, then building your variables from %1..%9 with `SHIFT` in the conventional manner. That way, country names like "Congo, Democratic Republic of the" are treated as a single string. Try it by entering a comma between "United" and "States" in your data file and look at the difference. — Magoo, Jul 03 '13 at 17:45

score 4 · Answer 1 · edited Mar 09 '15 at 14:06

4

this works here:


    for /f "tokens=1-10delims=," %%a in ("AA8315,"United States",N777AN,"American Airlines",AAL98,B772,"Boeing 777-223",AAL,"2013-06-11 23:30:47.923","2013-06-12 00:01:14.459"") do (
     set hexcode=%%a
     set country=%%b
     set reg=%%c
     set owner=%%d
     set callsegn=%%e
     set planefype=%%f
     set model=%%g
     set opflag=%%h
     set firstseen=%%i
     set lastseen=%%j
     set THISLINE=%%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,%%j
    )
    >"c:\FlightsNew.csv" echo %THISLINE%

I'm not sure, why you need the tokens.

edited Mar 09 '15 at 14:06

propjk007

655
1
10
18

answered Jul 03 '13 at 16:32

Endoro

37,015
8
50
63

As I said in my original post, I need the tokens to do some additional validation and formatting of the output, which I haven't coded yet. – SonicGoose Jul 03 '13 at 17:14
1

@user2547366 excuse me please, I*m kind of mentally impaired and can't read long texts. – Endoro Jul 03 '13 at 17:22

score 3 · Accepted Answer · answered Jul 03 '13 at 16:46

3

I have always had problems with comma separated values in a for loop. Here's what I did to make your code work.

Test.txt

AA8315,"United States",N777AN,"American Airlines",AAL98,B772,"Boeing 777-223",AAL,"2013-06-11 23:30:47.923","2013-06-12 00:01:14.459"

BatchFile.bat

set THECSVFILE=test.txt

::read %THECSVFILE% and loop through each line
for /F "usebackq tokens=* delims=" %%A in (%THECSVFILE%) do (
    set the_line=%%A
    call :process_line
)
goto TheEnd

:process_line
for /F "usebackq tokens=1,2,3,4,5,6,7,8,9,10 delims=~" %%1 in ('%the_line:,=~%') do (
    set hexcode=%%1
    set country=%%2
    set reg=%%3
    set owner=%%4
    set callsign=%%5
    set planetype=%%6
    set model=%%7
    set opflag=%%8
    set firstseen=%%9
    set lastseen=%%10
    set THISLINE=%hexcode%,%country%,%reg%,%owner%,%callsign%,%planetype%,%model%,%opflag%,%firstseen%,%lastseen%
    echo %THISLINE% > %THEOUTPUTFILE%
)

:TheEnd

Notice the :process_line for loop. I had to add single quotes around the %the_line% so it didn't try to interpret the string as a filename. Then I replaced all commas with the ~ character, and used the ~ character as the delimiter. It may not work precisely with all your data (if it contains single quotes or the ~ character), but it does work with this one record and gets you moving in the right direction again.

answered Jul 03 '13 at 16:46

James L.

9,384
5
38
77

This works! However, and rather puzzlingly, the first two lines of the output file are the last two lines of the input file -- reversed! The last line of the input file is blank, and the line before it contains data. In the output file, I'm getting the blank line as the first line, then the second-last line. Then the rest of the file starts from line 1 of the input file and continues in order so that the last line of the output file is the third-last line of the input file! – SonicGoose Jul 03 '13 at 17:11
This, obviously, can not work. There are so many errors, I stopped counting :-), and the output is `echo is OFF`. – Endoro Jul 03 '13 at 17:17
It doesn't work. Look at the value in the output file of the last element, `lastseen` - you'll find that it's the same as the first, `hexcode` with an appended `0` Furthermore, to work, the closing parenthesis of the `DO` in `:process_line` would have to be after the `set lastseen` - otherwise the values reported will be the values **BEFORE** taht `for/f` was executed - possibly the stale values from the last run. Further, the `>` would need to be `>>` to append the new data line-by-line. – Magoo Jul 03 '13 at 17:29
To qualify, this works when the variables are changed to letters rather than numbers AND the last line is changed to read `echo %THISLINE% >> %THEOUTPUTFILE%` (with two >). – SonicGoose Jul 03 '13 at 17:36
Why are you replacing commas with ~? It would appear that the problem was that he was assigning the reserved %1-%9 script arguments as opposed to anything else. Perhaps you personally encountered issues with comma delimitation. If so, please specify what they are. – MetaChrome Nov 15 '13 at 10:32
1

@MetaChrome - That was my initial thought too, but surprisingly, using `%%1` thru `%%10` works just fine. I'm not sure what it was about the comma, they've always been problematic for me. Instead of digging deep to find out why, I've always just replaced them with `~` and used it as the delimiter, which has always been effective. Perhaps someday when I have some spare time I'll try to discover why... – James L. Nov 15 '13 at 17:37

score 2 · Answer 3 · answered Jul 03 '13 at 16:36

2

You can only use letters fro the metavariable (%%1 in your code) - but the lower-case and upper-case letters are distinct.

Yes, you can use some other characters, but the contiguous blocks avaliable for "tokens=1-10" (which is an easier version of 1,2,3...) are a..z and A..Z

%0..%9 are reserved for the parameters to the batch or batch-procedure.

answered Jul 03 '13 at 16:36

Magoo

77,302
8
62
84

That's what I thought too, but as it turns out, I was wrong. You can use %1..%n in a `for` loop. – James L. Jul 03 '13 at 16:41
No worries. It comes as a complete surprise to me. Learned it just now when trying his code... – James L. Jul 03 '13 at 16:49
I have tried using letters instead of numbers (%%a instead of %%1 and %~a instead of %~1) and still get the same result. "The system cannot find the file AA8315." – SonicGoose Jul 03 '13 at 16:49
I had to change to using letters from numbers. Numbers %%1 through to %%9 worked, but $$10 was read as %%1 with a 0 on the end. %%a through %%j works fine now with modified code supplied by James. – SonicGoose Jul 03 '13 at 17:16

Windows Batch to read file and parse lines into tokens and variables

3 Answers3

Test.txt

BatchFile.bat

Linked