Excel Routes Banner

Export and Import Persistent Routes into Windows 2012R2

In previous versions of Windows if you wanted to import a large number of persistent routes you could import them directly into the registry. This no longer works on Windows 2012R2 so here’s a simple method using excel to create the ROUTE ADD commands.

Export Routes from Source Server

On the source server run regedit.exe and browse down to:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters\PersistentRoutes

Right click on PersistentRoutes and Export to a .reg file.

Registry export routes

Export the existing routes with regedit

 

Import the .reg File into Excel

Copy the .reg file to your workstation that has Excel on it.

Open Excel, then File/Open and browse to the location of the .reg file – make sure you have All Files selected in the drop-down so you can see the .reg file. This should kick off the Excel text import wizard. Follow the three simple steps to import the .reg into Excel:

1) Choose Delimited

Excel Text Import Wizard 1

Excel Text Import Wizard Step 1

2) Check only “Comma” and change the Text Qualifier to “{none}”

Excel Text Import Wizard 2

Excel Text Import Wizard Step 2

3) Leave as General

Excel Text Import Wizard 3

Excel Text Import Wizard 3

Once completed you should have an Excel that looks something like this, don’t worry we’ll clean it up in the next step.

Excel Raw Import

Excel Raw Import

 

Clean up Excel File

In Excel, delete the first three rows that contain the regedit header info. Also delete column D – the one that contains entries of 1″=””.

Then select column A and hit CTRL+F to open the find and replace window. Click on the Replace tab and put a double quote in the “Find what:” box (leave the replace box empty) and click on Replace All – this removes all those double quotes from the column.

Find and Replace

Find and Replace

You should now have a nice clean Excel sheet that looks like this:

Cleaned up Excel IPs

Cleaned up Excel IPs

 

Create Command Batch file from Excel.

So now to the clever bit copy the formula from below and paste it into Cell D1 (make sure you leave all the spaces in the formula).

="Route add "&A1&" mask "&B1&" "&C1&" /p"

You should see that the cell now magically contains the DOS command to add the required route. You can quickly add the formula to the cells below by clicking on the cell D1, then shift click on the last cell in column D that you want the formula to populate and then CTRL+D to “fill down”.

Your excel should now look like this:

Excel with ROUTE ADD

Excel with ROUTE ADD

Finally, we’ll create a new worksheet for the commands; select column D and right-click/copy.

Open a new worksheet by clicking on the + in the sheet tabs area. On the new sheet right-click on the first cell and choose Paste Values (if you do a regular paste you’ll get a lot of #ref! errors).

Final Excel Route Commands

Final Excel Route Commands

Save this worksheet as a text file and you now have all the commands you need to add the persistent routes on the destination server.

If you are the cautious sort you can cut and paste the commands into an administrator command prompt on the destination server. If you are confident you can rename the .txt file to a .bat, copy it to the destination server and run it (as admin) to add all the routes in one hit.

8 thoughts on “Export and Import Persistent Routes into Windows 2012R2

  1. I almost skipped over this hack because it looked like too many steps.. Glad I didn’t .. Super easy .. I had 100 static routes that somehow got deleted.. Took 2 mins to complete.. saved 2+ hours of work! Thanks so much!

Leave a Reply