I have a script that I have been working on that takes two files (both files are each 50k+ lines). The object of this script is create a new combined file. There is a key account number that match in both files. The issue is that when I run the script, it will run for 5 hours+ and I need to cut the time down. I have an idea that would make it faster, but it using a technique that I am unsure that is possible or even how to do it.
The first array is setup:
#**********************************************
#*** BORROW.TXT File Setup ***
#**********************************************
#This sets up the array of objects for the Nelnet BORROW.TXT file
echo "Processing file: $FileSource\BORROW.TXT"
ForEach ($Line in $BorrowFile)
{
$BrrwArray += New-Object psobject -Property @{
BRBSS = $($Line.substring(0,9));
BRBNMF = $($Line.Substring(9,14));
BRBNML = $($Line.Substring(23,25));
BRBNMM = $($Line.Substring(48,1));
BRBADS = $($Line.Substring(49,1));
BRBPA1 = $($Line.Substring(56,30));
BRBPA2 = $($Line.Substring(86,30));
BRBRPC = $($Line.Substring(116,18));
BRBPZC = $($Line.Substring(134,15));
BRQSTA = $($Line.Substring(153,2));
BRBPAC = $($Line.Substring(162,5));
BRBPPN = $($Line.Substring(167,11));
BRBDOB = $($Line.Substring(355,7));
}
}
The second array is setup in a similar manner:
#********************************************
#*** LOAN.TXT File Setup ***
#********************************************
#This sets up the array of objects for the Nelnet LOAN.TXT file
echo "Processing file: $FileSource\LOAN.TXT"
ForEach ($Line in $LoanFile)
{
$LNArray += New-Object psobject -Property @{
LNBSS = $($Line.substring(0,9));
LNLLN = $($Line.substring(9,2));
LNLLTY = $($Line.substring(11,4));
LNLSTS = $($Line.substring(15,3));
LNGGR = $($Line.substring(93,1));
LNQSSC = $($Line.substring(103,6));
LNLIRT = $($($Line.substring(120,5)).trimstart('0'));
LNLGSD = $($Line.substring(126,7));
LNL1DD = $($Line.substring(133,7));
LNLAMD = $($Line.substring(140,9));
LNLCUP = $($Line.substring(183,9));
LNBOND = $($Line.substring(295,3));
LNLN02 = $($Line.substring(548,3));
LNOQST = $($Line.substring(656,1))
}
}
The key field that matches in each array is BRBSS and LNBSS. In the first array each line is unique with no repeats, however, in the second array there can be repeats of the field LNBSS based on accounts each member has opened (IE: car loan, revolving loans, student loans, etc...).
The merging loop looks like this:
echo "Merging Files to: $OutFile"
ForEach ($BrwItem in $BrrwArray)
{
ForEach ($LNItem in $LNArray)
{
If ($($LNItem.LNBSS) -eq $($BrwItem.BRBSS))
{
#merge data
}
}
}
The idea is to create a smaller array that only contains the data from the array LNItem where LNBSS matches $BrwItem.BRBSS.
What is the best way to accomplish this in the fast most effective way so I am not basically looping 50k+ * 50k+times?