search and replace values in excel from comma separated text file

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of H Man H Man 1 year, 5 months ago.

  • Author
  • #24734
    Profile photo of Syed Naqvi
    Syed Naqvi

    I have a text file, which has

    Server Name, IP Address, Comments
    ABCserver1,, remote web server
    DEFserver2,, remote app server
    XYZserver3,, remote api server

    I have a excel file which has Server Name, IP Address, Protocol, Port.
    Server1, x.x.x.x, TCP, 80
    Server2, x.x.x.x, TCP, 80
    Server3, x.x.x.x, TCP, 80

    My script search for the excel spread sheet for a array that I defined [Server1,Server2,Server3], once the first value matches in excel sheet it replaces all values of Server1 with first value in text file i.e: ABCServer1 so my new excel sheet looks this:
    ABCserver1, x.x.x.x, TCP, 80
    DEFserver2, x.x.x.x, TCP, 80
    XYZserver3, x.x.x.x, TCP, 80

    What I'm trying to do is that, it will also copy the ip address in next column meaning replace the first two columns, with first column as server name and second column with IP address and ignore anything in text file after second comma
    I can get the script to replace the server name but unable to copy the IP address in next column. So this is what script will do if runs correctly:

    Before script ran on excel sheet
    Server1, x.x.x.x, TCP, 80
    Server2, x.x.x.x, TCP, 80
    Server3, x.x.x.x, TCP, 80

    ABCserver1,, TCP, 80
    DEFserver2,, TCP, 80
    XYZserver3,, TCP, 80
    Any ideas how I can replace the IP address as well in next column when it find the first value?


    $text = "Server1","Server2","Server3"
    $replace=$replace = get-content C:\script\test.txt | foreach{ ($_.split(","))[0]}
    $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
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Workbook = $$file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    $Range = $Worksheet.UsedRange
    Foreach($SearchString in $text){
    if ($Range.find("$SearchString")) {
    else {$i++}
  • #24741
    Profile photo of Rob Simmers
    Rob Simmers

    When you start coding in Excel, it's VBA, and it's rather brutal as it's a very COM interface that is really old. I believe this what you are trying to do, but if you are trying to figure something out search for VBA code or avoid Excel like the plague:

    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $workbook = $Excel.Workbooks.Add()
    $Worksheet = $Workbook.Worksheets.Item(1)
    $range = $Worksheet.Range("A1")
    $range.Cells.Value2 = "Test"
    $range.Next.Value2 ="AnotherTest"
  • #25057
    Profile photo of H Man
    H Man

    take a look at Doug Finke's Import-excel module

You must be logged in to reply to this topic.