question

AndrsSolano-2085 avatar image
0 Votes"
AndrsSolano-2085 asked ·

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)
10 |1000 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"
·
10 |1000 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 ·

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 ·
10 |1000 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?

·
10 |1000 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




·
10 |1000 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.