Solved

remove blocks text containing several lines in a text file

Posted on 2016-08-01
12
16 Views
Last Modified: 2016-08-01
Hello,

In a text file, I search to remove blocks text containing several lines in a text file   like

create trigger [MSmerge%

    . . .
    . . . 

    return     

GO

Open in new window


Thanks

Regards
0
Comment
Question by:bibi92
  • 6
  • 5
12 Comments
 
LVL 83

Expert Comment

by:oBdA
ID: 41737055
That depends strongly on the input file.
In general, you need to read the file as a whole (Get-Content will by default return an array of strings).
Then you need to tell the RegEx to use Single-line mode; you do this by using inline options, namely "(?s)".
Line breaks can now be searched like any other character (using "\r\n").
You now need to be very careful with quantifiers, as they are greedy by default, so they will swallow as much as possible; use a "?" after the quantifier to make it lazy.
That said, try
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)create trigger \[MSmerge%.*?\r\nGO', ''

Open in new window

And I've asked this before:
Please, add "Style Code" tags (from the tool bar above the Edit field) around any source you post; don't just put it in as regular text. This prevents unintended changes to the source, and makes copying and pasting it easier.
0
 

Author Comment

by:bibi92
ID: 41737195
Thanks but it doesn't work.
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41737208
"it doesn't work" is usually not enough to help identifying what went wrong.
I saved this as C:\Temp\test.sql:
Foo
create trigger [MSmerge%

Make me disappear

    return     

GO
Bar

Open in new window

Then used this:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$sql -replace '(?s)create trigger \[MSmerge%.*?\r\nGO', ''

Open in new window

And, as expected, had this as output:
Foo

Bar

Open in new window


If "it doesn't work" for you, you need to provide:
1. A sample input SQL file that can be used as is, without changes - enclosed in "Style Code" tags.
2. A description detailing exactly which line(s) from the code box need to be removed under which conditions.
0
 

Author Comment

by:bibi92
ID: 41737212
Hello

Following un example but the text file can contain trigger for update :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [MSmerge_del_599C8427ACEF4B32984FAA8ED57D7B88] on [dbo].[T_repl] FOR DELETE   AS 
    declare @is_mergeagent bit, @at_publisher bit, @retcode smallint 

    set rowcount 0
    set transaction isolation level read committed

            select @is_mergeagent = convert(bit, sessionproperty('replication_agent'))
            select @at_publisher = 0 
    if (select trigger_nestlevel()) = 1 and @is_mergeagent = 1
        return 
    declare @article_rows_deleted int
    select @article_rows_deleted = count(*) from deleted
    if @article_rows_deleted=0
        return
    declare @tablenick int, @replnick binary(6), 
            @lineage varbinary(311), @newgen bigint, @oldmaxversion int, @child_newgen bigint, 
            @child_oldmaxversion int, @child_metadatarows_updated int, @cv varbinary(1),
            @logical_record_parent_oldmaxversion int, @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311), @logical_record_parent_gencur bigint,
            @num_parent_rows int, @logical_record_parent_rowguid uniqueidentifier, @parent_row_inserted bit, @rowguid uniqueidentifier 
    declare @dt datetime, @nickbin varbinary(8), @error int
     
    set nocount on
    select @tablenick = 8117001    
    if @article_rows_deleted = 1 select @rowguid = rowguidcol from deleted
    select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
    select @dt = getdate()

    select @replnick = 0x7b8fe113ebeb
    set @nickbin= @replnick + 0xFF

    select @newgen = NULL
        select top 1 @newgen = generation from [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock, updlock, readpast) 
        where art_nick = 8117001      and genstatus = 0    
        
            and  changecount <= (1000 - isnull(@article_rows_deleted,0))
    if @newgen is NULL
    begin
        insert into [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
            (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
               values (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_deleted)
        select @error = @@error, @newgen = @@identity    
        if @error<>0 or @newgen is NULL
            goto FAILURE
    end
    else
    begin
        -- now update the changecount of the generation we go to reflect the number of rows we put in this generation
        update [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
            set changecount = changecount + @article_rows_deleted
            where generation = @newgen
        if @@error<>0 goto FAILURE
    end
  
    set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) }  
    if @article_rows_deleted = 1
        insert into [dbo].[MSmerge_tsvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock) (rowguid, tablenick, type, lineage, generation)
            select @rowguid, @tablenick, 1, isnull((select { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) } from 
            [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] c with (rowlock) where c.tablenick = @tablenick and c.rowguid = @rowguid),@lineage), @newgen
    else
        insert into [dbo].[MSmerge_tsvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock) (rowguid, tablenick, type, lineage, generation)
            select d.rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) }, @newgen from 
            deleted d left outer join [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] c with (rowlock) on c.tablenick = @tablenick and c.rowguid = d.rowguidcol 
             
    if @@error <> 0
        GOTO FAILURE  
        delete [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
        from deleted d, [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] cont with (rowlock)
        where cont.tablenick = @tablenick and cont.rowguid = d.rowguidcol
        option (force order, loop join)

    if @@error <> 0
        GOTO FAILURE

    
    return
FAILURE:
    if @@trancount > 0
        rollback tran
    raiserror (20041, 16, -1)
    return  

GO

Open in new window


Thanks
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41737221
Okay, that's part 1.
But, sorry, "but the text file can contain trigger for update" does not qualify as "detailing exactly which line(s) from the code box need to be removed under which conditions."
The part beginning and including line 5 up until and including line 86 can be removed by simply changing the % to an underscore:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$sql -replace '(?s)create trigger \[MSmerge_.*?\r\nGO', ''

Open in new window

0
 

Author Comment

by:bibi92
ID: 41737232
Hello

I need to remove all text block following :

create trigger [MSmerge% ...
.....
.....
....
GO

Open in new window


I have tried $sql -replace '(?s)create trigger \[MSmerge_.*?\r\nGO', ''

The replace is not done. I try with  Out-File "C:\Temp\test.sql"
Thanks
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 83

Expert Comment

by:oBdA
ID: 41737261
Could be an issue with line endings. Could you please attach the file you're using for testing using the "Attach file" link below the Edit field?
0
 

Author Comment

by:bibi92
ID: 41737270
Hello,

Maybe
In attachment, the file.
I try to remove too
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Thanks
trigger_msmerge.txt
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41737288
The line endings in the uploaded file are what's expected on a Windows system, that is, CRLF.
The script worked just fine here, except for the trigger starting in line 227. That one is skipped because the name is not enclosed in square brackets.
This now eradicates everything from the input file, because it only contains "create trigger" statements; don't know if that's intentional, and the full file has additional content.
Note that for easier testing, there's currently no export to an output file, just the pipeline/console output:
$Sql = [IO.File]::ReadAllText("C:\Temp\trigger_msmerge.txt")
$sql -replace '(?s)SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO(\r\n)+create trigger \[?MSmerge_.*?(\r\n)+GO(\r\n)?', ''

Open in new window

0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41737290
This seems to get them, give this a try.  It will display the results to the screen for testing.

$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)create trigger \[MSmerge.*?\nGO', ''

Open in new window

~bp
0
 
LVL 83

Accepted Solution

by:
oBdA earned 500 total points
ID: 41737300
This now handles both CRLF as well as single LF as EOL, just in case:
$Sql = [IO.File]::ReadAllText("C:\Temp\trigger_msmerge.txt")
$Sql -replace '(?s)SET ANSI_NULLS ON\r?\nGO\r?\nSET QUOTED_IDENTIFIER ON\r?\nGO(\r?\n)+create trigger \[?MSmerge_.*?(\r?\n)+GO(\r?\n)?', ''

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 41737337
Thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now