I have a file that has some names (table_names.txt) whose contents are:
ALL_Dog ALL_Cat ALL_Fish
and another file that has some entries (test.txt) whose contents include the above names, like:
INSERT INTO ALL_Dog VALUES (1,2,3) INSERT INTO ALL_Cat VALUES (2,3,4) INSERT INTO ALL_Fish VALUES (3,4,5)
I need to write a for loop in powershell that creates, within my current directory three separate files: ALL_Dog.txt whose contents are “INSERT INTO ALL_Dog VALUES (1,2,3)”, ALL_Cat.txt whose contents are “INSERT INTO ALL_Cat VALUES (2,3,4)”, ALL_Fish.txt whose contents are “INSERT INTO ALL_Fish VALUES (3,4,5)”
Here’s what I have so far:
[string[]]$tableNameArray = (Get-Content -Path '.table_names.txt') | foreach {$_ + " VALUES"} [string[]]$namingArray = (Get-Content -Path '.table_names.txt') | foreach {$_} For($i=0; $i -lt $tableNameArray.Length; $i++) {Get-Content test.txt| Select-String -Pattern $tableNameArray[$i] -Encoding ASCII | Select-Object -ExpandProperty Line | Out-File -LiteralPath $namingArray[$i]}
The problem with what I currently have is that I cannot define the output files as .txt files, so my output files are just “ALL_Dog”, “ALL_Cat”, and “ALL_Fish”.
The solution I’m looking for involves iteration through this namingArray to actually name the output files.
I feel like I’m really close to a solution and would mightily appreciate anyone’s assistance or guidance to the correct result.
Advertisement
Answer
If I understand the question properly, you would like to get all lines from one file containing a certain table name and create a new textfile with these lines and having the table name as filename, with a .txt
extension, correct?
In that case, I would do something like below:
$outputPath = 'D:Test' # the folder where the output files should go $inputNames = 'D:Testtable_names.txt' $inputCommands = 'D:Testtest.txt' # make sure the table names from this file do not have leading or trailing whitespaces $table_names = Get-Content -Path $inputNames | ForEach-Object { $_.Trim() } $sqlCommands = Get-Content -Path $inputCommands # loop through the table names foreach ($table in $table_names) { # prepare the regex pattern b (word boundary) means you are searching for a whole word $pattern = 'b{0}b' -f [regex]::Escape($table) # construct the output file path and name $outFile = Join-Path -Path $outputPath -ChildPath ('{0}.txt' -f $table) # get the string(s) using the pattern and write the file ($sqlCommands | Select-String -Pattern $pattern).Line | Out-File -FilePath $outFile -Append }