…import two CSV files and put their data into tables for comparison -@cxmelga
…if I can get started on an example that would be great -@cxmelga
Remarks
This demonstration features functions, classes, a datatable object with a primary key, and the use of the Find()
method. The goal is to use functional logic in the original post and provide examples of different programming techniques while maintaining short execution times.
Performance
Good performance is difficult to judge when there are no standards with which to compare. Other approaches may be more suitable for the circumstances of a program and its operational environment. For this reason, I chose to demonstrate multiple action and item types rather than following one course of action.
Notes
- Set A has 485268 rows; it was more efficient not to store this set as a
[datatable]
on my personal laptop.
- 25 rows are expected to be found in A that have two elements that match rows in B.
- These results are stored in C.
- B is treated as a dimensional object.
- B is not a subset of A, and the elements in B’s primary key must be non-null and unique in the set.
- (Special) Elements in all objects are alphabetically ordered.
- (Special) CSV files do not have a header row.
Demo Code
using namespace System.Collections;
using namespace System.Data;
$LoadSetA = $true;
if($LoadSetA)
{
$A = Import-Csv A:\resultsA.csv -Header CarrierTrackingNumber,ModifiedDate,Name,ProductID,ProductNumber,SalesOrderDetailID,SalesOrderID,SetNbr,UnitPrice;
}
Class Bi
{
[string] $Color;
[string] $DaysToManufacture;
[float] $ListPrice;
[string] $Name;
[int] $ProductID;
[string] $ProductNumber;
[int] $ReorderPoint;
[datetime] $SellEndDate;
[datetime] $SellStartDate;
[string] $Style;
Bi($q1,$q2,$q3,$q4,$q5,$q6,$q7,$q8,$q9,$q10)
{
$this.Color = $q1;
$this.DaysToManufacture = $q2;
$this.ListPrice = $q3;
$this.Name = $q4;
$this.ProductID = $q5;
$this.ProductNumber = $q6;
$this.ReorderPoint = $q7;
$this.SellEndDate = $this.SetDateTime($q8);
$this.SellStartDate = $q9;
$this.Style = $q10;
}
[datetime]SetDateTime($d1)
{
[datetime]$result = switch ($d1)
{
{$_ -eq 'NULL'}{'1900-01-01'; break}
{[string]::IsNullOrEmpty($_)}{'1900-01-01'; break}
default {$d1}
}
return $result;
}
}
$B = [Datatable]::new('Product','Production')
[void]$B.Columns.Add('Color',[string]);
[void]$B.Columns.Add('DaysToManufacture',[string]);
[void]$B.Columns.Add('ListPrice',[decimal]);
[void]$B.Columns.Add('Name',[string]);
[void]$B.Columns.Add('ProductID',[int]);
[void]$B.Columns.Add('ProductNumber',[string]);
[void]$B.Columns.Add('ReorderPoint',[int]);
[void]$B.Columns.Add('SellEndDate',[datetime]);
[void]$B.Columns.Add('SellStartDate',[datetime]);
[void]$B.Columns.Add('Style',[string]);
$B.PrimaryKey = $B.Columns['ProductID','SellStartDate'];
function AddItemB([Bi]$item)
{
$row = $B.NewRow();
foreach($i in $item)
{
$row.Color = $i.Color;
$row.DaysToManufacture = $i.DaysToManufacture;
$row.ListPrice = $i.ListPrice;
$row.Name = $i.Name;
$row.ProductID = $i.ProductID;
$row.ProductNumber = $i.ProductNumber;
$row.ReorderPoint = $i.ReorderPoint;
$row.SellEndDate = $i.SellEndDate;
$row.SellStartDate = $i.SellStartDate;
$row.Style = $i.Style;
}
$B.Rows.Add($row);
}
foreach($i in (Import-Csv A:\resultsB.csv -Header Color,DaysToManufacture,ListPrice,Name,ProductID,ProductNumber,ReorderPoint,SellEndDate,SellStartDate,Style))
{
$Bii = [Bi]::new($i.Color,$i.DaysToManufacture,$i.ListPrice,$i.Name,$i.ProductID,$i.ProductNumber,$i.ReorderPoint,$i.SellEndDate,$i.SellStartDate,$i.Style);
AddItemB($Bii);
}
$C = [ArrayList]::new();
foreach($r in $A)
{
$key = $r.ProductID,$r.ModifiedDate;
if($B.Rows.Find($key))
{
[void]$C.Add($r);
}
}
return $A.Count, $B.Rows.Count, $C.Count;
Results
Measure of Total Operation
Measure Operation with preloaded A ($LoadSetA = $false
)
Measure Load A
Measure Find and Load C