0

I'm working on a batch script. I have a SQL file where I'd like to replace some strings. I achieve this using the PowerShell 5.1 -replace operator like this:

powershell -Command "(Get-Content 'myfile.sql') -replace 'abc', 'xyz' | Set-Content 'myfile.sql'"

I noticed my edited file has a new empty line at its end. It's annonying in my case because I also use the copy command to merge this file to another to create a new script. This empty line creates error when I try to execute it in SSMS.

I tried different things to prevent the last empty line creation like Out-File instead of Set-Content but nothing moves. I also tried to use .NET command:

powershell -Command "[system.io.file]::WriteAllText('myfile.sql',((Get-Content 'myfile.sql') -replace 'abc', 'xyz' ))"

But this one transforms the file in a one line, which is even worse because the SQL script includes comments. So my file go from this :

Line 1 -- comment 1
Line 2 -- comment 2

to this:

Line 1 -- comment 1 Line 2 -- comment 2

making only the first line of the script executable. And of course, deleting comments isn't an option.

So, what's your opinion? Is there a specific option to add to PowerShell command to prevent the last empty line creation (-NoNewLine isn't correct in my case)? Should I maybe change the PowerShell command to another batch command? Using C program isn't an option too!

Edit 1 : According to the comment, I refered to this post which could bring me answers.

I tried the -NoNewLine option :

powershell -Command "(Get-Content 'myfile.sql') -replace 'abc', 'xyz' | Set-Content -NoNewLine -Force 'myfile.sql'"

Which renders this :

Line 1 -- comment 1 Line 2 -- comment 2

Then not ok here. I tried to add the -join operator. I had to replace double quotes by single quote to make it fit my Powershell -Command :

powershell -Command "(Get-Content 'myfile.sql') -replace 'abc', 'xyz' -join '`n' | Set-Content -NoNewLine -Force 'myfile.sql'"

The result now includes a 'n' string :

Line 1 -- comment 1 `n` Line 2 -- comment 2

Then I tried to complete the -join with the r option : 

powershell -Command "(Get-Content 'myfile.sql') -replace 'abc', 'xyz' -join '`r`n' | Set-Content -NoNewLine -Force 'myfile.sql'"

Which gives : 

Line 1 -- comment 1 `r`n`r` Line 2 -- comment 2

Nothing's better for now. The author also use the -Raw operator in the Get-Content, maybe I should do the same but I failed at positionning it in my command :

powershell -Command "(Get-Content 'myfile.sql' -Ram(?)) -Raw(?) -replace 'abc', 'xyz' -Raw(?) | Set-Content 'myfile.sql'"

Both of these locations triggered errors. I also tried to embed all of the command into parenthesis and add -Raw without success. Using .NET wasn't successfull either. So I feel stuck right here. Maybe I should try to build the command another way but as a novice, I have no idea of what to do. I must be missing something.

Squashman
  • 13,649
  • 5
  • 27
  • 36
Arhyaa
  • 369
  • 1
  • 3
  • 21
  • 1
    `Set-Content` creates a new line by default. For, PSv5+ and other versions see [this](https://stackoverflow.com/questions/45266461/set-content-appends-a-newline-line-break-crlf-at-the-end-of-my-file) answer by [Mklement](https://stackoverflow.com/users/45375/mklement0). – Vivek Kumar Singh Aug 22 '18 at 08:25
  • Thanks for your answer. I edited my post. I already found the post you mentionned but I wasn't able to find what was right for my case. – Arhyaa Aug 22 '18 at 09:37
  • 1
    You **must** use both `Get-Content -Raw` **and** `Set-Content -NoNewLine` to achieve your desired result. `Get-Content` without the parameter `-Raw` produces an array of strings (one string per line). Passing that to `Set-Content -NoNewLine` would remove *all* newlines from the input (`Set-Content` by default adds a newline to each input string, thus reconstructing what was removed when reading the file; `-NoNewLine` prevents that *for all input strings*). Adding the parameter `-Raw` reads the file as a single string, so not appending a newline to the output produces the desired output. – Ansgar Wiechers Aug 22 '18 at 11:37
  • seems to work for me with just -join and -nonewline no need for -raw so far as I can tell.... Maybe I'm missing something... – WayneA Aug 22 '18 at 12:05
  • Seems to finally work with `powershell -Command "(Get-Content -Path 'myfile.sql' -Raw) -replace 'abc','xyz' | Set-Content -NoNewLine 'myfile.sql'". Thanks for the help. – Arhyaa Aug 22 '18 at 12:20
  • 1
    @WayneA Yes, `-join` would work too, but why fix things when you can avoid breaking them in the first place? For `-join` to work the delimiter string must be in double quotes, though, otherwise the CR and LF escape sequences wouldn't be expanded. – Ansgar Wiechers Aug 22 '18 at 12:23

0 Answers0