Find and Replace strings in a TEXT FILE using values from two columns in an excel sheet, respectively for the FIND and REPLACE string

Question

I have many large "configuration" text files containing IP ADDRESSES on an old network [Router configurations. Switch configurations. Firewall configurations, etc.]

The configuration files may look something like this where the gibberish is some other important string but not relevant.

"asdhlksdjfkdlsh sldkhdlfh OLDIPADDRRESS1 sdalkjhdslkfhkhdasf sadkhjksdafh sdafkhkjdhfsakjhkdjfsa sadfk

adsfkjhkjdsf adfhjhjakds OLDIPADDRESS400 ljlkjjl

adskjhkhkhkjh OLDIPADDRESS10"

The IP addresses in the configuration txt file are NOT in any specific order.

I also have an excel sheet that contains many many columns of data. Column C = list of Old Ip address (example row1: 172.10.10.44, row2:...etc to row 500 something) Column E = list of New IP address (example row1: 10.10.10.58, row2:...., etc to row 500 something)

I would like to replace all the "old" ip addresses in the text file with the appropriate "new" Ip addresses. The remainder of the text file must remain AS IS. Only the old ip addresses need to be replaced with the new ip addresses.

HOW? I imagine there would be some iterative process in which the find function has to grab the old ip address from Column C excel and search for it in the notepad/txt file, and then replace it with the new ip address that is corresponding from the same row as the old ip address in the excel, but from Column E. And then repeat this going down, row by row through the excel sheet until all rows are exhausted.

Notepad ++? Python? VBA? etc?

Answer 1

If you have hundreds of ip addresses:

Sort 'old' ip addresses in Column C, making sure Column E follows along

Sort the text file list of 'old' ip addresses using same sorting criteria you selected when you sorted Column C

Create new blank column beside sorted column C

Paste sorted data (old ip addresses) from the text file into new blank column adjacent the sorted column C

Review data in new column and sorted column C to ensure the data matches

If you want a new text file of the new ip addresses, copy data from Column E into new text file

Answer 2

I used a text file with your three examples, and a second file, an XLSX, with the three "OLD IP ADDRESSES" in a different order than above in its column C. The "NEW IP ADDRESSES" were in column E and matched the order of column C.

I chose to let them match that order since you give pretty sufficient information and it seems unlikely you would not have them in the same order and not mention that fact. I also don't see how it could work if that were so, so... there's that too!

The XLSX file I used is called "Book 3.xlxs" and obviously, you would edit that to fit your name. The text file gets opened and immediately converted to an Excel file. (Presumably you can successfully open it as an Excel file. And then save it back into standard text file format or the .TXT format you find it in.) However, you can do it in a more complicated way if necessary. Due to the MANY possibilities, I shall not delve into that.

The formula I used is:

=SUBSTITUTE(A1,  INDEX([Book3]Sheet1!$C$2:$C$4,XMATCH(1000000,SEARCH([Book3]Sheet1!$C$2:$C$4,A1,1),-1),1),  INDEX([Book3]Sheet1!$E$2:$E$4,XMATCH(1000000,SEARCH([Book3]Sheet1!$C$2:$C$4,A1,1),-1),1))

There are three basic sections: 1) The SUBSTITUTE that does the final work, 2) The first INDEX/XMATCH that finds the OLDIPADDRESS in the source data, and 3) The INDEX/XMATCH that finds the new one for each line's source data.

The INDEX/XMATCH is in each case, a standard INDEX/MATCH style construction. The important part is the SEARCH inside them. SEARCH is used to test the text data for any given line against ALL the Old IP Addresses. ONE of them will give a number, the rest an ERROR. XMATCH (not MATCH as it cannot do this) is used to seek the number 1,000,000 which will be greater than any value SEARCH can yield since there is a limit to how many characters an Excel cell can contain. The match type is "Exact, or next smaller" so it finds the only numerical result. That result is the row of the column C data that the Old IP Address in the line being checked's data cell matches.

And then the other INDEX/XMATCH does the same to find the corresponding row of the New IP Addresses.

Those two parts operate independently of each other, which is usually a bad idea if one use one to feed the other, but in this case the only drawback is that an edit, to upgrade it perhaps, needs to be done equally to the other clause. For example, the ranges must stay synchronized.

Finally, the SUBSTITUTE comes into the fray replacing the old address with the new address.

At this point, you have a new column in which all the old addresses have been replaced by the new addresses.

As mentioned, you seem to have provided a complete set of information, so I have provided nothing in the way of "if error, do this"... And couldn't really, without more information. Since there is no reason an address cannot exist many times in the text file, its greatly larger number of rows than in the XLSX file is neither unlikely nor unreasonable. However, if there will or could be TXT file data that will not match up with the new IP information, you would want to provide a path for errors due to no match to carry the original data from those rows through to the new column.

Finally, you would copy the new column and paste it as values (Paste|Special|Values) overtop the original column. To avoid difficulties (complexities) with the TXT file, create the replacement column in the XLSX file. And, if need be, simply close it afterward without saving.