identifying last line in for /f loop in windows batch

Greetings, I have a windows batch that does a for /f token loop.

for /f "tokens=1,2,3,4 delims=() " %%a in ("!line!") do (
				set field=%%a
				set datatype=%%b
				set size=%%c
				set column=!field!:nullable string[max=255] {quote=double, null_field=''};
				if "!datatype!" == "VARCHAR2" (
						set /a size=!size!
						if !size! LEQ 255 (
							set column=!field!:nullable string[max=255] {quote=double, null_field=''};
						) else (
							set column=!field!:nullable string[max=!size!] {quote=double, null_field=''};
						)
					)
				if "!datatype!" == "DATE" (set column=!field!:nullable string[max=50] { quote=double, null_field='' };)
				if "!datatype!" == "NUMBER" (
					if "!size!" == "" (
						set column=!field!:nullable decimal[38,9] { null_field='', default=0, text };
					) else (
						set column=!field!:nullable decimal[!size!] { null_field='', default=0, text };
					)
				)
				echo     !column! >> !BaseDIR!/output/!table!.txt
			)

Open in new window


It works great, however, I need to add an update to the last line of the loop.

I was wondering...
is it's possible, during the loop to idenitify if the line is the last line of the loop?
if not, is there a way to go back and change it after the echo?

in my case, if the last line looks like this:
<field>:nullable decimal[<size>,<scale>] { null_field='', default=0, text }
to
<field>:nullable decimal[<size>,<scale>] { null_field='', default=0, text, width=41 }

Bottom Line....
How do I identify the last line?

Thanks
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
That's possible, but your code extract doesn't process several lines, it only tokenizes the single string contained in !line!.
So what does the outer loop that is setting the !line! variable look like?
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
it's a file....
I'm reading a file line per line.

here's an example:
TABLE: CONSTRUCTION_TYPE_CODE
CONSTRUCTION_TYPE_CODE  VARCHAR2(4)
DESCRIPTION  VARCHAR2(30)
LONG_DESC  VARCHAR2(400)

TABLE: MEASUREMENT
FACILITY_ID  VARCHAR2(15)
UTILIZATION_ID  NUMBER(10,0)
CONDITION_STATUS_CODE  VARCHAR2(12)
AREA_MEASURE  NUMBER(10,2)
OTHER_MEASURE  NUMBER(10,2)
ALTERNATE_MEASURE  NUMBER(10,2)

TABLE: QUANTITY_RATING
INSTALLATION_UIC  VARCHAR2(6)
CATEGORY_CODE  VARCHAR2(5)
ASSETS  NUMBER()
BFR  NUMBER()
QUANTITY_RATING  VARCHAR2(3)
SHORTFALL_COST  NUMBER()

Open in new window


in the first example, the last !line! is LONG_DESC  VARCHAR2(400), so no changes there, however, the second two has NUMBER().  When that happens, and only on the last line (note in Table quantity_rating, there's an ASSETS and BFR columns...these are not to be affected), I need to change the output to add that extra setting.

in the case of Quantity_rating, I need the last line to:
 SHORTFALL_COST:nullable decimal[38,9] { null_field='', default=0, text, width=41 }

in the case of table Measurement, the last column "ALTERNATE_MEASURE" needs to read
ALTERNATE_MEASURE:nullable decimal[10,2] { null_field='', default=0, text, width=12 }

The scales of the decimal[<here>,<and here>] are already set in the script.

Thanks.
0
oBdACommented:
Sorry, but it's not quite clear what your example represents; is it one single file, and you need each last line before an empty line, or are these three separate files that you just copied into the same code box?
And the width is supposed to be hard coded depending on whether the field name is SHORTFALL_COST or ALTERNATE_MEASURE?
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
ok...first of all, thanks for sticking with me.

the source is one single file.
THe example I provided above with the three tables is as exactly as it is seen in the source file.

WHat I am doing is reading the source file and creating other files, using the tablename as the file name.

The question regarding the width is only dependent on the idea of a number in the last field.  All other decimal/number fields and those tables with varchars in the last field are ignored.

If a number is in the last field, I must specify a width.
My question here is how do I identify the last field, so I can change the output?

Thanks
0
oBdACommented:
Can you post the complete loop, including the outer loop that sets the !line! and !table! variables?
From the line alone, it's obviously not possible to determine whether it's a last one, and for the context, I need the file processing. I'd rather change your original than having to create my own and then explain how to port that into your script.
And you still haven't said where the width to be added is supposed to be coming from.
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
ok, let's try this...

for /f "tokens=* delims=" %%f in (%BaseDIR%/master_index.txt) do (
	set line=%%f
	if "!line:~0,6!" == "TABLE:" (
		for /f "tokens=2 delims=: " %%a in ("!line!") do (
			set table=%%a
			echo !table! >> !BaseDIR!/tablelist.txt
			echo record > !BaseDIR!/output/!table!.txt
			echo   {intact, final_delim=none, record_delim='\n', charset='UTF8', delim=','} >> !BaseDIR!/output/!table!.txt
			echo ^( >> !BaseDIR!/output/!table!.txt
		)
		set usetext=true
	) else (
		if "!usetext!" == "true" (
			for /f "tokens=1,2,3,4 delims=() " %%a in ("!line!") do (
				set field=%%a
				set datatype=%%b
				set size=%%c
				set column=!field!:nullable string[max=255] {quote=double, null_field=''};
				if "!datatype!" == "VARCHAR2" (
						set /a size=!size!
						if !size! LEQ 255 (
							set column=!field!:nullable string[max=255] {quote=double, null_field=''};
						) else (
							set column=!field!:nullable string[max=!size!] {quote=double, null_field=''};
						)
					)
				if "!datatype!" == "DATE" (set column=!field!:nullable string[max=50] { quote=double, null_field='' };)
				if "!datatype!" == "NUMBER" (
					if "!size!" == "" (
						set column=!field!:nullable decimal[38,9] { null_field='', default=0, text, width=41 };
					) else (
						for /f "tokens=1,2 delims=," %%a in ("!size!") do (
							set /a scale=%%a
							set /a precision=%%b
						)
						set /a width = !scale!+!precision!
						set column=!field!:nullable decimal[!size!] { null_field='', default=0, text, width=!width! };
					)
				)
				echo     !column! >> !BaseDIR!/output/!table!.txt
			)
		)
	)
)

Open in new window


I'm sorry, I thought I had spoken about the width:
 When that happens, and only on the last line (note in Table quantity_rating, there's an ASSETS and BFR columns...these are not to be affected), I need to change the output to add that extra setting.

in the case of Quantity_rating, I need the last line to:
 SHORTFALL_COST:nullable decimal[38,9] { null_field='', default=0, text, width=41 }

in the case of table Measurement, the last column "ALTERNATE_MEASURE" needs to read
ALTERNATE_MEASURE:nullable decimal[10,2] { null_field='', default=0, text, width=12 }

The width is at the end of the list of items inside the curly brackets {}

THanks
0
oBdACommented:
My question with the width is referring to the "41" and "12".
I'm assuming that there are tables and fields other than SHORTFALL_COST and ALTERNATE_MEASURE that have a data type of number (otherwise you could just take the field name to identify the line in question), so where are these values coming from?
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
ok, so in the examples you see decimal[38,9] and decimal[12,0]...
width is the sum of the two numbers.
The sum is represented by the value !width!, which is already extracted from !size!, which is extracted from the first for /f loops.

Thanks
Evan
0
Bill PrewCommented:
You're in good hands with oBdA here, but did find this interesting and wanted to say a couple of things.  First, I always like to start by figuring out how I would determine if a line in the file was the "last line" of a group, or not?  Until you can come up with some logic to identify the last line you naturally can't script it.

In this case it looks like you can't actually tell a line was the last one just by looking at that line itself.  From your sample data it appears you actually don't know if it's the last line until you read the next line.  It also looks like you would know the prior line was a last line when you hit the next "TABLE:" line, or the end of the file.

Since you can't know if the current line is the last line in a block until you are processing the next line, then what we typically have to do is delay writing the data from the current line until we read the next line.  The first and last lines need to be handled a little differently, but basically you will read each line, decide if this makes the last line read an ending line, and then write out the info associated with the PRIOR line.  Once you do that, you take the current lines data and save it in variable(s) so you have it when you read the next line.

I suspect that is how oBdA will approach it, so I'm not going to work up any code yet, I suspect some is already taking shape ;-).  But wanted to share the general approach that can be used in these situations.

~bp
0
oBdACommented:
Wanted to try something new, so I decided against the "prior line" approach (and it keeps the inside of the loop easier to understand). I corrected the forward slashes in the paths, changed the loop a bit, and I moved the file redirection in front of the echos to prevent spaces at the end of the lines.
In case you're interested: piping the file through find /n /v "" is required because "for /f" skips empty lines.
@echo off
setlocal enabledelayedexpansion

set IndexFile=%BaseDIR%\master_index.txt
set LastLineList=
for /f "tokens=1,2 delims=[] " %%a in ('type "%IndexFile%" ^| find /n /v ""') do (
	set LastLine=%%a
	set /a PreviousLine = %%a - 1
	if "%%b"=="" (set IsNextLineEmpty[!PreviousLine!]=true) else (set IsNextLineEmpty[!PreviousLine!]=)
)
set IsNextLineEmpty[%LastLine%]=true
for /f "tokens=1* delims=[]" %%i in ('type "%IndexFile%" ^| find /n /v ""') do (
	for /f "tokens=1,2,3,4 delims=() " %%a in ("%%j") do (
		if "%%a" == "TABLE:" (
			set table=%%b
			>>!BaseDIR!\tablelist.txt echo !table! 
			> !BaseDIR!\output\!table!.txt echo record 
			>>!BaseDIR!\output\!table!.txt echo   {intact, final_delim=none, record_delim='\n', charset='UTF8', delim=','} 
			>>!BaseDIR!\output\!table!.txt echo ^(
			set usetext=true
		) else (
			if "!usetext!" == "true" (
				set field=%%a
				set datatype=%%b
				set size=%%c
				set column=!field!:nullable string[max=255] {quote=double, null_field=''};
				if "!datatype!" == "VARCHAR2" (
					set /a size=!size!
					if !size! LEQ 255 (
						set column=!field!:nullable string[max=255] {quote=double, null_field=''};
					) else (
						set column=!field!:nullable string[max=!size!] {quote=double, null_field=''};
					)
				)
				if "!datatype!" == "DATE" (
					set column=!field!:nullable string[max=50] { quote=double, null_field='' };
				)
				if "!datatype!" == "NUMBER" (
					set WidthAttribute=
					if "!size!" == "" (
						set size=38,9
						if "!IsNextLineEmpty[%%i]!"=="true" set WidthAttribute=, width=41
					) else (
						if "!IsNextLineEmpty[%%i]!"=="true" (
							for /f "tokens=1,2 delims=," %%a in ("!size!") do (
								set /a scale=%%a
								set /a precision=%%b
							)
							set /a width = !scale!+!precision!
							set WidthAttribute=, width=!width!
						)
					)
					set column=!field!:nullable decimal[!size!] { null_field='', default=0, text!WidthAttribute! };
				)
				>>!BaseDIR!\output\!table!.txt echo     !column!
			)
		)
	)
)

Open in new window

0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Hi oDBA,
thank you so much for helping me out here...
Im' getting this error:
The system cannot find the file specified.
find: unable to access "/N": The system cannot find the file specified.
find: unable to access "/V": The system cannot find the file specified.
find: unable to access "": The system cannot find the file specified.
The system cannot find the file specified.
find: unable to access "/N": The system cannot find the file specified.
find: unable to access "/V": The system cannot find the file specified.
find: unable to access "": The system cannot find the file specified.
The system cannot find the file G:\DS_DATA\src_data\Infads/tablelist.txt.

AM I doing something wrong?  It appears as if it's part of the for/f find sections
Thanks,
Evan
0
oBdACommented:
I suspect that you have a find.exe either in the current folder or somewhere in the path before C:\Windows\system32. Replace all occurrences of "find" with "%systemroot%\system32\find.exe".
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
well, that fixed teh find errors....
still have: The system cannot find the file specified.
The system cannot find the file specified.
The system cannot find the file G:\DS_DATA\src_data\Infads/tablelist.txt.

looking myself in this.
Thanks so much for the effort
0
oBdACommented:
There's still a forward slash between "Infads" and "tablelist.txt".
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
again, thanks so much for hanging with me on this....
In both cases...this line:
for /f "tokens=1,2 delims=[] " %%a in ('type "%IndexFile%" ^| find /n /v ""') do (

the machine does not like %IndexFile%.  As you can see below, the direct approach works.

'type "G:\DS_DATA\src_data\Infads\master_index.txt" ^| %systemroot%\system32\find /n /v ""'

Is there any way to counter the error? without hardcoding?
Thanks
0
oBdACommented:
Leave the line set IndexFile=%BaseDIR%\master_index.txt in the script.
And I just noticed that I accidentially posted an older version, sorry. That should have been the one:
@echo off
setlocal enabledelayedexpansion
set BaseDir=C:\Temp
set IndexFile=%BaseDIR%\master_index.txt
for /f "tokens=1,2 delims=[] " %%a in ('type "%IndexFile%" ^| find /n /v ""') do (
	set LastLine=%%a
	set /a PreviousLine = %%a - 1
	if "%%b"=="" (set IsNextLineEmpty[!PreviousLine!]=true) else (set IsNextLineEmpty[!PreviousLine!]=)
)
set IsNextLineEmpty[%LastLine%]=true
for /f "tokens=1* delims=[]" %%i in ('type "%IndexFile%" ^| find /n /v ""') do (
	for /f "tokens=1,2,3,4 delims=() " %%a in ("%%j") do (
		if "%%a" == "TABLE:" (
			set table=%%b
			>>!BaseDIR!\tablelist.txt echo !table! 
			> !BaseDIR!\output\!table!.txt echo record 
			>>!BaseDIR!\output\!table!.txt echo   {intact, final_delim=none, record_delim='\n', charset='UTF8', delim=','} 
			>>!BaseDIR!\output\!table!.txt echo ^(
			set usetext=true
		) else (
			if "!usetext!" == "true" (
				set field=%%a
				set datatype=%%b
				set size=%%c
				set column=!field!:nullable string[max=255] {quote=double, null_field=''};
				if "!datatype!" == "VARCHAR2" (
					set /a size=!size!
					if !size! LEQ 255 (
						set column=!field!:nullable string[max=255] {quote=double, null_field=''};
					) else (
						set column=!field!:nullable string[max=!size!] {quote=double, null_field=''};
					)
				)
				if "!datatype!" == "DATE" (
					set column=!field!:nullable string[max=50] { quote=double, null_field='' };
				)
				if "!datatype!" == "NUMBER" (
					set WidthAttribute=
					if "!size!" == "" (
						set size=38,9
						if "!IsNextLineEmpty[%%i]!"=="true" set WidthAttribute=, width=41
					) else (
						if "!IsNextLineEmpty[%%i]!"=="true" (
							for /f "tokens=1,2 delims=," %%a in ("!size!") do (
								set /a scale=%%a
								set /a precision=%%b
							)
							set /a width = !scale!+!precision!
							set WidthAttribute=, width=!width!
						)
					)
					set column=!field!:nullable decimal[!size!] { null_field='', default=0, text!WidthAttribute! };
				)
				>>!BaseDIR!\output\!table!.txt echo     !column!
			)
		)
	)
)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Ok. They killed the servers for tonight. I will make the change in the am
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
You've done so much for me...
this is awesome.

First round works....I want to close this, so I can ask another question to give more points if possible for such great work.

Thank you so much.  I'll come back if I need more help.
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.