Formatting xml fields from a csv with powershell

Question

I have a CSV of hostname,ip of routers in our network and I want to create an XML template for importing these all into putty.

I have the xml schema required

My existing plan was to import the CSV and call it $router then place $router.hostname and $router.ip into the script, but I can't work out how to make this populate the text with the csv values nor how to then export it as XML.

Ideally it would then output each xml as a file per router $hostname.xml.

Does anyone have any pointers on how to make this work? I have a fair amount of experience with PS and CSV but never XML, I'm aware this is gonna need a foreach but I am fairly new at this all.

Thanks Sam

$router=import-csv C:\Users\Admin\Desktop\RouterIPHost.csv

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<root expanded="True" name="Test MSP" type="database" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection name=($router.hostname) type="PuTTY">
<name>$Putty.name </name>
<protocol>Telnet</protocol>
<host>($router.ip)</host>
<port>23</port>
<session>Default Settings</session>
<connectiontimeout>1500</connectiontimeout>
<logintimeout>1000</logintimeout>
<passwordtimeout>1000</passwordtimeout>
<commandtimeout>1000</commandtimeout>
<postcommands>False</postcommands>
</connection>
</root>

Export-Clixml -path "C:\Users\PRTG_Admin\Desktop\xmlrouter.xml"

Be aware that Export-CLIXML exports data to a format that is specific to powershell that is for storing objects. It includes information about the objects themselves in addition to the data. It is not what you are looking for. Basically you will need to create the root node then append child nodes to build the XML. — EBGreen
– EBGreen, Commented Jun 5, 2018 at 13:25
also what have you tried so far with foreach and writetohost — moore1emu
– moore1emu, Commented Jun 5, 2018 at 14:41

henrycarteruk · Accepted Answer · 2018-06-05 15:31:33Z

0

Assuming a csv format of:

hostname,ip
router_site_london,192.168.0.1
router_site_paris,192.168.1.1
router_site_newyork,192.168.2.1

You can treat the files as plain text and just create them in a loop from a multi-line string using variables:

$routers = Import-Csv C:\Users\Admin\Desktop\RouterIPHost.csv

foreach ($router in $routers) {

$router_hostname = $router.hostname
$router_ip = $router.ip

$xml = @"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<root expanded="True" name="Test MSP" type="database" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection name=$router_hostname type="PuTTY">
<name>$router_hostname</name>
<protocol>Telnet</protocol>
<host>$router_ip</host>
<port>23</port>
<session>Default Settings</session>
<connectiontimeout>1500</connectiontimeout>
<logintimeout>1000</logintimeout>
<passwordtimeout>1000</passwordtimeout>
<commandtimeout>1000</commandtimeout>
<postcommands>False</postcommands>
</connection>
</root>
"@

$xml | Out-File -FilePath C:\folder\putty_routers\$router_hostname.xml
}

answered Jun 5, 2018 at 15:31

henrycarteruk

13.3k2 gold badges40 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel A Over a year ago

Hi! This is exactly what i was hoping for, as much as i'd like to understand XML more fully, handling this as text was perfect for my needs and urgency. Worked a treat with a bit of fiddling to make columns match. Thanks so much!

Collectives™ on Stack Overflow

Formatting xml fields from a csv with powershell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related