issues with PowerShell query

Question

I am using the Invoke-Sqlcmd command in PowerShell to execute a query so I can force the correct encoding (UTF8) as well as the removal of the headers and other characters from the results. This approach is giving me 99% of what I need, only issue I'm having is the extra insertion of a space after each column. The code below is what I've thrown together to force the removal of CR, LF and commas.

How can I keep everything past the Select-Object? If I remove the ConvertTo-Csv which caontains the -Delimiter I'm assuming is responsible for the extra space if fails, if I set the -Delimiter to no space I get "Cannot bind parameter 'Delimiter'. Cannot convert value "" to type "System.Char". Error: "String must be exactly one character long."

If I'm not trying to export to CSV how can I remove since it doesn't seem to be needed?

| ConvertTo-Csv -NoTypeInformation -Delimiter " " | Select-Object -Skip 1 | % {$_ -replace '"', ""} | % {$_ -replace '\n'," "} | % {$_ -replace '\r'," "}|Out-File ("\\WINSVR2019-DELTA\Ggp\customer.txt") -Force -Encoding ascii

How can I keep everything past the Select-Object? If I remove the ConvertTo-Csv which caontains the -Delimiter I'm assuming is responsible for the extra space if fails, if I set the -Delimiter to no space I get:

Cannot bind parameter 'Delimiter'. Cannot convert value "" to type "System.Char". Error: "String must be exactly one character long.

Below are the query results. I have to force each field to a certain character value, which is why the delimiter is causing an issue. The Pipe is a visual indicator where the delimiter is that I'm trying to remove. Again, this is exactly what I need, minus the delimiter.

005688 |Brothers Electric Supply |PO Box 8699 |Riverside |UT |35807 |US|20000 |06152022| 14904.00|0.00000 |0 |REI

Answer 1

I'm unclear on the exact requirements, and I suspect that an OO solution based on the DataRow objects that Invoke-Sqlcmd outputs may be preferable, but to address your question as asked:

• Indeed, ConvertTo-Csv and Export-Csv require that the -Delimiter argument be a single, literal character.

• A workaround if you want no delimiter is to choose a temporary delimiter (separator) that you know not be part of the data itself, and to remove all instances of it later.

Applied to your code, using "`t", i.e. a tab character as the temporary delimiter:

... | 
  ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | 
  Select-Object -Skip 1 | 
  % { $_ -replace "`t`"", '' -replace '[\r\n]', " " } |
  Out-File -Force -Encoding ascii \\WINSVR2019-DELTA\Ggp\customer.txt

Note:

• As in your own attempt, the streamlined -replace operation replaces a CR (\r) and a LF (\n) each with a space. If your intent is to instead replace each CRLF sequence with a space, use '\r\n'. (In general, if you want to match both Windows-format CRLF sequences and Unix-format LF-only newlines, use '\r?\n')