PowerShell script to read excel and write into text file

Question

I am trying to write a script in power shell to read the first column value in excel, find the value in text file and replace that value with value in second column of the excel file.I am new to the power shell. Please help. Below is the code:

$excel = Import-Csv -Path C:\Users\abc\Desktop\newnbc.csv
foreach ($ex in $excel)
{
    $name = $ex.first  
    $match = Get-Content -Path 'C:\Users\abc\Desktop\all.txt'
    foreach ($ma in $match)
    {
        if ($match | %{$_ -replace $ex.first,$ex.last})
        {
          ((Get-Content -Path C:\Users\abc\Desktop\all.txt -Raw) -replace $name,$ex.last) | Set-Content -Path C:\Users\abc\Desktop\all.txt
        }

    } 
}

what is the issue/error you are facing? it is good to add that in the question? — Ashish Modi, Jan 06 '20 at 10:33
The file is replacing entirely with the first value in first column — Arna k, Jan 06 '20 at 10:59
first last i3-chq-nbc-dt-comcleanup-cmd i3-chq-nbc-dt-d-com-cleanup — Arna k, Jan 06 '20 at 11:31
**Arna k**, if your csv doesn't contain headers, it is not a common csv file therefore it is important to add samples of the `cvs`file and the `text` file (and expected output) ***to the question***. (see: [How to Ask](https://stackoverflow.com/help/how-to-ask)). — iRon, Jan 06 '20 at 11:54
Does this answer your question? [Using Powershell to replace multiple strings in multiple files & folders](https://stackoverflow.com/questions/32014105/using-powershell-to-replace-multiple-strings-in-multiple-files-folders) — iRon, Jan 06 '20 at 12:08
Please [edit](https://stackoverflow.com/posts/59610130/edit) your question and show an example of the input csv there (the first 3 or 4 lines will do nicely). Right now, as you pasted in a comment, there is no telling what the structure really looks like. — Theo, Jan 06 '20 at 12:33

score 0 · Answer 1 · answered Jan 06 '20 at 12:54

Judging by the info from your comment, it looks like the input CSV is a space-delimited file, something like this:

first last
i3-chq-nbc-dt-comcleanup-cmd i3-chq-nbc-dt-d-com-cleanup

Of course, if the delimiter character is something other than a space, change the value for the -Delimiter parameter.

$fileName = 'C:\Users\abc\Desktop\all.txt'
# get the original content of the text file BEFORE the loop
$content  = Get-Content -Path $fileName -Raw
# then, import the csv with the replacement strings and start replacing the content
Import-Csv -Path 'C:\Users\abc\Desktop\newnbc.csv' -Delimiter ' ' | ForEach-Object {
    $content = $content -replace $_.first, $_.last
}
# finally, save the updated content to file
$content | Set-Content -Path $fileName

PowerShell script to read excel and write into text file

1 Answers1