Skip to content
Advertisement

Save list of txt files through referencing array [Powershell]

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
}
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement