Is there a clean way to append SEP=, to the start of a CSV file created by Export-CSV?
NB: This is an XY Problem; see Why's this needed below for info on my root issue. I'm asking for the SEP piece since this seems like the best fix given I need to continue using UTF8 CSVs and don't want users to have to change how they work to avoid issues.
Amend File After Creation
The simple option is this; however it feels messy (i.e. we release the lock on the file then have to go back and update it).
function Repair-Csv {
[CmdletBinding()]
Param (
[Parameter(Mandatory = $true, ValueFromPipeline = $true)]
[string]$Path
)
Begin {
$sep = "SEP=,`r`n"
}
Process {
$sep + (Get-Content -Path $Path -Raw) | Set-Content -Path $Path
}
}
Proxy Function Solution Attempt
I tried creating a proxy function (code below), but found that the wrapped Export-CSV command doesn't append its output to my tweaked file, but rather reads in the content I've already written and attempts to use SEP= as a column heading; so I end up with no data (unless one of my exported object's properties happens to be called SEP=.
# $MetaData = New-Object System.Management.Automation.CommandMetaData (Get-Command 'Export-CSV')
# [System.Management.Automation.ProxyCommand]::Create($MetaData)
function Export-CsvAdvanced {
[CmdletBinding(DefaultParameterSetName='Delimiter', SupportsShouldProcess=$true, ConfirmImpact='Medium', HelpUri='http://go.microsoft.com/fwlink/?LinkID=113299')]
param (
[Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[psobject]
${InputObject}
,
[Parameter(Position=0)]
[ValidateNotNullOrEmpty()]
[string]
${Path}
,
[Alias('PSPath')]
[ValidateNotNullOrEmpty()]
[string]
${LiteralPath}
,
[switch]
${Force}
,
[Alias('NoOverwrite')]
[switch]
${NoClobber}
,
[ValidateSet('Unicode','UTF7','UTF8','ASCII','UTF32','BigEndianUnicode','Default','OEM')]
[string]
${Encoding}
,
[switch]
${Append}
,
[Parameter(ParameterSetName='Delimiter', Position=1)]
[ValidateNotNull()]
[char]
${Delimiter}
,
[Parameter(ParameterSetName='UseCulture')]
[switch]
${UseCulture}
,
[Alias('NTI')]
[switch]
${NoTypeInformation}
,
[Alias('SEP')]
[switch]
${PrefixWithSep}
)
begin {
try {
$outBuffer = $null
if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer))
{
$PSBoundParameters['OutBuffer'] = 1
}
if ($PrefixWithSep.IsPresent) {
if (!$Delimiter) {
if ($UseCulture.IsPresent) {
$Delimiter = [System.Globalization.CultureInfo]::CurrentCulture.TextInfo.ListSeparator
} else {
$Delimiter = ','
}
}
$splat = @{}
if ($Encoding) { $splat.Add('Encoding', $Encoding) }
if ($Force) { $splat.Add('Force', $Force) }
if ($Append) { $splat.Add('Append', $Append) }
if ($NoClobber) { $splat.Add('NoClobber', $NoClobber) }
if ($PSCmdlet.ParameterSetName -eq 'PSPath') {
$splat.Add('LiteralPath', $LiteralPath)
} else {
$splat.Add('FilePath', $Path)
}
("SEP={0}`r`n" -f $Delimiter) | Out-File @splat
$PSBoundParameters.Remove('PrefixWithSep') #Export-CSV won't understand our custom parameter, so remove it (NB: if this is not the first call this will already have been removed)
$PSBoundParameters['Append'] = $true #we don't want to delete the file after adding a header
$PSBoundParameters['Force'] = $true #Don't treat `SEP=,` as a heading when appending to the file
}
$wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Microsoft.PowerShell.Utility\Export-Csv', [System.Management.Automation.CommandTypes]::Cmdlet)
$scriptCmd = {& $wrappedCmd @PSBoundParameters }
$steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin)
$steppablePipeline.Begin($PSCmdlet)
} catch {
throw
}
}
process {
try {
$steppablePipeline.Process($_)
} catch {
throw
}
}
end {
try {
$steppablePipeline.End()
} catch {
throw
}
}
<#
.ForwardHelpTargetName Microsoft.PowerShell.Utility\Export-Csv
.ForwardHelpCategory Cmdlet
#>
}
#test populated
Get-Process | Export-CsvAdvanced -Path 'c:\temp\processSep.csv' -NoTypeInformation -PrefixWithSep
Get-Process | Export-CsvAdvanced -Path 'c:\temp\process.csv' -NoTypeInformation
#test blank
$blankArray = @()
$blankArray | Export-CsvAdvanced -Path 'c:\temp\emptySep.csv' -NoTypeInformation -PrefixWithSep
$blankArray | Export-CsvAdvanced -Path 'c:\temp\empty.csv' -NoTypeInformation
I could always create a proxy function over ConvertTo-CSV adding streams to output the results to file; but that doesn't sit comfortably (i.e. if there's an exception it gets complicated trying to correctly dispose of the streams).
Why's this needed?
I don't strictly need the SEP prefix; it would be useful; but the truth is I'm trying to solve a different problem (so this is an XY problem).
I have some functions which output data to CSV.
In some cases, the exported files are blank (i.e. as there were no results). In such cases I still want a file to be created (i.e. so it's clear the code has run and there were no results, vs the code simply not running).
However, my CSVs are UTF8, which when blank show as a BOM in Excel (i.e. cell A1 contains ).
Since I have to continue using CSVs and ideally wish to continue using UTF8 it seems the best solution is to add some content; one suggestion is to prefix the SEP=, instruction (which is also useful when sharing the CSV with users in other countries, where a different default delimiter is used).
