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 { 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 = $csvString.Remove(0, $csvString.IndexOf('"')) } # 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 [PSCustomObject]@{} } # 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 [PSCustomObject]@{} } # 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 [PSCustomObject]@{} } # 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) { # 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 { param ( # The object containing properties to convert to CSV [Parameter(Mandatory = $true)] [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 "" } # 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 "" } # 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 "" } # 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 { 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 "" } # 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 "" } # Return the result as a CSV string $result = ConvertTo-CsvString $csvObject if (!$result) { Write-Error "Failed to convert CSV string to CSV object." return "" } return $result } |