question

AndrsSolano-2085 avatar image
0 Votes"
AndrsSolano-2085 asked SureshSekar-9621 answered

Avoid PowerShell creating line break in long strings

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.



windows-server-powershell
file.txt (650 B)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered

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"
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrsSolano-2085 avatar image
0 Votes"
AndrsSolano-2085 answered MotoX80 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The only difference between your original line of code and MotoX80's is that he missed the ">" after the "4" that redirects the Verbose stream to the file.

0 Votes 0 ·

I think it's the number 4 that is causing that. I was not familiar with the verbose stream. I was thinking that SQL had multiple levels for the verbose switch.

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_redirection?view=powershell-7.1

Do either of these work for you?

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

or

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





0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrsSolano-2085 avatar image
0 Votes"
AndrsSolano-2085 answered

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SureshSekar-9621 avatar image
0 Votes"
SureshSekar-9621 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.