JSON, XML and CSV in PowerShell

JSON

Parsing JSON into PowerShell objects

PowerShell has a powerful way to convert JSON strings to PowerShell objects using a cmdlet ConvertFrom-Json.

Example:

$jsonString = @"
{
    "Name": "Ivan",
    "Age": 32,
    "Skills": ["PowerShell", "Python", "Bash"],
    "Address": {
        "Street": "Lenina 1",
        "City": "Moscow",
        "ZipCode": "123456"
    }
}
"@

# Преобразование JSON в объект PowerShell
$jsonObject = $jsonString | ConvertFrom-Json

# Доступ к данным
Write-Host "Имя: $($jsonObject.Name)"
Write-Host "Первый навык: $($jsonObject.Skills[0])"
Write-Host "Город: $($jsonObject.Address.City)"

But not everything is as simple as it might seem. It is important to consider the data depth and use the parameter -Depthwhen working with more complex structures. By default ConvertFrom-Json handles only three levels of nesting.

Example:

$jsonString = @"
{
    "Company": {
        "Departments": [
            {
                "Name": "IT",
                "Employees": [
                    {"Name": "Ivan", "Role": "SysAdmin"},
                    {"Name": "Sara", "Role": "DevOps"}
                ]
            }
        ]
    }
}
"@

$jsonObject = $jsonString | ConvertFrom-Json -Depth 5
Write-Host $jsonObject.Company.Departments[0].Employees[1].Name

Here -Depth 5 allows you to access deeply nested data.

JSON Data Manipulation

It's time to manipulate the data

PowerShell hasAdd-Member to add new fields to an object. The example below adds a new property to a JSON object:

$jsonObject | Add-Member -MemberType NoteProperty -Name "Email" -Value "[email protected]"
Write-Host $jsonObject

After running this command the object will contain a new property Email.

To change the value of a property, simply assign a new value to the property:

$jsonObject.Name = "Sasha"
Write-Host $jsonObject.Name

You will change the property Nameand the object will be automatically updated.

You can remove a property from an object using the method Remove():

$jsonObject.PSObject.Properties.Remove("Email")
Write-Host $jsonObject

Generating JSON from PowerShell Objects

When it comes time to convert PowerShell objects back to JSON for data transfer, this is where ConvertTo-Json. This cmdlet turns the object into a JSON string, ready for use.

Example:

$process = Get-Process | Select-Object -First 1
$json = $process | ConvertTo-Json
Write-Host $json

This code produces JSON representing information about the first process running on your system.

PS:

To process large JSON files, you should use stream processing to avoid loading the entire file into memory.

XML

Parsing XML in PowerShell

The first thing you need to know about working with XML in PowerShell is the syntax [xml]which turns XML data into objects that you can easily work with.

Example:

[xml]$xmlData = Get-Content -Path "C:\config.xml"
Write-Host $xmlData.Configuration.AppSettings.Setting[0].Key

Here we are reading the XML file using Get-Content and convert it to an XML object using [xml]. The elements can then be accessed in the same way as the properties of PowerShell objects.

XPath for working with large XML files:

When it comes to large XML files, it is worth using more powerful data fetching tools. XPATH is good in this case. PowerShell supports XPath via cmdlet Select-Xmlwhich allows you to query XML documents.

Example:

$xmlFile = "C:\config.xml"
$xpathQuery = "//Setting[@Key='AppMode']"
$result = Select-Xml -Path $xmlFile -XPath $xpathQuery

# Доступ к результату
Write-Host $result.Node.InnerText

Here we perform an XPath query to find the element with the attribute Keyequal AppMode.

XML Data Manipulation

Now it's time to talk about how to change this data. In PowerShell, adding, modifying, and deleting XML elements is a simple task. There are methods for this CreateElement, SetAttributeAnd AppendChild.

Example of adding a new element:

# Создаем новый элемент
$newElement = $xmlData.CreateElement("NewSetting")
$newElement.SetAttribute("Key", "NewFeature")
$newElement.InnerText = "Enabled"

# Добавляем его в существующую структуру
$xmlData.Configuration.AppSettings.AppendChild($newElement)

# Сохраняем изменения
$xmlData.Save("C:\config.xml")

We created a new element with an attribute and a value and then added it to the settings section. Manipulating XML objects through methods such as CreateElement, SetAttributeAnd AppendChild.

Changing an existing element:

$xmlData.Configuration.AppSettings.Setting[0].InnerText = "Production"
$xmlData.Save("C:\config.xml")

Simply assigning a new value to an existing element changes its content. After the change, do not forget to save the file using the method Save.

Validating XML Data Using XSD

Validating XML against the XSD schema helps ensure that the data conforms to the required format.

To validate XML, you first need to load the XSD schema, then apply it to the XML data.

# Загрузка XML и XSD
[xml]$xmlData = Get-Content -Path "C:\config.xml"
$schemaSet = New-Object System.Xml.Schema.XmlSchemaSet
$schemaSet.Add("", "C:\schema.xsd")

# Настройка валидации
$xmlData.Schemas.Add($schemaSet)
$xmlData.Validate({
    param ($sender, $e)
    Write-Host "Ошибка валидации: $($e.Message)"
})

This code loads the XSD schema and validates the XML data. If the schema does not match, an error is displayed.

CSV

Import CSV in PowerShell

Let's start with the simplest thing – CSV import. PowerShell uses a cmdlet for this Import-Csvwhich automatically converts CSV strings into PowerShell objects. Each column of the CSV file becomes a property of the object.

CSV import example:

$employees = Import-Csv -Path "C:\data\employees.csv"

After this command Import-Csv converts each line of a CSV file into an object PSCustomObjectwhere the file headers become property names. Now you can access the data as PowerShell objects:

$employees[0].FirstName

Working with files without headers:

It happens that the CSV file does not contain headers. In this case, PowerShell will create the headers automatically (Column1, Column2, etc.), but this is rarely convenient. To set your own headers, you can use the option -Header:

$employees = Import-Csv -Path "C:\data\employees.csv" -Header "FirstName", "LastName", "Department"

Separator settings:

By default, PowerShell expects the CSV delimiter to be a comma. But commas are not always used in CSV – there are also tabs, semicolons and other symbols. There is a parameter to specify a different separator -Delimiter:

$employees = Import-Csv -Path "C:\data\employees.tsv" -Delimiter "`t"  # для табуляции

CSV data manipulation

Cmdlet Where-Object allows you to filter CSV rows based on conditions:

$itEmployees = $employees | Where-Object { $_.Department -eq "IT" }

Here we have filtered all employees from the IT department. Rows can be filtered by any object property using any logical operators.

Data sorting is done using Sort-Object.

$sortedEmployees = $employees | Sort-Object LastName

PowerShell allows you to change data. For example, if you want to change the department of all employees with the last name “Smith”:

$employees | ForEach-Object {
    if ($_.LastName -eq "Smith") {
        $_.Department = "HR"
    }
}

This approach allows you to flexibly modify data directly in memory, which is especially useful when processing large CSV files.

Export data to CSV

When all the changes have been made, it becomes necessary to save the data in CSV. The cmdlet is used for this Export-Csvwhich converts the objects back to CSV strings and saves them to a file.

$employees | Export-Csv -Path "C:\data\updated_employees.csv" -NoTypeInformation

Parameter -NoTypeInformation prevents adding a metadata line to the beginning of the file, which in most cases is unnecessary.

Some nuances

PowerShell uses UTF-16 encoding for CSV by default. If a different encoding is required (for example, UTF-8 for compatibility), you can specify it explicitly:

$employees | Export-Csv -Path "C:\data\employees_utf8.csv" -NoTypeInformation -Encoding UTF8

To work with huge CSV files, you should avoid loading the entire file into memory by using line-by-line processing. This can be implemented through stream methods like Get-Content in combination with ConvertFrom-Csv.

In conclusion, I recommend attending open lessons on Windows administration: