Like to use PowerShell if possible.
Looking to generate a report that I can save to a CSV that displays POS register CPUs by store and by lane.
Backend is a SQL database I'm am querying with
select STORE,REG,CPU from MY_REGISTERS;
I am free to change the query to anything necessary.
I'm truncating the format due to limited space here but this is the general form.
A register lane may be empty and I would like a blank string to hold the place.
STORE REG001 REG002 REG003 REG004 REG005 REG006 REG007 REG008 REG009 REG010
0007 i5 i7 i7 i3 <blnk> <blnk> i6 <blnk> <blnk> <blnk>
0018 i6 <blnk> i3 i6 i5 i6 <blnk> <blnk> <blnk> <blnk>
0256 i7 i7 i5 i7 i4 <blnk> <blnk> <blnk> <blnk> <blnk>
1234 i5 i7 i7 i3 i7 i7 i5 i7 i4
I have started with multiple approaches but nothing seems to pan out. I'm having trouble envisioning the path to a useful solution.
Have tried multiple nested for loops, looking for changing values and pulling them out.
I've tried array and hash manipulation.
I've tried group-by functions.
My general approaches have all been around this basic idea below but I've not been able to realize it.
Get-Member info on my $Returned object from the DB Query.
TypeName: System.Data.DataRow
Name MemberType Definition
---- ---------- ----------
AcceptChanges Method void AcceptChanges()
BeginEdit Method void BeginEdit()
.
.
.
ParameterizedProperty System.Object Item(int columnIndex) {get;set;}, System.Object Item(string columnName) {get;set;}, System.Object Item(System.Data.D...
CPU Property string CPU {get;set;}
REG Property int REG {get;set;}
STORE Property int STORE {get;set;}
Some sample data:
PS E:\Repos\process-cmdb> $Returned
STORE REG CPU
----- --- ---
7 1 3500 MHz Core i5-7600
7 2 3500 MHz Core i5-7600
7 3 3500 MHz Core i5-7600
7 4 3500 MHz Core i5-7600
7 5 3500 MHz Core i5-7600
7 6 3500 MHz Core i5-7600
7 7 3500 MHz Core i5-7600
8 1 3500 MHz Core i5-7600
8 2 3500 MHz Core i5-7600
8 3 2500 MHz Celeron
8 4 3500 MHz Core i5-7600
8 5 3500 MHz Core i5-7600
8 6 2500 MHz Celeron
12 1 2500 MHz Celeron
12 2 2900 MHz Pentium
12 3 2500 MHz Celeron
12 4 2500 MHz Celeron
12 5 2500 MHz Celeron
12 6 2500 MHz Celeron
23 1 3500 MHz Core i5-7600
23 2 3500 MHz Core i5-7600
23 3 3500 MHz Core i5-7600
23 4 3500 MHz Core i5-7600
23 5 3500 MHz Core i5-7600
23 6 3500 MHz Core i5-7600
25 1 3500 MHz Core i5-7600
25 2 3500 MHz Core i5-7600
25 3 2500 MHz Celeron
25 4 3500 MHz Core i5-7600
25 5 3500 MHz Core i5-7600
25 6 2500 MHz Celeron
33 1 3500 MHz Core i5-7600
33 2 3500 MHz Core i5-7600
33 3 2500 MHz Celeron
33 4 3500 MHz Core i5-7600
33 5 3500 MHz Core i5-7600
33 6 2500 MHz Celeron
I'm looking to better understand how to break this problem down and how to translate into a PowerShell script.
Thanks in advance!
Maybe something like this is what you had in mind. First I group the data by 'Store', then loop through each of the groups and build a hashtable with the reg values as keys and the CPU values as the values. Finally I output these hashtables as pscustomobjects which when exported to csv should be in your desired format.
$ConvertedData = $Return |
Group-Object -Property Store |
Sort-Object {[int]$_.Name} |
ForEach-Object {
# Create hashtable to build object
$out = [ordered]@{}
# Create STORE property
$out.add('STORE', $_.Name.PadLeft(4, '0'))
# Build each of the REG properties from 1 to 10
foreach ($reg in 1..10) {
# Look for a matching reg entry and set it to CPU value
if ($matchReg = $_.group | Where-Object {$_.Reg -eq $reg}) {
$out.add("REG$($reg.ToString().PadLeft(3,'0') )", $matchReg.Cpu)
}
else {
$out.add("REG$($reg.ToString().PadLeft(3,'0'))", '<blank>')
}
}
[pscustomobject]$out
}
# Output to screen
$ConvertedData | Format-Table
# Export to csv
$ConvertedData | Export-Csv -Path .\output.csv # Export to csv
OUTPUT
STORE REG001 REG002 REG003 REG004 REG005 REG006 REG007 REG008 REG009 REG010
----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
0007 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 <blank> <blank> <blank>
0008 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron <blank> <blank> <blank> <blank>
0012 2500 MHz Celeron 2900 MHz Pentium 2500 MHz Celeron 2500 MHz Celeron 2500 MHz Celeron 2500 MHz Celeron <blank> <blank> <blank> <blank>
0023 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 3500 MHz Core i5-7600 <blank> <blank> <blank> <blank>
0025 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron <blank> <blank> <blank> <blank>
0033 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron 3500 MHz Core i5-7600 3500 MHz Core i5-7600 2500 MHz Celeron <blank> <blank> <blank> <blank>