Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

remove blocks text containing several lines in a text file

Posted on 2016-08-01
12
Medium Priority
?
29 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 85

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 85

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 85

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
 
LVL 85

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 85

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 57

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 85

Accepted Solution

by:
oBdA earned 2000 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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