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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
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 :)
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:
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."ASKER
Thanks a lot. Really appreciate your timely help :)
ASKER