Csv.psm1
|
<#
.SYNOPSIS This function converts a CSV string to a CSV object. .DESCRIPTION This function takes in a CSV representation as a string and returns a custom object with properties that correspond to the CSV. .PARAMETER csvString The string containing the CSV representation. #> function ConvertTo-CsvObject { [CmdletBinding()] [OutputType([Object[]])] param ( # The string containing the CSV representation. [Parameter(Mandatory=$true)] [String]$csvString, # If this parameter is set, strings consisting solely of digits with an optional '-' in front # which are shorter than the maximum length will be converted to integer variables. [Parameter(Mandatory=$false)] [ValidateNotNull()] [ValidateScript({ $_ -gt 0 -and $_ -le 6})] [Int]$convertIntegersMaxLength, # Select if strings "true" and "false" are processed into boolean $true and $false [Parameter(Mandatory=$false)] [Switch]$convertBooleans = [Switch]::Present, # Select if strings should be trimmed of starting and trailing whitespace. [Parameter(Mandatory=$false)] [Switch]$trimStrings ) # Remove extra first line for Excel if present if ($csvString -like "sep=,*") { $csvString = [Regex]::new("sep=,[\s]+([\s\S]*)").Match($csvString).Groups[1].Value } # Output CSV string to temporary file # Try to create a temporary file try { $tempFile = New-TemporaryFile } catch { Write-Error "Failed to create temporary file for converting CSV string to CSV object. $($_.Exception.Message)" return $null } # Try to set the content of the temporary file try { $csvString | Set-Content -NoNewline -Path $tempFile.FullName } catch { Write-Error "Failed to set content of temporary file for converting CSV string to CSV object. $($_.Exception.Message)" return $null } # Try to import CSV file to object try { $csvObject = Import-Csv -Path $tempFile.FullName } catch { Write-Error "Failed to import temporary file into CSV object. $($_.Exception.Message)" return $null } # Convert single objects to array $csvObject = ConvertTo-Array $csvObject # Process the CSV if ($convertIntegersMaxLength -or $convertBooleans -or $trimStrings) { foreach ($csv in $csvObject) { foreach ($propertyName in $csv.PSObject.Properties.Name) { if ($null -eq $csv.$propertyName) { continue } # Check for string if ($csv.$propertyName.GetType() -eq [String]) { # Check if string should be converted to an integer if ($convertIntegersMaxLength -and $csv.$propertyName.length -le $convertIntegersMaxLength -and [Regex]::new("^-?[0-9]{1,$($convertIntegersMaxLength)}$").IsMatch($csv.$propertyName)) { $csv.$propertyName = [Int]$csv.$propertyName } # Check if string should be converted to boolean elseif ($convertBooleans -and $csv.$propertyName.ToLower().Trim() -in @("true", "false")) { $csv.$propertyName = $csv.$propertyName.Trim() -eq "true" } # Check if string should be trimmed elseif ($trimStrings) { $csv.$propertyName = $csv.$propertyName.Trim() } } } } } return $csvObject } <# .SYNOPSIS This function converts a CSV object to a CSV string. .DESCRIPTION This function takes in a custom object and returns the CSV representation of that object as a single string. .PARAMETER csvObject The object containing properties to convert to CSV. #> function ConvertTo-CsvString { [CmdletBinding()] [OutputType([String])] param ( # The object containing properties to convert to CSV [Parameter(Mandatory = $true)] [AllowEmptyCollection()] [ValidateNotNull()] [PSObject[]]$csvObject ) # Output CSV object to temporary file # Try to create a temporary file try { $tempFile = New-TemporaryFile } catch { Write-Error "Failed to create temporary file for converting CSV object to CSV string. $($_.Exception.Message)" return $null } # Try to export CSV object to temporary file try { $csvObject | Export-Csv -Path $tempFile.FullName -NoTypeInformation } catch { Write-Error "Failed to export CSV object into temporary file. $($_.Exception.Message)" return $null } # Try to get content of temporary file to CSV string try { $csvString = Get-Content $tempFile.FullName -Raw } catch { Write-Error "Failed to get content of temporary file to CSV string. $($_.Exception.Message)" return $null } # Add extra line so that Excel opens properly $csvString = "sep=,`r`n" + $csvString return $csvString } <# .SYNOPSIS This function filters a CSV string, keeping rows which have $true as the value for the specified columns. #> function Get-FilteredCsvString { [CmdletBinding()] [OutputType([Object[]])] param ( # The CSV string to filter. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [String]$csv, # The columns used to filter the CSV string. [Parameter(Mandatory=$false)] [AllowNull()] [AllowEmptyString()] [String]$columns, # Select if the filtering is 'and' or 'or'. [Parameter(Mandatory=$false)] [ValidateSet("and", "or")] [String]$filtering = "and" ) # Convert the CSV string to CSV object $csvObject = ConvertTo-CsvObject $csv if (!$csvObject) { Write-Error "Failed to convert input CSV string to CSV object." return $null } # Filter the CSV by the column names if ($columns) { $columnNames = ConvertTo-Array ($columns.Split(",") | ForEach-Object { $_.Trim() }) # Run through each entry in the CSV $csvObject = $csvObject | ForEach-Object -Process { $csvEntry = $_ # Perform a count of the number of properties which are true $filterResult = ConvertTo-Array ($columnNames | ForEach-Object -Process { if ($csvEntry.$_ -eq $true) { $true } }) # All the properties are true, the entry stays if ($filtering -eq "and" -and $filterResult.length -eq $columnNames.length) { $csvEntry } # At least one property is true, the entry stays elseif ($filtering -eq "or" -and $filterResult.length -gt 0) { $csvEntry } } } # No items left after filtering if (!$csvObject) { return $null } # Return the result as a CSV string $result = ConvertTo-CsvString $csvObject if (!$result) { Write-Error "Failed to convert CSV string to CSV object." return $null } return $result } <# .SYNOPSIS This function returns the result of combining CSVs. .DESCRIPTION This function returns the result of combining CSVs. The resulting CSV contains all the columns which were present in any of original CSVs. The combined CSV will contain the columns in sorted order. #> function Get-CombinedCsvString { [CmdletBinding()] [OutputType([String])] param ( [Parameter(Mandatory=$true)] [ValidateNotNull()] [String[]]$csvStrings ) # Remove the sep=, from the beginning if present $csvStrings = $csvStrings | ForEach-Object -Process { if ($_ -like "sep=,*") { [Regex]::new("sep=,[\s]+([\s\S]*)").Match($_).Groups[1].Value } else { $_ } } # Collate all the column names $columnNames = @{} foreach ($csvString in $csvStrings) { $columns = $csvString.Split("`r`n")[0].Split(",") | ForEach-Object { if ([Regex]::new("^`"(.)*`"$").IsMatch($_)) { [Regex]::new("^`"(.*)`"$").Match($_).Groups[1].Value } else { $_ } } foreach ($column in $columns) { $columnNames[$column] = $true } } $columnNames = $columnNames.Keys | Sort-Object # Convert each of the CSV strings to CSV objects $csvObjects = @() foreach ($csvString in $csvStrings) { $csvObject = ConvertTo-CsvObject -csvString $csvString if (!$csvObject) { Write-Error "Not all CSV strings to combine could be converted to CSV objects." return $null } # Add in additional columns if they don't exist for each row foreach ($csvRow in $csvObject) { foreach ($columnName in $columnNames) { if ($columnName -notIn $csvRow.PSObject.Properties.Name) { $csvRow | Add-Member -NotePropertyName $columnName -NotePropertyValue $null -Force } } } $csvObjects += $csvObject } # Combine the CSV objects $combinedCsvObject = @() foreach ($csvObject in $csvObjects) { foreach ($csvRow in $csvObject) { $combinedCsvObject += $csvRow } } # Convert the combined CSV into a CSV string $combinedCsvString = ConvertTo-CsvString -csvObject $combinedCsvObject if ([String]::IsNullOrWhiteSpace($combinedCsvString)) { Write-Error "Failed to convert combined CSV object into CSV string." return $null } # Return the combined CSV string return $combinedCsvString } |