none
Read excel file and replace string with text file RRS feed

  • Question

  • I'm a new to powershell and stuck with a problem:

    I'm trying to open a excel and search a for a string, if a string matches I want to replace that string from the first value on a text file, and it will run in a loop for a as many strings specified in an array.

    test file:

    Read1

    Read2

    Read3

    so my array is like $text = ($text = “text1”,”text2”,”text3”,”text3”)

    so it will open a excel file, search for first value in array "text1", once found, replace value from the first value in text file i.e "Read1". It will continue for the whole $text and then save the excel file with a new name name.

    Tuesday, April 21, 2015 8:05 PM

Answers

  • Hi Jawano,

    I assume the array "$text" and the text.txt file have good reflections, for example if I find a match "text2" in the array, I will replace the vaule to "read2".

    In this case, please refer to the script below, which will loop every value in array, and check if we find a match string in Excel, we will replace this string, I also recommend you can backup the excel file in case of lose data:

    The test.txt file listed like:

    Read

    Read1

    ...

    $text = "text","text1","text2","text3"
    $replace=get-content d:\test.txt
    $File = "d:\test.xlsx"
    
    # Setup Excel, open $File and set the the first worksheet
    $i=0
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    $Range = $Worksheet.Range("A1").EntireColumn
    Foreach($SearchString in $text){
    $Search = $Range.find($SearchString)
    if ($search){
    $SearchString
    $replace[$i]
    $Search.value() = $replace[$i]
    }
    $i++
     }
    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang



    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Marked as answer by jawano Thursday, April 23, 2015 10:33 AM
    Wednesday, April 22, 2015 9:44 AM
    Moderator
  • Hi Jawano,

    Please refer to the script below:

    $replace = get-content d:\test.txt|foreach{
    ($_.split(","))[0]
    }

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang



    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Marked as answer by jawano Friday, April 24, 2015 6:03 PM
    Thursday, April 23, 2015 11:21 AM
    Moderator

All replies

  • Tuesday, April 21, 2015 8:19 PM
    Moderator
  • I have a script, but that is missing many things:

    1) I don't know how to read the value from text file, this script only runs once and I can't loop it to go through array and it doesn't save to new file:

    $remoteappserver1 = "RemoteAPPServer1"
    $remoteappserver2 = "RemoteAPPServer1"

    $newservername = ($remoteappserver1, $remoteappserver2)

    $File = "C:\Script\ports.xlsx"

    # Setup Excel, open $File and set the the first worksheet
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)

    $SearchString = "$newservername" # This is the value I will be searching for and want to loop through array

    $Range = $Worksheet.Range("A1","Z10").EntireColumn

    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
    $FirstAddress = $search.Address
    do {
    $Search.value() = $FirstValueInTextfile # replacing value with first value in txt file, not sure how to open and read that and loop through

    $search = $Range.FindNext($search)
    } while ( $search -ne $null -and $search.Address -ne $FirstAddress)
    }

    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit() 

    Tuesday, April 21, 2015 8:29 PM
  • Sounds bad.  Have you tried to search for examples of how to use Excel in script?  Most of what you ask has many examples all over the net.  Pick one item  and solve it then do the next item.  This is how you learn to sue Excel and learn how to script.

    You are asking how to replace a cell with a file.  Why?  That does not make much sense in Excel.

    To "Search and Replace" you need two items per replacement.  You need the string to search for and the string to replace it with.

    A file that does that would look like this:

    search,replcae
    text1, repalce1
    text2,replace2

    etc.

    Now look up how to search and replace in Excel and update your script.

    To read a file in loop you can easily use:

    foreach($line  in (Get-Content $file)){ #process line }


    \_(ツ)_/

    • Proposed as answer by Koen Halfwerk Wednesday, April 22, 2015 8:48 AM
    Tuesday, April 21, 2015 10:05 PM
    Moderator
  • Hi Jawano,

    I assume the array "$text" and the text.txt file have good reflections, for example if I find a match "text2" in the array, I will replace the vaule to "read2".

    In this case, please refer to the script below, which will loop every value in array, and check if we find a match string in Excel, we will replace this string, I also recommend you can backup the excel file in case of lose data:

    The test.txt file listed like:

    Read

    Read1

    ...

    $text = "text","text1","text2","text3"
    $replace=get-content d:\test.txt
    $File = "d:\test.xlsx"
    
    # Setup Excel, open $File and set the the first worksheet
    $i=0
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    $Range = $Worksheet.Range("A1").EntireColumn
    Foreach($SearchString in $text){
    $Search = $Range.find($SearchString)
    if ($search){
    $SearchString
    $replace[$i]
    $Search.value() = $replace[$i]
    }
    $i++
     }
    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang



    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Marked as answer by jawano Thursday, April 23, 2015 10:33 AM
    Wednesday, April 22, 2015 9:44 AM
    Moderator
  • Thanks a lot it is somewhat working. Issue is when you do a search for value "text" or "text1" any of the values in array $text it can appear any where in excel sheet, meaning it can appear at column A1, B6 or Z10 and it can appear at multiple columns not just once but more than once. 

    So i have "text" in column A1, B5, C3 it doesn't do replace for the all values of "text" in all columns.

    you can try this: Copy the value to multiple column, and create your text file with Read, Read1, Read2 and run it it changes some values and skip some. 

    I changed the range something like this $Range = $Worksheet.Range("A1","Z10").EntireColumn but that didn't help.

    I had an example where it works for all columns, im pasting here:

    # Setup Excel, open $File and set the the first worksheet
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)

    $SearchString = "Read"  # This is the value that I will be searching for

    $Range = $Worksheet.Range("A1","Z10").EntireColumn

    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
    $FirstAddress = $search.Address
    do {
    $Search.value() = $text
    $search = $Range.FindNext($search)
    } while ( $search -ne $null -and $search.Address -ne $FirstAddress)
    }

    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    Wednesday, April 22, 2015 7:07 PM
  • ok so I was able to make it work up to a point where it will find all "text" values in entire spreadsheet and replace them with read, but if it finds "text1", or "text2" it would replace as "read" also not as "read1" or "read2". Seems like $replace(i) is not incrementing: here is the code:

    $text = "text","text1","text2","text3"
    $replace=get-content C:\script\test.txt
    $File = "C:\script\test.xlsx"

    # Setup Excel, open $File and set the the first worksheet
    $i=0
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    $Range = $Worksheet.Range("A1","Z10").EntireColumn

    Foreach($SearchString in $text){
    $Search = $Range.find($SearchString)

    if ($search -ne $null){

    $SearchString
    $replace[$i]

    $FirstAddress = $search.Address
    do {
    $Search.value() = $replace[$i]
    $search = $Range.FindNext($search)
    } while ( $search -ne $null -and $search.Address -ne $FirstAddress )
    $i++
    }

     }
    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()


    Wednesday, April 22, 2015 8:06 PM
  • You need to learn a bit more about how Excel works and you will be there.

    Find in Excel will find a match but you have to tell Excel to find an "exact" match or it will match any cell that has that in other text.

    To select the whole worksheet just use "UsedRange"

    foreach($item in $Worksheet.UsedRange.Find('Test1',$missing,$missing,$xlWhole))
         # process item.

    That will search everywhere for an exact match in any cell.  Try it.  It can even be made case sensitive.

    https://msdn.microsoft.com/en-us/library/office/ff839746.aspx


    \_(ツ)_/


    Wednesday, April 22, 2015 9:16 PM
    Moderator
  • Sorry -

    $missing=[type]::Missing

    $xlWhole=[Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole


    \_(ツ)_/

    Wednesday, April 22, 2015 9:52 PM
    Moderator
  • Im trying to understand where to use this my code
    Wednesday, April 22, 2015 10:16 PM
  • Hey,

    I figure this out thanks, made few changes but now it search the entire excelsheet

    $text = "text","text1","text2","text3"
    $replace=get-content C:\script\test.txt
    $File = "C:\script\test.xlsx"
    $now = [datetime]::now.ToString("yyyy-MM-dd")
    #$now = get-date -Format "MM-dd-yyyy_hh:mm:ss"
    copy-Item C:\script\test.xlsx test_$now.xlsx


    # Setup Excel, open $File and set the the first worksheet
    $i=0
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    $Range = $Worksheet.UsedRange

    Foreach($SearchString in $text){

    if ($Range.find("$SearchString")) {
        $Range.replace($SearchString,$replace[$i])
        } 
    else {$i++}
    }

    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    Thursday, April 23, 2015 10:33 AM
  • I have a question to ask, so the text file which reads as 

    test.txt

    read

    read1 

    read 2

    if my text file has value like 

    read, book

    read1, book1

    read2, book2

    but i want to read only the read, read1, read2 before comma and pass that to variable $replace how I can do this? because when I do get-content c:\test.txt it reads the read, book value both and my search fails

    any ideas?



    Thursday, April 23, 2015 10:38 AM
  • Hi Jawano,

    Please refer to the script below:

    $replace = get-content d:\test.txt|foreach{
    ($_.split(","))[0]
    }

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang



    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Marked as answer by jawano Friday, April 24, 2015 6:03 PM
    Thursday, April 23, 2015 11:21 AM
    Moderator
  • Thanks a million it works. 

    I need a little more help on this one:

    1) My text file, has two values on each line:

    Read, book345

    Read1, book001

    Read2, Book098

    So what we doing we open excel, search for a aray like "text" and replace with text file value "read".

    What I'm trying to do is when it finds the value "text" and replace with value "read", it will also replace what ever value is in next column with, "book345" i so thats how it will look:

    now my excel file looks this way, it has two columns:

    column   column2

    text        page

    text1      page4

    text2      page9

    once script will run it will look like this

    Read        book345

    Read1      book001

    Read2      Book098

    my text file can be a csv file comma separated, with column headers if that will help

    2) I'm trying to rename the file with current system date and time before running script:

    #$now = get-date -Format "MM-dd-yyyy_hh:mm:ss"
    copy-Item C:\script\test.xlsx test_$now.xlsx

    but when I do this it get a error that drive not found, if remove time "hh:mm:ss" it works fine.

    really appreciate your help.



    • Edited by jawano Friday, April 24, 2015 6:37 PM
    Friday, April 24, 2015 6:34 PM
  • You have asked the question at least five different ways.  Each time you get an answer you change the question.  This is not how a scripting support forum works.  What you are doing is just  forcing others to write the code and to figure out what you want.  I asked you in the very beginning  if this was what you wanted and you ignored all attempts by any of use to show you how to do it.

    I recommend that you contact a consultant too work with you figuring this out.  The forum is not a free customscript writing forum.

    Sorry.


    \_(ツ)_/

    Friday, April 24, 2015 7:11 PM
    Moderator