nested file parsing loops

[prakashG]

In the last 6 lines of code inside the condition of ticker1 != Ticker2, The text is not able to add Line2
the Text stays as : ,,,,

file2 is bigger than file1 in number of lines.
What i am trying to do is matching Ticker field in 2 csv files. when match is found lines are merged. If no match found then the minimum info is retained by adding empty commas.
but after 4 empty commas the minimum info (line2) is not getting added.

Code: Select all

Text = 
Line1 =
Line2 =
Line3 =
Text2 =

FileRead,File1,Y:\Data\Ordermodify.txt
FileRead,File2,Y:\Data\Portfolio.txt

Loop,Parse,File2, `n
{
		
	Line1 := A_LoopField
	linearray1 := StrSplit(Line1, ",")
		
	Ticker1 := linearray1.3
	Qty := linearray1.4
	nowPrice := linearray1.5
	EntryPrice := linearray1.7
	
	Loop,Parse,File1, `n
	{
	
	Line2 := A_LoopField
	linearray2 := StrSplit(Line2, ",")
	
	Ticker2 := linearray2.27 
	Qty2 := linearray2.30

	if ( (ticker1  = ticker2) ) 

		{
		Text := Text . ticker2 . "," . Qty2 . "," . Entryprice . "," . nowprice . "," . Line2 . "`n"
		Break	
		}

	}

	if ( (ticker1  != ticker2)  )
		{   
			Text := Text . "," . "," . "," . "," . Line3 . "`n"
			;Break
		}
}

filedelete,Y:\data\matched_OM_PF.txt
fileappend,%Text%,Y:\data\matched_OM_PF.txt

Return

[boiler]

Before anything else, your parsing loops should omit `r because lines in Windows files are usually terminated with CR+LF (`r`n), like this:

Code: Select all

Loop,Parse,File2, `n, `r

Try that first before looking further.

Btw, when posting code, use regular [code][/code] tags so it treats the code as AHK code (with syntax highlighting and links to help file), not as a regular text file.

[prakashG]

Thanks Boiler.

i added `n, `r for the loop parsing. no difference.
when the tickers are not matched inside, the Line2 variable is empty.
I think the Break in previous loop emptying the Line2 var?
I have tried copying line2 as line3 before break, but still not working.

Code: Select all

if ( (ticker1  != ticker2)  )
		{   
			
			msgbox, Line2 is %line2% 
			Text := Text . "," . "," . "," . "," . Line2 . "`n"
			;Break
		}
		
{/code]

[boiler]

In your first script, you don’t ever assign a value to Line3, so it can’t be anything other than blank.

Even when you modify it as you’ve shown it your last post, that whole if ( (ticker1 != ticker2) ) section is outside the inner loop, unlike the other if section which is inside the loop. The way you have indented it doesn’t make that clear. This indentation actually matches how you have things grouped:

Code: Select all

Text = 
Line1 =
Line2 =
Line3 =
Text2 =

FileRead,File1,Y:\Data\Ordermodify.txt
FileRead,File2,Y:\Data\Portfolio.txt

Loop,Parse,File2, `n
{
		
	Line1 := A_LoopField
	linearray1 := StrSplit(Line1, ",")
		
	Ticker1 := linearray1.3
	Qty := linearray1.4
	nowPrice := linearray1.5
	EntryPrice := linearray1.7
	
	Loop,Parse,File1, `n
	{
	
		Line2 := A_LoopField
		linearray2 := StrSplit(Line2, ",")
		
		Ticker2 := linearray2.27 
		Qty2 := linearray2.30

		if ( (ticker1  = ticker2) ) 
		{
			Text := Text . ticker2 . "," . Qty2 . "," . Entryprice . "," . nowprice . "," . Line2 . "`n"
			Break	
		}

	}

	if ( (ticker1  != ticker2)  )
	{   
		Text := Text . "," . "," . "," . "," . Line3 . "`n"
		;Break
	}
}

filedelete,Y:\data\matched_OM_PF.txt
fileappend,%Text%,Y:\data\matched_OM_PF.txt

Return

Did you mean it to be like this? (grouped differently, not just indented differently):

Code: Select all

Text = 
Line1 =
Line2 =
Line3 =
Text2 =

FileRead,File1,Y:\Data\Ordermodify.txt
FileRead,File2,Y:\Data\Portfolio.txt

Loop,Parse,File2, `n
{
		
	Line1 := A_LoopField
	linearray1 := StrSplit(Line1, ",")
		
	Ticker1 := linearray1.3
	Qty := linearray1.4
	nowPrice := linearray1.5
	EntryPrice := linearray1.7
	
	Loop,Parse,File1, `n
	{
	
		Line2 := A_LoopField
		linearray2 := StrSplit(Line2, ",")
		
		Ticker2 := linearray2.27 
		Qty2 := linearray2.30

		if ( (ticker1  = ticker2) ) 
		{
			Text := Text . ticker2 . "," . Qty2 . "," . Entryprice . "," . nowprice . "," . Line2 . "`n"
			Break	
		}

		if ( (ticker1  != ticker2)  )
		{   
			Text := Text . "," . "," . "," . "," . Line3 . "`n"
			;Break
		}
	}
}

filedelete,Y:\data\matched_OM_PF.txt
fileappend,%Text%,Y:\data\matched_OM_PF.txt

Return

By the way, you have an extraneous set of parentheses in if ( (ticker1 = ticker2) ) and if ( (ticker1 != ticker2) ). They can just be if (ticker1 = ticker2) if (ticker1 != ticker2).

[prakashG]

I am sorry Boiler,, I am lazy. Thanks for your help. I am going to cleanup the whole code, add sample csv file and post it here. Let's start from that.

[prakashG]

Portfolio_1.txt
================
09042020,0,AAL,96,13.59,1304.64,13.252,32.44
09042020,2,AAT,49,26.1479,1281.25,25.9561,9.39
09042020,1,AAP,7,153.76,1076.32,155.173,-9.9

Ordermodify_1.txt
=================
4,1,0,None,L2,TB,STF,1,0,0,0,0,0,0,COM,RetailWholesale,10.0624,1,0,0,0,0,0,0,0,3.85624,AAP,9/1/2020,9:42:01 AM,7,Buy,Sell
7,1,3,None,L2,LB,RO8,1,0,0,0,0,0,0,COM,Transportation,6.67,7.34,0,0,0,0,0,0,0,0.92,AAL,8/27/2020,2:56:18 PM,96,Buy,Sell

desired output file
====================

AAL,96,13.59,13.25,7,1,3,None,L2,LB,RO8,1,0,0,0,0,0,0,COM,Transportation,6.67,7.34,0,0,0,0,0,0,0,0.92,AAL,8/27/2020,2:56:18 PM,96,Buy,Sell
AAT,49,26.14,25.95
AAP,7,153.76,155.17,4,1,0,None,L2,TB,STF,1,0,0,0,0,0,0,COM,RetailWholesale,10.0624,1,0,0,0,0,0,0,0,3.85624,AAP,9/1/2020,9:42:01 AM,7,Buy,Sell


desired operation
=================
if symbol matched, copy 4 fileds from portfolio file followed by matched line from Ordermodify line
If not matched, just copy 4 fileds from portfiolio file
Portfolio file will always have more lines.

Code: Select all

#EscapeChar `

FileRead,File1,Y:\Data\Ordermodify_1.txt
FileRead,File2,Y:\Data\Portfolio_1.txt

Text =

Loop,Parse,File2, `n, `r
{
		
Line3 := A_LoopField
linearray1 := StrSplit(Line3 ",")

Ticker1 := linearray1.3
Qty1 := linearray1.4
nowPrice := linearray1.5
EntryPrice := linearray1.7
	
	Loop,Parse,File1, `n, `r
	{
	
	Line4 := A_LoopField
	linearray2 := StrSplit(Line4, ",")
	Ticker2 := linearray2.27 
	Qty2 := linearray2.30
	
	
		if  (ticker2  = ticker1)  

			{
			Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . Line4 . "`n"
			Break	
			}
					
	}
	
if  (ticker2  != ticker1)  
	{   
	Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . "`n"
	Break
	}		
}

filedelete,Y:\data\matched_OM_PF_1.txt
fileappend,%Text%,Y:\data\matched_OM_PF_1.txt

Return

[boiler]

You still have your if (ticker2 != ticker1) block of code outside of the inner loop.

[prakashG]

Still not working.

output file is
============
AAL,96,13.252,13.59,
AAT,49,25.9561,26.1479,
AAP,7,155.173,153.76,4,1,0,None,L2,TB,STF,1,0,0,0,0,0,0,COM,RetailWholesale,10.0624,1,0,0,0,0,0,0,0,3.85624,AAP,9/1/2020,9:42:01 AM,7,Buy,Sell

It did not matched for the 1st line. (or symbol matched but line4 not added.

Code: Select all

#EscapeChar `

FileRead,File1,Y:\Data\Ordermodify_1.txt
FileRead,File2,Y:\Data\Portfolio_1.txt

Text =

line3 =
line4 =


Loop,Parse,File2, `n,`r
{
		
Line3 := A_LoopField
linearray1 := StrSplit(Line3, ",")

Ticker1 := linearray1.3
Qty1 := linearray1.4
nowPrice := linearray1.5
EntryPrice := linearray1.7
;msgbox, loine3 is %line3%
;msgbox, Ticker1 is %ticker1%
	
	Loop,Parse,File1, `n,`r
	{
	
	Line4 := A_LoopField
	linearray2 := StrSplit(Line4, ",")
	Ticker2 := linearray2.27 
	Qty2 := linearray2.30
	;msgbox, Ticker2 is %ticker2%
	
		if  (ticker1  = ticker2)  

			{
			Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . Line4 . "`n"
			Break	
			}
			
		if  (ticker1  != ticker2)  
			{   
			Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . "`n"
			Break
			}	
		
					
	}
	
		
}

filedelete,Y:\data\matched_OM_PF_1.txt
fileappend,%Text%,Y:\data\matched_OM_PF_1.txt

Return

[prakashG]

if (ticker1 = ticker2) : it should break the inner loop, but continue with next line of the outer loop.


IF it reaches end of file in inner loop but match still not found, i.e. "if (ticker1 != ticker2)"
It should copy just the 4 fields and exit from inner loop to continue with next line of outer loop.

[boiler]

If you break each time, you're not giving it a chance for each line in one file to find its match with the each line in the other file. So maybe that's why you had the other one outside the inner loop. But then that's not the way to determine if it didn't find a match. You need to have the one that checks for the match in the inner loop (like you originally had it), and have a variable that starts off as false or 0 get set to true or 1 if it's found. Then you check to see if it's still false or 0 once it's been through the entire inner loop.

[boiler]

Try this:

Code: Select all

#EscapeChar `

FileRead,File1,Ordermodify_1.txt
FileRead,File2,Portfolio_1.txt

Text =

line3 =
line4 =


Loop,Parse,File2, `n,`r
{
		
Line3 := A_LoopField
linearray1 := StrSplit(Line3, ",")

Ticker1 := linearray1.3
Qty1 := linearray1.4
nowPrice := linearray1.5
EntryPrice := linearray1.7
;msgbox, loine3 is %line3%
;msgbox, Ticker1 is %ticker1%

Found := 0	
	Loop,Parse,File1, `n,`r
	{
	
	Line4 := A_LoopField
	linearray2 := StrSplit(Line4, ",")
	Ticker2 := linearray2.27 
	Qty2 := linearray2.30
	;msgbox, Ticker2 is %ticker2%
	
		if  (ticker1  = ticker2)  

			{
			Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . Line4 . "`n"
			Found := 1	
			}					
	}

	if  !Found  
		{   
		Text := Text . ticker1 . "," . Qty1 . "," . Entryprice . "," . nowprice . "," . "`n"
		Break
		}	
}

filedelete,matched_OM_PF_1.txt
fileappend,%Text%,matched_OM_PF_1.txt

Return

Although I'm not sure you want a break in the bottom if block either.

[hd0202]

your main problem is that you must exchange the files in the parse loops !
Hubert

[boiler]

The files are actually associated with the correct loops.

[hd0202]

NO !
Hubert

[boiler]

File1 is Ordermodify_1.txt, which is the one with the large number of columns. File1 is in the inner parsing loop, and it's the one that parses the large number of columns, such as Ticker2 := linearray2.27

[hd0202]

Sorry, I put the data for testing into file1 and file2 and did not look at the filenames
remove the Break from the If !Found
Hubert

[prakashG]

Boiler, yes that found flag worked.
and it should not have the Break in the bottom loop.

The solution worked.
many thanks.