Avoid PowerShell creating line break in long strings

Question

Hello Community,

I´m pretty new working with PowerShell and I have some doubts about some behavior related with PowerShell. Basically, I´m trying to create a .json file with the output of a select statement, for this I created a process that generates the .json file in SQL Server and then it prints it and lot it into a file using PowerShell

Invoke-Sqlcmd -inputfile $script -QueryTimeout 30 -ServerInstance $instance -Database $database -Verbose 4> "$file"

This process create a .json with the structure that I desired, but I saw some problems related with some strings that has a huge length were in this cases PowerShell added a line break when the string is too long. For example:

75572-file.txt

This file shows in the column4 where the string is getting cut like

"COLUMN4":"AAAAAAAAAAAAAAAAAAAAAA,.......,OOOOOOOO,PPPPPPPPPPP,QQQQQQQQQQQQQQQ
QQQQQQQQ,RRRRRRRRRR,.......,BBBBBBBBBBBBB
BBB,CCCCCCCC,DDDDDDDDDD,FFFFFFFFFF",

Where the lines with Q and B are having a line break.

Is there a way to avoid PowerShell adding this extra line while the file creation process?

Or is any other way I can remove this line break without removing all the break lines?

I will appreciate any help provided.

Answer 1

Instead of redirecting stdout (which might get console settings involved) , try it this way.

Invoke-Sqlcmd -inputfile $script -QueryTimeout 30 -ServerInstance $instance -Database $database -Verbose 4  | out-file "$file"

Answer 2

Hello,

I tried the code above, but I'm getting the following error message:

Invoke-Sqlcmd : The 'Query' and the 'InputFile' options are mutually exclusive.

Thanks in advance.

Answer 3

What sort of data are returned from the Invoke-SQL cmdlet? If you assigned the output from the Invoke-SQL cmdlet to a variable, is that variable an array of PSCustomObjects? If it is you could just pipe the results of the Invoke-SQL command to a ConvertTo-JSON cmdlet.

The Out-File usually assumes the width of the PowerShell console. You can add the "-Width" parameter, but if the length of the output exceeds that length the output is truncated. Why not pipe the results to a Set-Content cmdlet instead?

Answer 4

I just fixed the file with the -NoNewLine command, this made only one string of the json file then I replace some characters as needed to create the line breaks in order to provide the necessary format.

#Replace double quotes and comma with double quotes, comma and a line break
(Get-Content $file) -replace '",', """,`r`n" | Set-Content $file -Force

#Replace brakets with line breaks and a braket
(Get-Content $file) -replace "{","`r`n{`r`n" | Set-Content $file -Force

#Replace brakets with line breaks and a braket
(Get-Content $file) -replace "}","`r`n}`r`n" | Set-Content $file -Force

Answer 5

You should use Out-File to write the results to the file and use -Width parameter to specify the number of characters in a line upto which line break should not occur. The maximum number you can specify for the width is 2147483647.

Try the below command and it should work as you expect.

Invoke-Sqlcmd -inputfile $script -QueryTimeout 30 -ServerInstance $instance -Database $database -Verbose 4>&1 | Out-File $file -Width 2147483647