How do I use PowerShell to create UTF8 file with NO BOM (Byte Order Marks)

Question

I have a SQL Server SQL Agent job that is using PowerShell and the following script to create a csv file, however the script is putting hidden Byte Order Marks (BOMs) in my output. How do I remove the BOMs?

$query = "select * from [server].[database].[view]"

Invoke-Sqlcmd -QueryTimeout 0 -Query $query  | Export-Csv -NoTypeInformation -Encoding UTF8 -Path "[FilePath\[FileName].csv" 

I tried changing the code to:

Invoke-Sqlcmd -QueryTimeout 0 -Query $query  | Export-Csv -NoTypeInformation -Encoding UTF8NoBom -Path "[FilePath\[FileName].csv" 

but received the following error:

...The error information returned by PowerShell is: 'Cannot validate argument on parameter 'Encoding'. The argument "UTF8NoBOM" does not belong to the set "Unicode.UTF7.UTF8.ASCII.UTF32.BigEndianUnicode.DefaultOEM" specified by the ValidateSet attribute...

Answer 1

The Stack Overflow question pointed to by Xedni has enough info to solve the problem, but in the context of CSV, it becomes a little convoluted if you want to use Lucero's answer.

Instead of using Export-Csv -NoTypeInformation, use ConvertTo-Csv -NoTypeInformation to first save the file's text to a variable, then use Lucero's answer to write to the file.

$FileText = Invoke-Sqlcmd -QueryTimeout 0 -Query $query | ConvertTo-Csv -NoTypeInformation
Set-Content ([Text.UTF8Encoding]::new().GetBytes($FileText)) -Encoding Byte -PSPath "[FilePath\[FileName].csv"