SOLVED Awk or gawk and use of an array or how do I add content from one file to end of relevant lines in another file ?

[going-bald]

Using awk or gawk, for each line where
$1 in a line of file 1
matches
substr($5,1,5) in a line of file 2
I want to add $2 of file 1 to the end of the relevant line in file 2.

I am stuck, if you will, help please.

Explanation of superflous code,
1) there are multiple versions of "summary_*.csv". The file names include a date and time stamp of when the files were created.
I want to scan only the most recently created file and I thought the
"if (file_number == 2)"
would restrict the script to the most recent file but it didn't.
For reasons unknown the script found an older file first so I have added the full date and time stamp to name of "summary_*.csv" in the std in.
Question, if it is possible, how would you suggest I restrict the script to scanning the most recent version of summary_*.csv .... so that I do not have have to update this script when a new version of summary_* is produced?

2) "if (FNR < 7) {print $0}" .prints column titles.

3) the " print number[$1] > ("LG_test.csv")" is a test to see whether or not the array number[$1] element contains the contents of $2, it appears that it does at that point in the script.

Currently the output file "summary_location_test.csv" contains only the column titles.

I repaced
if ( ( number[substr($5,1,5)] !=0 ) && ( number[substr($5,1,5)] !="") ) {print $0 ", test ," substr($5,1,5) "," number[substr($5,1,5)] }
with
if (subtr($5,1,5) in number) { print $0 ", test ," substr($5,1,5) "," number[substr($5,1,5)] }
but it didn't work.

Code: Select all

gawk -F[,"\t"] '{
	         if (NR == 1)			{file_number =1    } 
		 if (file_number == 1) 	{number[$1] = $2 
		 					 print number[$1] > ("LG_test.csv")	 
		 }
		 if ( (FNR ==1) && (NR >=2) ) 	{file_number++  }
		 if ((file_number == 2)) {
		         if (FNR <7) {print $0 }
		    	 if (FNR >=7) {
				if ( ( number[substr($5,1,5)] !=0 ) && ( number[substr($5,1,5)] !="") ) {
				       			print $0 ", test ," substr($5,1,5) "," number[substr($5,1,5)]
				}
			 }
		 }
	}' URL_list_temp.txt        summary_2024-03-01_[09-10-11].csv > summary_location_test.csv
	

[mikeflan]

I don't know how to do that with awk.

$1 in a line of file 1
matches
substr($5,1,5) in a line of file 2

This would be very easy to do with hashes in Perl (or any other favorite programming language). Do you know any programming language? I suspect that is a stupid question

[going-bald]

Unfortunately my lack of knowledge of awk / gawk is the sum total of my 'knowledge'.

[going-bald]

Hmmm, after some googling I have just run the following script on sample, just-typed files and it appears to work perfectly

Code: Select all

gawk -F[,"\t"] '{
	         if (NR == FNR)  {Number[$1] = $2 ; next}
# next line is/was a test just to see what was seen
		 if (NR != FNR)  {print substr($5,1,5)}  
	     	 if (substr($5,1,5) in Number ) {num = substr($5,1,5) ; print $0 ", test, " num " , " Number[num]}
	    	}' location_file1.2 target_file_file2.2 > output_file2

Yet when run on the intended files it doesn't work, I will have to check the second file to see whether or not there is something about $5 in the second file that I am not seeing when looking at if in a spreadsheet progam and which 'breaks' the script.

[going-bald]

Gaaaarrrgggh, accursed white space in files 1 & 2 .... Grrrr.
My faults for making things look pretty in a spreadsheet.

Thanks for looking Mike, it's appreciated.