How to read keywords from a file and delete relevant section from file using window batch or powershell

Hi Experts,
pls help me out on this. bit tricky one :)

1. Have a file similar to ".ini" format header and its sections. e.g. schema.ddl
2. Another file with list of keywords on each line. e.g table_list.txt
3. Need to read each line from table_list.txt and search it in schema.ddl and remove the entire table section and its associated index section as well.

Have attached both the files for your quick reference. pls unzip schema.zip file.

E.g
"S_PARTY" is the first keyword from the table_list.txt
table section can be found in schema.ddl between line # 317941 and 317960
associated section can be found between 317962 and 318029

basically for each keyword (table name) we need to repeat the process and remove relative entries using windows batch or powershell or vbscript reason,  this will be executed on a windows machine. if any util that doesnt require installation and would easy this. we can use that util as well.

pls help and thx in advance.
schema_txt.zip
table_list.txt
enthuguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
PowerShell.
Don't know if it's required, but the script will renumber the "Object" sections to make up for the deleted ones. If you don't want/need that, set the $Renumber variable to $false.
$SchemaFile = '.\schema.txt'
$TableFile = '.\table_list.txt'
$OutSchemaFile = '.\schema_new.txt'
$Renumber = $true

Write-Host "Importing schema from '$($SchemaFile)' ..."
$ddl = [ordered]@{}
Switch -RegEx -File $SchemaFile {
	"^\[(?<Section>.+)\]" 			{$Section = $Matches['Section']; $ddl[$Section] = [ordered]@{}}
	"(?<Key>.+?)\s*=(?<Value>.*)"	{$ddl[$Section][$Matches['Key'].Trim()] = $Matches['Value'].TrimStart()}	## lines may have trailing spaces
}
ForEach ($Table In (Get-Content -Path $TableFile)) {
	$Table = $Table.Trim()
	Write-Host "Removing table '$($Table)'" -NoNewline
	$ObjectFound = $false
	ForEach ($Section In @($ddl.Keys)) {
		If (($ddl[$Section].Type -eq 'Table') -and ($ddl[$Section].Name -eq $Table)) {
			$ObjectFound = $true
			Write-Host " ($($Section))" -NoNewline
			Write-Host " - OK." -ForegroundColor Green
			$ddl.Remove($Section)
			break
		}
	}
	If (-not $ObjectFound) {Write-Host " - not found." -ForegroundColor Yellow}
	$ObjectFound = $false
	Write-Host "Removing associated objects of table '$($Table)'" -NoNewline
	ForEach ($Section In @($ddl.Keys)) {
		If ($ddl[$Section].Table -eq $Table) {
			If (-not $ObjectFound) {Write-Host ':'}
			$ObjectFound = $true
			Write-Host "  - '$($ddl[$Section].Name)' ($($Section))"
			$ddl.Remove($Section)
		}
	}
	If (-not $ObjectFound) {Write-Host " - none found." -ForegroundColor Yellow}
}

Write-Host "Exporting schema to '$($OutSchemaFile)' ..."
$Content = New-Object -TypeName System.Collections.ArrayList
$Index = 0
$Total = $ddl.Keys.Count
ForEach ($Section In $ddl.Keys) {
	$Index += 1
	[void]$Content.Add('')
	$RenamedSection = If ($Renumber -and ($Section -like 'Object *')) {"Object $($Index)"} Else {$Section}
	Write-Progress -Activity "Exporting schema [$($Index)/$($Total)] ..." -Status "$($Section): $($ddl[$Section].Type) '$($ddl[$Section].Name)'" -PercentComplete ((100 * $Index) / $Total)
	[void]$Content.Add("[$($RenamedSection)]")
	ForEach ($Property In $ddl[$Section].Keys) {
		[void]$Content.Add("$($Property) = $($ddl[$Section].$Property)")
	}
}
Write-Progress -Activity 'Done' -Status 'Done' -Completed
Set-Content -Path $OutSchemaFile -Encoding UTF8 -Value $Content
Write-Host "Done."

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enthuguyAuthor Commented:
Hi oBdA, thanks so much for taking time to help me out. Will check this now and update you shortly :)
0
enthuguyAuthor Commented:
Hi oBda, you  are really a star!

Thats exactly I was requesting...I'm so happy...thanks very much!

one last request. Could you explain me the logic please? I can understand a bit but not all :)
0
oBdACommented:
8-11: First it's reading the file content into a hash table, with the section names as keys; the values are themselves hash tables as well, containing the keys and values from inside the section.
PowerShell allows the '.' notation to access a hash table's value like a property, aside from the [] array notation. I've used the traditional [] to access the "outer" hash table (the sections), and the '.' notation to access the inner ones (the values inside the key/value pairs inside the section), because I find it easier to read.
12-36: Then it's reading the file with the table names to remove, and tries to find the respective sections/objects where the type/names match the current table name to remove. Any matching section found will be dropped from the "outer" hash table.
40-52: It's turning the hash tables back into strings, collecting the results in an array.

And while I was explaining, I noticed a slight error in the renumbering; the object index is incremented for every section, not just "Object" sections, but there's the "DDL File", so it's off by one.
Here's the fixed version:
$SchemaFile = '.\schema.txt'
$TableFile = '.\table_list.txt'
$OutSchemaFile = '.\schema_new.txt'
$Renumber = $true

Write-Host "Importing schema from '$($SchemaFile)' ..."
$ddl = [ordered]@{}
Switch -RegEx -File $SchemaFile {
	"^\[(?<Section>.+)\]" 			{$Section = $Matches['Section']; $ddl[$Section] = [ordered]@{}}
	"(?<Key>.+?)\s*=(?<Value>.*)"	{$ddl[$Section][$Matches['Key'].Trim()] = $Matches['Value'].TrimStart()}	## lines may have trailing spaces
}
ForEach ($Table In (Get-Content -Path $TableFile)) {
	$Table = $Table.Trim()
	Write-Host "Removing table '$($Table)'" -NoNewline
	$ObjectFound = $false
	ForEach ($Section In @($ddl.Keys)) {
		If (($ddl[$Section].Type -eq 'Table') -and ($ddl[$Section].Name -eq $Table)) {
			$ObjectFound = $true
			Write-Host " ($($Section))" -NoNewline
			Write-Host " - OK." -ForegroundColor Green
			$ddl.Remove($Section)
			break
		}
	}
	If (-not $ObjectFound) {Write-Host " - not found." -ForegroundColor Yellow}
	$ObjectFound = $false
	Write-Host "Removing associated objects of table '$($Table)'" -NoNewline
	ForEach ($Section In @($ddl.Keys)) {
		If ($ddl[$Section].Table -eq $Table) {
			If (-not $ObjectFound) {Write-Host ':'}
			$ObjectFound = $true
			Write-Host "  - '$($ddl[$Section].Name)' ($($Section))"
			$ddl.Remove($Section)
		}
	}
	If (-not $ObjectFound) {Write-Host " - none found." -ForegroundColor Yellow}
}

Write-Host "Exporting schema to '$($OutSchemaFile)' ..."
$Content = New-Object -TypeName System.Collections.ArrayList
$SectionIndex = 0
$ObjectIndex = 0
$Total = $ddl.Keys.Count
ForEach ($Section In $ddl.Keys) {
	$SectionIndex += 1
	$Index += 1
	[void]$Content.Add('')
	$RenamedSection = If ($Renumber -and ($Section -like 'Object *')) {$ObjectIndex += 1; "Object $($ObjectIndex)"} Else {$Section}
	Write-Progress -Activity "Exporting schema [$($SectionIndex)/$($Total)] ..." -Status "$($Section): $($ddl[$Section].Type) '$($ddl[$Section].Name)'" -PercentComplete ((100 * $SectionIndex) / $Total)
	[void]$Content.Add("[$($RenamedSection)]")
	ForEach ($Property In $ddl[$Section].Keys) {
		[void]$Content.Add("$($Property) = $($ddl[$Section].$Property)")
	}
}
Write-Progress -Activity 'Done' -Status 'Done' -Completed
Set-Content -Path $OutSchemaFile -Encoding UTF8 -Value $Content
Write-Host "Done."

Open in new window

0
enthuguyAuthor Commented:
Thanks a lot. Really appreciate your timely help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.