-
Notifications
You must be signed in to change notification settings - Fork 0
/
ColumnMapper.psm1
68 lines (61 loc) · 2.79 KB
/
ColumnMapper.psm1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
function Invoke-ColumnMapper {
<#
.SYNOPSIS
Consolidate duplicate row values and create new columns for each unique value found in a corresponding column.
.DESCRIPTION
ColumnMapper finds all unique values in the column specified by the KeysHeader parameter and converts them to hash keys. When it finds a new key,
it asks for any values found in the column specified by the ValuesHeader parameter and adds them to a new array. It continues to add any new values
found in the ValuesHeader column to each corresponding array. Finally, we split the array into a comma-separated string so that it can be imported
easily into your favorite spreadsheet application.
.PARAMETER InputPath
The absolute or relative path to the input CSV file.
.PARAMETER OutputPath
Specifies the path to write the output CSV file to. Defaults to ColumnMap_YYYYMMDD.csv
.PARAMETER Open
Opens the output CSV file. Defaults to false.
.PARAMETER KeysHeader
Specifies the column name to search in for the row identifiers. ColumnMapper will use these values as the primary row identifiers. Currently defaults to '[PO] Order Id' for legacy purposes.
.PARAMETER ValuesHeader
Specifies the column name to search for unique values mapped to the row identifiers. ColumnMapper will take any value found in the first column and create new columns for each unique value found in this one. Currently defaults to '[PO]GL Account (GL Account Id)' for legacy purposes. #>
param(
[Parameter(Mandatory=$true)]
$InputPath,
[string]$KeysHeader = '[PO] Order Id',
[string]$OutputPath = "ColumnMap_$(Get-Date -UFormat "%Y%m%d").csv",
[string]$ValuesHeader = '[PO]GL Account (GL Account Id)',
[switch]$NoExport = $false,
[switch]$Open = $false
)
$dataHash = @{}
$content = Import-Csv -Path $InputPath
$content | ForEach-Object {
$key = $_.$KeysHeader
$value = $_.$ValuesHeader
# TODO: Create column headers for
# each unique column we create
if ($dataHash.ContainsKey($key)) {
$dataHash[$key] += $value
}
else {
$dataHash[$key] = @($value)
}
$ary = $dataHash[$key] | Select-Object -Unique
$dataHash[$key] = @($ary)
}
if ($NoExport -eq $true) {
return $dataHash
}
$dataHash.GetEnumerator() |
Select-Object -Property @{ N = $KeysHeader; E = { $_.Key } },@{ N = $ValuesHeader; E = { $_.Value } } |
Export-Csv -NoTypeInformation $OutputPath
$outputContent = Get-Content $OutputPath
$outputContent -replace "(\d)\s(\d)",'$1","$2' | Set-Content $OutputPath
if ($Open -eq $true) {
Start "" $(Resolve-Path $OutputPath)
}
else {
Write-Output "Output file is here: $(Resolve-Path $OutputPath)"
}
}
Set-Alias -Name cmap -Value Invoke-ColumnMapper
Export-ModuleMember -Function * -Alias *