Extract specific text and extract

Question

I am trying to create a PowerShell to read a SQL Dump text file and read this file from a specific search point 'CREATE TABLE fks_common.bo_bdsvragen' until the first place that I find ';)'. The lines between I want to extract and write to a new file. I am just starting to learn PowerShell and definite need some help. Also it would be nice that the solution perform since the search dump file is very big (40Gb).

$Zoektext = '*CREATE TABLE fks_common.bo_bdsvragen*'

$FILE = Get-Content "c:\scripts\dump.sql" | Where-object {$_ -like $Zoektext}

foreach ($LINE in $FILE)
{
    $Tekst = 'Op regel ' + $Line.ReadCount + ' staat ' + $LINE 
    Write-Output $Tekst
}

Answer 1

I think you have swapped the characters for the end of the table declaration ;) should be );

With a large file like this, it would be fastest to use switch.

Suppose your dump.sql file looks like this:

blah
blahblahblah
blahblahblahblah
CREATE TABLE fks_common.bo_bdsvragen (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
more blah
and a lot more blahblah

Then you can do this to exctract the text you're interested in like below:

# create two regex escaped strings for the beginning and end of the table declaration
$tableBegin = [regex]::Escape('CREATE TABLE fks_common.bo_bdsvragen')
$tableEnd   = [regex]::Escape(');')
# set a boolean flag to $false
$foundBegin = $false

# loop through the text line-by-line and capture only what you seek
$result = switch -Regex -File 'c:\scripts\dump.sql' {
    $tableBegin { $foundBegin = $true; $_ }  # set the flag and output this line
    $tableEnd   { if ($foundBegin) { $_ ; break }  }         # we've reached the end of the CREATE_TABLE declaration
    default     { if ($foundBegin) {$_ } }   # only output this line if $foundBegin is $true
}

# output on screen
$result

# output to new file
$result | Set-Content -Path 'c:\scripts\table.sql'

Output:

CREATE TABLE fks_common.bo_bdsvragen (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);