none
Edit and replace in text Files with power shell

    Question

  •  I have used PS to replace some old UNIX based processes. Woot!
     But I can’t seem to do this one with PS.
    Text in FILE:

    GO
    END
    GO

     

     Wanted result:

    END
    GO

     

    Script I tried:

     

    (Get-Content C:\Test1.txt) | 
    Foreach-Object {$_ -replace "GO
    END
    GO", "END 
    GO"} | 
    Set-Content C:\ Test2.txt 
     How can I get rid of the first GO. As the script is it does not replace any thing. 
     I am guessing that is because the words are loaded into an array but I need to process two lines as one item how can this be done?
     I also tried ‘n but this does work with array either.
     
    Please help?
    Tuesday, February 02, 2010 4:49 PM

Answers

  • Not tested:

    $file = gc c:\test\file1.txt
    $file2 = @()

    $file |% {

    switch ($_){
    "GO" {$last_GO = $true ; continue}
    "END" {$file2 += $_;$last_GO = $false ; continue}
    default {if ($last_GO){$file2 += "GO";$last_GO = $false};$file2 += $_}
    }

    }

    $file2 | out-file c:\test\file2.txt

    • Edited by mjolinorModerator Wednesday, February 17, 2010 12:47 AM typo
    • Marked as answer by Lucentor Wednesday, February 17, 2010 4:47 PM
    Wednesday, February 17, 2010 12:41 AM

All replies

  • Actually you've got the right idea but the "Search Replace" is wrong.

    the Foreach is pulling in each individual line of text and examining it for the phrase

    GO
    END
    GO

    But since each line only has ONE of those words (not the whole chunk including paragraph returns) it'll never match

    (That's the logic being used)  But what you actually wantt (if I'm reading it right) is to search the content as a WHOLE and replace those matches.

    Does that sound correct? :)

    Sean
    The Energized Tech
    Computers are cool. I just can't hold myself back.
    Tuesday, February 02, 2010 6:09 PM
  • Ok if it's the latter I played with it.  This was fun :)

    The text file reads as an array.  There might be another way, but I cheated and recombined it into a GIANT string.

    Let's mark it as a script to see the flow

    $FILE=Get-Content C:\Test1.txt
    Foreach-Object { $CombinedString=$CombinedString+$_+"`n" }
    $CombinedString -replace "GO`nEND`nGO","`nEND`nGO" | Set-Content C:\Test2.txt

    There is also a "Replace" method which could be attached to the Actual original content like this

    $FILE.Replace

    But I'm not 100% on the Syntax.  THAT would be way quicker and would be exactly like the Unix solution.  Only WAY faster and more efficient :)


    Powershell. It's so Easy and it's FREE! Dive in and use it now, It'll take no time. :)
    Tuesday, February 02, 2010 6:53 PM
  • Try this:

    $FILE=Get-Content C:\Test1.txt
    [REGEX]::replace($FILE,"END\sGO","GO").split() | Set-Content C:\Test2.txt
    Wednesday, February 03, 2010 11:26 AM
  • yes that is right. i wil try the methods provided tommorrow... thnks for the feed back

    Tuesday, February 16, 2010 5:25 AM
  • I will vote on the  on helpfulness as well after testing.  thanks:-)
    Tuesday, February 16, 2010 5:29 AM
  • Proplem,

     Transferrring a DB FROM SQL 2000 to SQL2005.

     The stored procedures do not contain Begin END  NO Rowcount etc...

    Thus in editing a sig file as the output of all SPs in DB  I end up Needing to edit across  Many CRLFs.

     IE:

     GO
    END
    GO

    Tuesday, February 16, 2010 6:36 PM
  • I tried this script and the output was not as expected.

    took the file and output 3  CRLF and that is all.

     see output below.

    "

     


    "

    Tuesday, February 16, 2010 7:30 PM
  • Is that leading whitespace in front of the first GO a typo, or something that actually expected in the data?
    Tuesday, February 16, 2010 8:26 PM
  • There is an error in the script I posted.  the regex replace should be ($FILE,"END\sGO","END")

    I can't reproduce the results you say you're getting, but it does produce a blank line with that leading whitespace in front of the first "GO".  I can fix that if that's what the actual data may look like.



    PS C:\test> $file = gc c:\test\test1.txt
    PS C:\test> $file
     GO
    END
    GO
    PS C:\test> [REGEX]::replace($FILE,"END\sGO","END").split() | Set-Content C:\test\Test2.txt
    PS C:\test> gc c:\test\test2.txt

    GO
    END
    PS C:\test>

    Tuesday, February 16, 2010 8:33 PM
  • Unfortunately I tested this on a very short file and just looked at the output as a string.

    When done this splits the wholes statemet up. This file still needs to be readable by SSMS as a query to run. so this will not work for me. I also tried this with out the .split() and one big long string over a thousand lines is too unwiledy to edit mannually. I really am looking for solution that does not jumble the word wrap and all... So in short cheats will not work either. Does any one have a more elgant example that does not severely cripple the formatting. BTW Thank to everyone who replied this has given me some insight on how to proceed.
    Tuesday, February 16, 2010 9:50 PM
  • It would help if I could have at least a short example of the actual data that's involved.  I wrote the script to the data that was submitted.

    Tuesday, February 16, 2010 10:16 PM
  • Not tested:

    $file = gc c:\test\file1.txt
    $file2 = @()

    $file |% {

    switch ($_){
    "GO" {$last_GO = $true ; continue}
    "END" {$file2 += $_;$last_GO = $false ; continue}
    default {if ($last_GO){$file2 += "GO";$last_GO = $false};$file2 += $_}
    }

    }

    $file2 | out-file c:\test\file2.txt

    • Edited by mjolinorModerator Wednesday, February 17, 2010 12:47 AM typo
    • Marked as answer by Lucentor Wednesday, February 17, 2010 4:47 PM
    Wednesday, February 17, 2010 12:41 AM
  •  typo..

     i am using script stored procedures as create and then  etc.

    Wednesday, February 17, 2010 1:06 AM
  • I will retest the devil i not in the detatils but in the syntax and white space..... ;-)
    Wednesday, February 17, 2010 1:06 AM
  • What i am doing is converting sql 2000 stored procedures into sql 200 with auto script feature  so what I am really doing is in inserting:


     Begin
     Set no rowcount;




     END

     as a result of earlier trial.

    I ended up with 



    GO
    END
    GO

     in every place where the Proc statement ended.

     had lots of fun as well trying to code around the AS statement to add begin set No Rowcount;
    Wednesday, February 17, 2010 1:11 AM
  • I will try this tommorrow. Can't wait 'til I'm to the point where I can assist others. :-(
    Wednesday, February 17, 2010 1:12 AM
  • other wise proc will report error for some in SQL 2005
    Wednesday, February 17, 2010 1:13 AM
  • I wil provide better file output sample tommorrow as well. If you like me this is exhausting at times but really fun. I love power shell.
    Wednesday, February 17, 2010 1:15 AM
  • Well, there's your problem.  You need to get a more agreeable database :).

    Wednesday, February 17, 2010 1:18 AM
  • Also,

     I'm not sure if it Ok to post comments as I have but any feed back on improving this process is welcome.


     The comment on providing actual content  sample and exactness has been acknowledged.
    Wednesday, February 17, 2010 1:19 AM
  • If the last code I posted works, we're good, but for future reference, a sample of the actual data is always better than abbreviated test sets.  Abbreviated horizontally is usuallly worse than vertically.
    Wednesday, February 17, 2010 1:24 AM
  • The text below is what I ended up with after my first pass at editing. I wanted to delete the bold GOs.



    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHealth] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO
    CREATE proc [dbo].[spu_DeleteHealth]

    @DistrictID int

    ,

    @HealthID int

    AS

    BEGIN

    SET

    NOCOUNT ON;

    Delete

    tblDistrictIMhealth

    Where

    districtID = @DistrictID AND healthID = @HealthID

    GO
    END

    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHistory] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO
    CREATE
    proc [dbo].[spu_DeleteHistory]

    @DistrictID int

    ,

    @HistoryID int

    As

    BEGIN

    SET

    NOCOUNT ON;

    Delete

    tblDistrictIMhistory

    Where

    districtID = @DistrictID AND historyID = @HistoryID

    GO
    END
    GO

    Wednesday, February 17, 2010 4:37 PM
  • Can you post what it looked like before you ran that pass?

    Wednesday, February 17, 2010 4:41 PM
  • Output:

    GO

    /******
    Object:
    StoredProcedure
    [dbo].[spu_DeleteHealth]
    Script
    Date:
    02/17/2010
    08:30:42
    ******/

    SET

    ANSI_NULLS
    OFF

    GO

    SET

    QUOTED_IDENTIFIER
    OFF

    GO
    CREATE
    proc
    [dbo].[spu_DeleteHealth]

    @DistrictID
    int

    ,

    @HealthID
    int


    AS

    BEGIN

    SET


    NOCOUNT
    ON;

    Delete

    tblDistrictIMhealth

    Where

    districtID
    =
    @DistrictID
    AND
    healthID
    =
    @HealthID

    GO
    END

    GO

    /******
    Object:
    StoredProcedure
    [dbo].[spu_DeleteHistory]
    Script
    Date:
    02/17/2010
    08:30:42
    ******/

    SET

    ANSI_NULLS
    OFF

    GO

    SET

    QUOTED_IDENTIFIER
    OFF

    GO
    CREATE
    proc
    [dbo].[spu_DeleteHistory]

    @DistrictID
    int

    ,

    @HistoryID
    int


    As

    BEGIN

    SET


    NOCOUNT
    ON;

    Delete

    tblDistrictIMhistory

    Where

    districtID
    =
    @DistrictID
    AND
    historyID
    =
    @HistoryID

    GO
    GO

    Wednesday, February 17, 2010 4:42 PM
  • Out put





     pasted as blank or crlf

    Wednesday, February 17, 2010 4:43 PM
  • Which script are you running that through? 

    That looks like what the regex would produce after doing the .split().

    Have you tried the last script I posted (the one with the switch statement)?

    Wednesday, February 17, 2010 4:46 PM
  • GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHealth] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO
    CREATE proc [dbo].[spu_DeleteHealth]

    @DistrictID int

    ,

    @HealthID int

    AS

    BEGIN

    SET


    NOCOUNT ON;

    Delete

    tblDistrictIMhealth

    Where

    districtID = @DistrictID AND healthID = @HealthID

    END

    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHistory] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO
    CREATE proc [dbo].[spu_DeleteHistory]

    @DistrictID int

    ,

    @HistoryID int

    As

    BEGIN

    SET


    NOCOUNT ON;

    Delete

    tblDistrictIMhistory

    Where

    districtID = @DistrictID AND historyID = @HistoryID

    END

    Wednesday, February 17, 2010 4:47 PM
  • this script seems to do the job well: Please provide  a walk thru if you dont mind so imay get all the concepts at use here.

    Your explanation of it would help greatly.

    Thank you.

    Wednesday, February 17, 2010 4:48 PM
  • There is not a white space. Iwould like to test your example though agains the sample with out the white space.
    Wednesday, February 17, 2010 4:50 PM
  •  gc =Get-Content
    Wednesday, February 17, 2010 4:52 PM
  • I can see so far that you are creating a tempfile.

     Doing some line compare and resetting value if next line = end and pushing some how to end of file line by line the pushing countents of temp file into permananent trying to decipher. Am i close?
    Wednesday, February 17, 2010 5:04 PM
  • $file = gc c:\test\file1.txt  #get the input file

    $file2 = @()   #initialize an arrray to hold the output (otherwise the += operations on the strings will just concatenate) 

    $file |% {   # fore-each through each line of the file

    switch ($_){                                        #initialize a switch, using each line of the file as the comparison base
    "GO" {$last_GO = $true ; continue}      # if the current line is "GO", set $last_GO to $true, and continue*


    "END" {$file2 += $_;$last_GO = $false ; continue}  #if the current line is END, and $last_GO is $true, add the current line to $file2, and set $last_GO back to $false, and continue*

    default {if ($last_GO){$file2 += "GO";$last_GO = $false};$file2 += $_} If the current line is not GO or END, and $last_GO is $true, then the last line was "GO", and wasn't followed by END, so we want to keep it.  Add "GO" to $file2, set $last_GO back to $false, and then add the current line to $file2
    }

    }

    $file2 | out-file c:\test\file2.txt  # save the lines we added to file2 as a new file.

    * continue means don't do any more comparisons in the switch, just go back and start over with the next record in the pipeline.


    So basically,

    If the current line is GO, set a switch and go read the next record.

    If the current line is END, and that switch is on, add "END" to the output file, and turn off the switch (do not add GO, do not collect $200)

    If the current line is something else, and the switch is on, add "GO" to the output file, then add the current line, and turn the switch back off.

    Lather, rinse , repeat.

    When it runs out of input, spit out the collected output.

    Wednesday, February 17, 2010 5:08 PM
  • USE

    [Williams]

    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHealth] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO

    CREATE

    proc [dbo].[spu_DeleteHealth]

    @DistrictID int

    ,

    @HealthID int

    As

    BEGIN

    SET

    NOCOUNT ON;

    Delete

    tblDistrictIMhealth

    Where

    districtID = @DistrictID AND healthID = @HealthID

    END

    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHistory] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO

    CREATE

    proc [dbo].[spu_DeleteHistory]

    @DistrictID int

    ,

    @HistoryID int

    As

    BEGIN

    SET

    NOCOUNT ON;

    Delete

    tblDistrictIMhistory

    Where

    districtID = @DistrictID AND historyID = @HistoryID

    END

    Wednesday, February 17, 2010 5:08 PM
  • GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHealth] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO

    CREATE

    proc [dbo].[spu_DeleteHealth]

    @DistrictID int

    ,

    @HealthID int

    As

     

    Delete tblDistrictIMhealth

    Where

    districtID = @DistrictID AND healthID = @HealthID

    END

    GO

    /****** Object: StoredProcedure [dbo].[spu_DeleteHistory] Script Date: 02/17/2010 08:30:42 ******/

    SET

    ANSI_NULLS OFF

    GO

    SET

    QUOTED_IDENTIFIER OFF

    GO

    CREATE

    proc [dbo].[spu_DeleteHistory]

    @DistrictID int

    ,

    @HistoryID int

    As

     

    Delete tblDistrictIMhistory

    Where

    districtID = @DistrictID AND historyID = @HistoryID

     

    Wednesday, February 17, 2010 5:10 PM
  • The output of that  switch is below 

     |
     |
     V
    Wednesday, February 17, 2010 5:11 PM
  • Hi. I am having a similar issue: I tried to modify the example above but seem to be missing badly.
    I am trying to find the two END statements together and insert a SELECT @_PID

    My code:
    $file = gc ./testsmall.sql
    $file2 = @()

    $file |% {

    switch ($_){
    "END" {$last_END = $true ; continue}
    "SELECT @P_ID" {$file2 += $_;$last_END = $false ; contiune}
    default {if ($last_END){$file2 += "END";$last_END = $false};$file2 += $_}
    }
    }

    $file2 | set-content ./testsmall.sql


    Here is an example of what I am try to search and replace:
       BEGIN

          IF @P_ID IS NULL OR @P_ID = '00000000000000000000000000000000'
             BEGIN

                 SET @P_ID = newid()

                INSERT matms.ASSET_ATTRIBUTE_TYPES(
                   ID,
                   ASSET_TYPE_ID,
                   NAME,
                   DESCRIPTION,
                   DATA_TYPE,
                   FORMAT,
                   CREATED_BY,
                   CREATED_DATE,
                   SITE_ID)
                   VALUES (
                      @P_ID,
                      @P_ASSET_TYPE_ID,
                      @P_NAME,
                      @P_DESCRIPTION,
                      @P_DATA_TYPE,
                      @P_FORMAT,
                      @P_CREATED_BY,
                      sysdatetime(),
                      @P_SITE_ID)

             END
          ELSE
             BEGIN
                UPDATE matms.ASSET_ATTRIBUTE_TYPES
                   SET
                      ASSET_TYPE_ID = @P_ASSET_TYPE_ID,
                      NAME = @P_NAME,
                      DESCRIPTION = @P_DESCRIPTION,
                      DATA_TYPE = @P_DATA_TYPE,
                      FORMAT = @P_FORMAT,
                      UPDATED_BY = @P_CREATED_BY,
                      UPDATED_DATE = sysdatetime(),
                      SITE_ID = @P_SITE_ID
                WHERE ASSET_ATTRIBUTE_TYPES.ID = @P_ID
             END
             SELECT @P_ID <--- ***** I need to added this
       END
    GO
    Monday, March 01, 2010 4:53 PM
  • Not tested, the conditons were a little simpler so I switched to using if/else instead of the switch statement.
    The input was a more complicated because of the indents, so I changed to a regex match, and captured the leading whitespace to mainting the formatting on the insert.

    $file = gc ./testsmall.sql
    $file2 = @()

    $file |% {

    if ($_ -match "^(\s*)END\s*$" ){
       if ($last_END){$file2 += ($matches[1] + ' SELECT @_PID');$file2 += $_;$last_END = $false}
       else {$last_END = $true}
       }

    else {$file2 += $_;$last_END = $false}

    }

    $file2 | set-content ./testsmall.sql

     






    Monday, March 01, 2010 5:51 PM
  • Thanks for the reply. I get no love though. It just removes the END statements. That what is sooo frustrating. I keep going round and round and I get close but just can't get it to do what I need. I was trying to use a simple replace up until I saw this post and tried this out. It just would replace every END statement with END SELECT @P_ID END.

    I'll keep plugging alone until I explode (getting close) or I get it figured out.

    $l = @"
    END

    END
    "@

    $m = @"
    END
    SELECT @P_ID
    END
    "@

    (get-content ./testsmall.sql) | %{$_ -replace "$l", "$m" } | set-content ./testsmall.sql
    Monday, March 01, 2010 8:00 PM
  • Okay, I had an error in the script.  This is a corrected version, but looking at the test data in your last post, it appears the the original data has a blank line between the two END statements, rather than two consecutive END statements, and that would have caused the original script not to work.  I added an additional test to account for consecutive END statements separated by blank lines. 

    $file = gc ./testsmall.sql
    $file2 = @()

    $file |% {
    if ($__){
    if ($_ -match "^(\s*)END\s*$" ){
       if ($last_END){$file2 += ($matches[1] + ' SELECT @_PID');$file2 += $_;$last_END = $false}
       else {$file2 += $_;$last_END = $true}
       }

    else {$file2 += $_;$last_END = $false}

    }
    }
    $file2 | set-content ./testsmall.sql
    Monday, March 01, 2010 8:21 PM
  • (get-content ./testsmall.sql) | %{$_ -replace "$l", "$m" } | set-content ./testsmall.sql

    That cannot work, because when you read in a file and then use foreach to send it down the pipeline (|%) it sends it down one line at a time.  There's no way the replace can work, because you're trying to match 3 lines in $l against 1 line in $_.
    Monday, March 01, 2010 8:25 PM
  • I guess that's why is replaces all the END statements. I am working with you last post try to change things around and see what I can get. Still no luck but I am more stubborn that the code. :@)
    Monday, March 01, 2010 8:28 PM
  • Does your actual data have 2 consecutive END statements, or is there one or more blank lines between them where you want to insert the string?

    Monday, March 01, 2010 8:33 PM
  • There are blank lines. The same I provided above is exactly that what it looks like. I pulled it directly from the file I am working against.
    Monday, March 01, 2010 9:39 PM
  • Does the last scrpt I posted not work?
    Monday, March 01, 2010 9:41 PM
  • Oops. I missed you posted it. I just tried it and it did not do anything. No change in the file at all.
    Monday, March 01, 2010 10:38 PM
  • I should've stayed with the switch.

    $file = gc ./testsmall.sql
    $file2 = @()

    $file |% {

    switch -regex ($_){

    "^\s*$" {continue}

    "^(\s*)END\s*$" {
    if ($last_END){
     $file2 += ($matches[1] + ' SELECT @_PID')
     $file2 += $_
     $last_END = $false
     continue
     }
    else {
     $file2 += $_
     $last_END = $true
     continue
     }
    }
    default {$file2 += $_;$last_END = $false}

    }
    }
    $file2 | set-content ./testsmall.sql

    Monday, March 01, 2010 11:51 PM
  • I tried this one. Still just removing the END statements.
    Tuesday, March 02, 2010 2:03 PM
  • I'm using this for input (testsmall.sql)

    BEGIN

          IF @P_ID IS NULL OR @P_ID = '00000000000000000000000000000000'
             BEGIN

                 SET @P_ID = newid()

                INSERT matms.ASSET_ATTRIBUTE_TYPES(
                   ID,
                   ASSET_TYPE_ID,
                   NAME,
                   DESCRIPTION,
                   DATA_TYPE,
                   FORMAT,
                   CREATED_BY,
                   CREATED_DATE,
                   SITE_ID)
                   VALUES (
                      @P_ID,
                      @P_ASSET_TYPE_ID,
                      @P_NAME,
                      @P_DESCRIPTION,
                      @P_DATA_TYPE,
                      @P_FORMAT,
                      @P_CREATED_BY,
                      sysdatetime(),
                      @P_SITE_ID)

             END
          ELSE
             BEGIN
                UPDATE matms.ASSET_ATTRIBUTE_TYPES
                   SET
                      ASSET_TYPE_ID = @P_ASSET_TYPE_ID,
                      NAME = @P_NAME,
                      DESCRIPTION = @P_DESCRIPTION,
                      DATA_TYPE = @P_DATA_TYPE,
                      FORMAT = @P_FORMAT,
                      UPDATED_BY = @P_CREATED_BY,
                      UPDATED_DATE = sysdatetime(),
                      SITE_ID = @P_SITE_ID
                WHERE ASSET_ATTRIBUTE_TYPES.ID = @P_ID
             END
           
       END
    GO



    And I get this for output:



    BEGIN
          IF @P_ID IS NULL OR @P_ID = '00000000000000000000000000000000'
             BEGIN
                 SET @P_ID = newid()
                INSERT matms.ASSET_ATTRIBUTE_TYPES(
                   ID,
                   ASSET_TYPE_ID,
                   NAME,
                   DESCRIPTION,
                   DATA_TYPE,
                   FORMAT,
                   CREATED_BY,
                   CREATED_DATE,
                   SITE_ID)
                   VALUES (
                      @P_ID,
                      @P_ASSET_TYPE_ID,
                      @P_NAME,
                      @P_DESCRIPTION,
                      @P_DATA_TYPE,
                      @P_FORMAT,
                      @P_CREATED_BY,
                      sysdatetime(),
                      @P_SITE_ID)
             END
          ELSE
             BEGIN
                UPDATE matms.ASSET_ATTRIBUTE_TYPES
                   SET
                      ASSET_TYPE_ID = @P_ASSET_TYPE_ID,
                      NAME = @P_NAME,
                      DESCRIPTION = @P_DESCRIPTION,
                      DATA_TYPE = @P_DATA_TYPE,
                      FORMAT = @P_FORMAT,
                      UPDATED_BY = @P_CREATED_BY,
                      UPDATED_DATE = sysdatetime(),
                      SITE_ID = @P_SITE_ID
                WHERE ASSET_ATTRIBUTE_TYPES.ID = @P_ID
             END
        SELECT @_PID
       END
    GO


    I don't know what else I can do.

    Tuesday, March 02, 2010 2:49 PM
  • Humm. The tool I am using is SSMS to view the file and check the results. What are you using?
    I was just about to try and convert the file to just plain text but I don't think that will make a difference. Seems it may be something I am doing/not doing if you are getting the above results.
    Tuesday, March 02, 2010 3:20 PM
  • Well,seems SSMS was the issue. I used plain old notepad to test it this time and all worked as you noted. Wondering how far back things were working now and scratching my head. Any knowledge on SSMS and caching files? Maybe that had something to do with it? Or the history stored in SSMS? Not sure. I'll look into that on the side.

    Also wondering where your knowledge and background comes from? Are you more a developer (.Net or other) or more a DBA?

    Thank you very, very much for all your help! I really appreciate it.
    Tuesday, March 02, 2010 3:34 PM
  • Actually, I'm an Exchange admin, but I've been messing with Powershell from back when it was Monad B2.

    No experience with SSMS at all.

    I just copied and pasted the text you posted into notepad, and renamed it to the .sql file.  That was my test data.
    Tuesday, March 02, 2010 3:42 PM
  • I posted my issue on my blog last week and via twitter. I just updated that post (http://sqlaj.wordpress.com/2010/02/26/powershell-using-regex/)
    with the solution you provided and gave you the credit.
    Thanks
    Tuesday, March 02, 2010 5:09 PM
  • Glad it worked! 
    Tuesday, March 02, 2010 5:37 PM
  • Another way would be to use the Replace operator with a couple of Regex to insert the text in the blank line between the two END statements and then – if necessary – remove the other blank lines with another replace operation.
    Note: Setting the new file's encoding is optional.
    Try this on SQLAJ's sample text:

    $file1 = '<full path to input file>'
    $file2 = '<path to new file>'
    $pat1 = '(?<=END\r\n\s+)\r\n(?=\s+END\r\n)'
    $pat2 = '\r\n\s*\r\n'
    $rep1 = "SELECT @_PID`r`n"
    $rep2 = "`r`n"
    [IO.File]::ReadAllText($file1) -creplace $pat1, $rep1 -replace $pat2, $rep2 |
     Set-Content $file2 -Encoding UTF8

    Just adding to the fun :)


    Robert

    Tuesday, March 09, 2010 3:59 PM
  • This will strip the first line out of any arbitrary text file (test.txt).

     

    (Get-Content test.txt)[1..((Get-Content test.txt).length)] | Set-Content test.txt

     

    Let me break that down so you can see what's happening. In each line I'm talking about the part in bold.

    (Get-Content test.txt) - just takes text.txt and breaks it into a collection of strings (one line per item)

    (Get-Content test.txt)[1..((Get-Content test.txt).length)] - gets the number of items in the collection. In this case the number of lines in the text file.

    (Get-Content test.txt)[1..((Get-Content test.txt).length)] - this returns in index of the collection. It means return the 2nd (collections are zero based) item through the last item.

    (Get-Content test.txt)[1..((Get-Content test.txt).length)] | Set-Content test.txt - create a new text file (using the 2nd through the last line)

    I know this was already answered but I thought this was a more generic way to do it in one line.

    Thanks,

    Jonathan Walz

    • Proposed as answer by JonWalzMVP Tuesday, January 10, 2012 6:26 PM
    Tuesday, January 10, 2012 6:25 PM