Extract data from multiple XMLs and generate CSV report - Powershell

Question

I have few connectionstrings which are xml files. I need to generate a CSV report using all these connectionstrings with the following output:

Below is my PS script which works fine for 1 file and gives me the desired output. I have been playing around to make it work for all the files

function extractValues ($string, $values){
    return ($values | %{ $string -match "$_=([^;]+)" | Out-Null; $matches} | %{$_[1]})
}
    [string]$Value1 = '/connectionStrings/add[@name="Value1"]/@connectionString'
    [string]$Value2 = '/connectionStrings/add[@name="Value2"]/@connectionString'
    [string]$Value3 = '/connectionStrings/add[@name="Value3"]/@connectionString'

$files = Get-Childitem –Path C:\inetpub\wwwroot\*\ConfigSections\connectionStrings.config.New.config

foreach ($file in $files) {

[xml]$xml = Get-Content $file


$connectionString = ($xml | Select-Xml -XPath $Value1).ToString()
($serverName, $dbName, $user) = extractValues $connectionString @("Data Source", "Initial Catalog", "User ID")


$connectionString = ($xml | Select-Xml -XPath $Value2).ToString()
($EserverName, $EdbName, $Euser) = extractValues $connectionString @("Data Source", "Initial Catalog", "User ID")

$connectionString = ($xml | Select-Xml -XPath $Value3).ToString()
($SCSserverName, $SCSdbName, $SCSuser) = extractValues $connectionString @("Data Source", "Initial Catalog", "User ID")

$table=@"
Initial Catalog = $Value1,
Value1, connectionstring=Data Source=$serverName, Initial Catalog=$dbName, User ID=$user
Value2, connectionstring=Data Source=$EserverName, Initial Catalog=$EdbName, User ID=$Euser
Value3, connectionstring=Data Source=$SCSserverName, Initial Catalog=$SCSdbName, User ID=$SCSuser
"@


#export to a csv file
$table | Set-Content $home\desktop\test.csv
}

This only outputs csv for 1 connectionstring. Not sure, what am I missing.

Thank you

since you are setting the table here string inside a for loop you you to extend the table variable each time you loop, other wise you will only end up with 1 iteration. i.e. $table +=@"...."@ — Sum1sAdmin, Aug 31 '17 at 16:35
You are awesome @Sum1sAdmin This does work and can output content, but the format is a little messed up. So, this variable $table has multiple tables. It needs to output all these tables to a CSV. It does not have any spaces in between. Second table starts where the first table ends in the same line csv. Any suggestions? Thanks — user3421341, Aug 31 '17 at 17:35
@Sum1sAdmin never mind, I figured it out! I can control this by closing "@ however many lines down. Thanks a lot — user3421341, Aug 31 '17 at 17:38
this connected question I try to found out : https://serverfault.com/questions/872891/bizhub-c554-system-counter-xml thx — Airbeone, Sep 10 '17 at 08:46

score 0 · Answer 1 · answered Aug 31 '17 at 18:51

0

Thanks to @sum1sadmin, I was missing + when declaring my table. So, it should have been $table += instead of $table = because this $table is in the foreach loop.

answered Aug 31 '17 at 18:51

user3421341

33
1
1
8

Extract data from multiple XMLs and generate CSV report - Powershell

1 Answers1