PowerShell Succinctly^®
by Rui Machado

CHAPTER 6

Structured Files

Manipulating XML Files

Import XML from File

To import an XML structure from a previously built XML file, you can use the Import-Clixml command if your XML file was created by an Export-CliXml command. CLIXML in PowerShell is used to store object and its representation object to a file.

To manipulate your own XML structures, you should use Get-Content command, casting the object to XML. In this example, because my XML file was created outside PowerShell, I will use the Get-Content command.

$xmlFile = [xml](Get-Content -Path "C:\Users\rui.machado\Desktop\X.xml")

#Creates a DOM Object available

$xmlFile.PersonList | %{

}

Load XML File from String

PowerShell allows you to easily build a DOM object of an XML structure using the .NET object System.Xml.XmlDocument and invoking the LoadXml($xmlString), building a DOM object and allowing you to iterate in your XML tree structure. Now to try it, build a script to load an XML file from a string. The following code block shows the XML string:

<Address>Street A</Address>

</Person>

<Name>Peter</Name>

<Address>Street B</Address>

</Person>

<Address>Street C</Address>

</Person>

</PersonList>

Using the XML file you are going to work with, create a routine to process the requirements.

$xmlString = @"

<Address>Street A</Address>

</Person>

<Name>Peter</Name>

<Address>Street B</Address>

</Person>

<Address>Street C</Address>

</Person>

</PersonList>

$xmlDoc = new-object System.Xml.XmlDocument

$xmlDoc.LoadXml($xmlString)

$xmlDoc.PersonList | %{

write $_.Person.Name

}

Export XML to File

To export a data set to an XML file, you have two options. First, you can use the Export-CliXml command. However, this is a very specialized XML format defined by PowerShell that creates an XML-based representation of an object or objects and stores it in a file. You can then use the Import-CLIXML command to recreate the saved object based on the contents of that file. The second option is to export a structure using the .NET System.Xml.XmlDocument object, which allows you to save it to the file system. You can also pipe the output to the Out-File command, which gives you more control over how the file is created. This second approach creates XML files that are more easily consumed outside of PowerShell scripts.

#The structure to export.

$xml = @"

<Car>

<Brand>Ferrari</Brand>

</Car>

<Car>

<Brand>Porsche</Brand>

</Car>

</CarsList>

#Create a new XML document.

$xmlDoc = New-Object System.Xml.XmlDocument

#Load the xml structure.

$xmlDoc.LoadXml($xml)

#Define the file path.

$pathToFile="c:\temp\FileXML.xml"

#Save the structure to a file.

$xmlDoc.Save($pathToFile)

In the previous example, I used the .NET object System.Xml.XmlDocument to load and save the XML file to file system; however, you can use the out file command as well, as you can see in the following example.

#The structure to export.

$xml = @"

<Car>

<Brand>Ferrari</Brand>

</Car>

<Car>

<Brand>Porsche</Brand>

</Car>

</CarsList>

#Define the file path.

$pathToFile="c:\temp\FileXML2.xml"

#Export the file.

$xml | Out-File $pathToFile

Both of these previous scripts will result in the same XML file. The difference is just in the amount of code created and cleanliness of it, which in the second case is much better. The resulting structure is shown in the following sample.

<Car>

<Brand>Ferrari</Brand>

</Car>

<Car>

<Brand>Porsche</Brand>

</Car>

</CarsList>

Manipulating CSV Files

Import CSV from File

Importing CSV files in PowerShell might be important for projects like a technology migration in which its records will be inserted in a SQL Server database. In the following code block, we have a small example of a CSV file, which in this case will be imported to PowerShell as a PSObject, allowing you to iterate it as you wish.

Rui;24;Portugal

Tony;45;USA

Thiago;12;Brazil

Anna;56;Germany

To import a CSV file, use the Import-CSV command, having as parameters the path to the CSV file, the file delimiter, and the header, which identifies every column in the structured file. If your file has headers, you don´t need to use this parameter.

#Path to CSV file.

$path="c:\temp\ListaUsers.csv"

#Import your CVS file defining the file path, CSV delimiter and the header.

$csv=Import-CSV -Path $path -Delimiter ";" -Header "Name","Age","Nationality"

#Storing the CSV to a variable allows you to manipulate its content.

$csv | %{

$_.Name

$_.Age

$_.Nationality

}

Export CSV to File

Exporting to CSV in PowerShell is a little bit more complicated than any other export for one simple reason: PowerShell’s Export-Csv command works with PSObjects, so you need to convert your structures to one of this kind before exporting it to CSV. In the following code block, I will be showing you how to create a PSObject from an array, and then export it to CSV. To export, I will be using the Export-Csv command, which converts objects into a series of comma-separated (CSV) strings and saves the strings into a file.

PSObject

#Path to export.

$path = "c:\temp\testeExport.csv"

#Structure to export.

$csv=("Rui",24,"Portugal"),("Helder",29,"China"),("Vanessa",24,"Brasil")

#Initialize the array that will be exported.

$allRecords = @()

$csv | %{

#Export-CSV separates columns from PSObject members, so we need to create one.

$customCSV = New-Object PSObject

#Add the name member to the PSObject

$customCSV | Add-Member -MemberType NoteProperty -Value $_[0] -Name "Name"

#Add the name member to the PSObject

$customCSV | Add-Member -MemberType NoteProperty -Value $_[1] -Name "Age"

#Add the name member to the PSObject

$customCSV | Add-Member -MemberType NoteProperty -Value $_[2] -Name "Nationality"

#Add the PSObject to the array that stores every object to export.

$allRecords+=$customCSV

}

#Export as CSV.

$allRecords | Export-Csv -Path $path -NoTypeInformation -Delimiter ";"

Load CSV and Send Email

Rui,[email protected],Hello

Machado,[email protected],Hi!

With the previous common separated values file, we´ll use its values and send an email to every recipient defined.

Routine that matter CSV file. As the CSV file has columns use the

Header-parameter to set the column headers. We also need to define the delimiter of the CSV,

which in this case is a comma, but can be any other.

$userList= Import-Csv -Path "C:\Users\rui.machado\Desktop\Users.csv"`

-Header "Name", "Email", "Subject"`

-Delimiter ","`

#SMTP Server

$smtpServer="smtp.gmail.com"

#Create a new .NET SMTP client onject (587 is the default smtp port)

$smtpClient = new-object Net.Mail.SmtpClient($smtpServer,587)

#Create a new .NET Credential object.

$credentials = New-Object Net.NetworkCredential

#Set your credentials username.

$credentials.UserName="[email protected]"

#Set your credentials password.

$credentials.Password="rpsrm@89"

#Set the smtp client credential with the one created previously.

$smtpClient.Credentials=$credentials

#Create a new .NET mail message object.

$email = new-object Net.Mail.MailMessage

#Initialize the from field.

$from="[email protected]"

#Initialize the mail message body.

$message="Este é um email do powershellpt. Seja bem vindo "

#Set the from field in the mail message.

$email.From = $from

#Set the Reply to field in the mail message.

$email.ReplyTo = $from

#Set the body field in the mail message.

$email.body = $message

# foreach user in the CSV file, send the email.

$userList | %{

#Just send to users with a defined email.

if($_.Email -ne $null)

{

#Set recipient name.

$nome=$_.Name

#Add recipient email to the Mail message object.

$email.To.Add($_.Email)

#Set the subject.

$email.subject = $_.Subject

#This is a Google smtp server requirement.

$smtpClient.EnableSsl=$true

#Send the email.

$smtpClient.Send($email)

}

Manipulating TXT Files

Import TXT from File

PowerShell does not have an explicit import command. Instead, you need to use the Get-Content command to retrieve the text file content, which means that you only need to specify as a parameter the file path. After you invoke the command, PowerShell will instantiate a System.String object containing the text file content.

#Path to the txt file.

$path = "C:\temp\test.txt"

#Get content from the txt file.

$content = Get-Content -Path $path

#Print content.

$content

Export TXT to File

Exporting an object to a text file is provided in PowerShell through its Out-File command, which sends a specific output to a file as a string. This command also allows you to define as a parameter the encoding of the file you want to create.

#Path to the txt file.

$path = "C:\temp\newTest.txt"

EXAMPLE 2

Create txt file and write to it.

"Write to Txt" | Out-File $path

EXAMPLE 2

Create txt file and write to it, setting the encoding to ACII.

"Write to Txt" | Out-File $path -Encoding ASCII

EXAMPLE 3

Create txt file and write to it, setting the encoding to ACII and the width of 5,

which means that only the first 5 characters will be exported.

"Write to Txt" | Out-File $path -Encoding ASCII -Width 5

Using XSL to Transform XML Files

Sometimes you might need to transform XML files in order to respect a predefined XML schema, for instance a SQL Server database table. Imagine that you wish to query an SQL Server database of a client, return the dataset as a XML document, transform it using an XSL script, and then insert it in another SQL database table from another client. This is a very powerful tool for integration projects in which source and destination structures are not equal. In the following code block, I will you how you can use .NET System.XML.XSL.XSLCompiledTransform object to apply a XSL script to an XML file.

To input an XML file, use the following one, which is a list of people. The goal is to transform the three elements of a person (Name, Age, Address) into a comma-separated values element (Data).

<Address>Street A</Address>

</Person>

<Name>Peter</Name>

<Address>Street B</Address>

</Person>

<Address>Street C</Address>

</Person>

</PersonList>

The XSL file you are using is the following, which makes the transformation defined previously:

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<xsl:for-each select="PersonList/Person">

<Data>

<xsl:value-of select="Name" />;

<xsl:value-of select="Age" />;

<xsl:value-of select="Address" />

</Data>

</Person>

</xsl:for-each>

</PersonList>

</xsl:template>

</xsl:stylesheet>

To achieve a transformation with an XSL file in PowerShell, use the System.XML.XSL.XSLCompiledTransform as I have previously mentioned, particularly two of its methods, the Load() method.

The first one will load the XSL script from its path, and the Transform() method applies the transformation script to an input XML file and returns transformed structure to an output XML. Both of these XML files are defined by their path as parameters of this method.

#Path to XSL stylesheet

$xslStylesheetPath = "C:\temp\XsltFile.xsl"

#Path to XML Input file

$xmlInputPath = "C:\temp\InputXml.xml"

#Path to XML Ouput file. The result of the transformation.

$xmlOutputPath = "C:\temp\OutputXml.xml"

#Instantiate the XslCompiledTransform .NET object.

$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;

#Load the XSL script.

$xslt.Load($xslStylesheetPath);

#Applies the transformation to the input XML and return the result as an ouput XML.

$xslt.Transform($xmlInputPath, $xmlOutputPath);

As a result, the transformation resulted in the following XML structure, with the person elements transformed into one single comma-separated values element.

<?xml version="1.0" encoding="utf-8"?>

<Data>Rui;24;Street A</Data>

</Person>

<Data>Peter;45;Street B</Data>

</Person>

<Data>Mary;10;Street C</Data>

</Person>

</PersonList>

Put your data to work with Bold BI

Shared infrastructure for efficient resource management
Self-service analytics for easy data exploration
Enhanced security with row-level security and SSO
Customizable dashboards to meet your specific needs

Get Your Free Trial Now

Structured Files

Manipulating XML Files

Import XML from File

Load XML File from String

Export XML to File

Manipulating CSV Files

Import CSV from File

Export CSV to File

Load CSV and Send Email

Manipulating TXT Files

Import TXT from File

Export TXT to File

Using XSL to Transform XML Files

DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.