in

WindowsWindows Loving ItLoving It

[WORKING] Parse XML | JSON | Excel in PowerShell quick syntax

Parse XML JSON and, Excel with PowerShell using quick synatx. Use different properties to interact with CSV files and custom PS objects in PowerShell

PowerShell parse xml

This post is divided into 3 sections, the first is to parse excel, second is to parse JSON and finally, we have the section to parse XML.  If you are here for a specific section, we recommend you to go the section directly using the quick Jump to provided below.  Otherwise, we recommend you check out all the sections carefully for something that you might not have already known.

Jump to:

People also search for:

PowerShell parse Excel quick syntax:

Here we are going to quickly open an excel file and interact with the contents so that any reader returning to the site can use this section for quick reference.  Without further delay, let’s open an excel file located in the current location.

  • We need an instance of Microsoft Excel for this purpose.  The following command will create an instance of Microsoft Excel for us.

$Ecl = new-object -comobject excel.application

excel com object
  • Each excel file is a workbook and so we will use the Excel object to open an excel file using the workbook property.
  • We need to provide the full path to the file in order for this to work.
  • Please ensure that the file is not open in any other excel application.

$Workbook = $Ecl.Workbooks.Open(“E:\pwrshl\survey.csv”)

open csv file powershell
open csv file powershell
  • Since our necessary data is always inside a sheet inside an excel file.  We will use the sheet name or the sheet index to get the data inside the sheet.
  • Note that our sheet name is “sheet”.
sheet name is survey

$wsheet = $Workbook.Worksheets.Item(“survey”)

open sheet
  • We need to finally active the sheet using the below script.

$wsheet.Activate()

  • We will get to the different values of the sheet using the Value2 property.  For example, we can get the contents of the column A using the below script.

$wsheet.Columns(“A”).value2

  • And we can select the first 10 rows using the below script.

$wsheet.Columns(“A”).value2 | select -First 10

PowerShell Parse Excel in Detail:

In this section, we will look into some of the features of the parsing excel in PowerShell.  For how to open and get to this stage, please refer to the previous section.  If you do not know how to open an excel file, you will not be able to proceed any further.

If you have opened a file, and you are looking for some additional features, please read ahead.

  • We can view the range of cells in the worksheet using Range() method.

$wsheet.Range(“A1:E1”).value2

powershell excel range of values
  • The data in the entire column is taken in a PowerShell local variable using the following script.

$col1 = $($wsheet.Range(“A1”).entirecolumn).value2

values in entire column
  • Similarly, we can take the values in the entire row and save it to a PowerShell local variable as below.

$header = $($wsheet.Range(“A1”).entirerow).value2

powershell excel entire row
  • We can also get to an individual cell using the Cell method as shown below.

$wsheet.Cells(1,1).value2

Note that, we are getting the value from the cell that corresponds to the first row, and first column.

  • The number of rows in the file can be obtained by the UsedRange property of the worksheet.

$wsheet.usedrange.rows.count

get rows count
  • Similarly, we can get the column count by the following script.

$wsheet.usedrange.Columns.count

PowerShell excel columns count
  • And last but not least, we can save the workbook using the below script.

$Workbook.Save()

PowerShell workbook save

There are many more interesting properties and methods to the excel parsing and if you want to know more about it, please let us know in the comments below.

PowerShell Parse JSON quick syntax:

Below we are using the Invoke-WebRequest to request a URL and get the JSON output to parse it to a custom object using the ConvertFrom-Json commandlet.

$url = “http://musicbrainz.org/ws/2/artist/5b11f4ce-a62d-471e-81fc-a69a8278c7da?inc=aliases&fmt=json”

$res = iwr $url

$cobj = $res.Content | ConvertFrom-Json

In the above script replace the $url value with the URL you will get the JSON from.

PowerShell parse json syntax

PowerShell Parse JSON explained in detail:

While parsing a JSON in PowerShell, we are creating a custom object.  Each field in the JSON is converted to a custom object property.  Due to this one to one mapping, we are able to convert text-based definitions to custom objects in PowerShell.

For demonstrating this, we are going to use a URL that will give us a JSON output.  And we will use the Invoke-WebRequest command let to make the request to the URL and get the output.  Finally, we will use the ConvertFrom-Json to parse the JSON and get the custom object.

We will initially take up the URL in the variable called $url

$url = “http://musicbrainz.org/ws/2/artist/5b11f4ce-a62d-471e-81fc-a69a8278c7da?inc=aliases&fmt=json”

We will use the Invoke-WebRequest alias called as iwr and pass the $url as a parameter.

$res = iwr $url

The content property of the $res object has the response from the server.  We will pipe the content property to the ConvertFrom-Json and get the custom object.

$cobj = $res.Content | ConvertFrom-Json

We can view the different properties of the custom object by piping it to the get-member commandlet as shown below.

$cobj | gm

PowerShell get member

The custom objects inherit from the PsObject class.  For this reason we can get the list of fields in the custom object using the following script.

$cobj.PsObject.Properties.Name

list all properties

And similarly, we can get the values also.

$cobj.PsObject.Properties.value

values

PowerShell parse XML using the quick synatx:

Parsing the XML in PowerShell is very easy.  We can directly enter the XML in the console as shown below.

[xml]$doc = @’
<?xml version=”1.0″ encoding=”UTF-8″?>
<xml>
<Section name=”FrontendStatus”>
<FEName FE=”red” Status=”1″ />
<FEName FE=”blue” Status=”1″ />
<FEName FE=”yellow” Status=”1″ />
<FEName FE=”orange” Status=”1″ />
<FEName FE=”green” Status=”1″ />
<FEName FE=”pink” Status=”1″ />
<FEName FE=”cyan” Status=”1″ />
</Section>
</xml>
‘@

PowerShell parse xml

Entering the xml must not give an error, if it does, please ensure that there is nothing that follows @’ and leads ‘@ as shown in the above image.

Once we have parsed the XML, we can access its different members using the syntax given below.

$doc.xml.Section.FEName

The output of the above script is shown below.

xml properties

Since we are able to get the output in a tabular format, we can apply the PowerShell pipe and do regular PowerShell calculations as shown below.

$doc.xml.Section.FeName | ? { $_.Status -eq 1 } | % { $_.FE + ” is a color” }

pipe xml properties

Verdict:

Parsing Excel, JSON, and XML are very easy in PowerShell.  It is due to the fact that it inherits from the PSObject class that creating a custom object is very easy.  We have three quick syntax section for readers to quickly use it as a reference in order for them to come back later. And later down the post, we have a brief explanation for each parsing technique.

If you want to know more about them in detail, please let us know in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *