Mastering Data Export and Import in PowerShell: A Guide to Text, JSON, CSV, and XML

Jos Fissering

📆

🏷️

,

Index

Introduction

PowerShell offers various ways to export and display data. The most commonly used output data types are text, JSON, CSV, and XML. In this post, we will discuss these formats and how to use them in PowerShell.

To export output to a file, we use the Out-File cmdlet. For CSV and XML types, there are also dedicated cmdlets that you can use instead of the Out-File cmdlet.

To import the exported output, we then use Get-Content. Here too, you can choose to use dedicated cmdlets for the CSV and XML data types.

– Back to top –

TXT (Text)

Text is the simplest and most direct output format. It is also the default format. To generate this output, you can use Write-Output or simply call the text or variable.

Although it seems that Write-Host does the same, its output only goes to the console. Therefore, it is not possible to store this output in a variable or write it to a file using Out-File. One way to write this output to a file is by using transcripts. If you start a transcript with Start-Transcript, all console output will be written to the file. see also: Logging – Using transcripts

Export

To export text to a file, you can pipe the output directly to Out-File.

Write-Output "Hello, world!" | Out-File -FilePath "output.txt"
$env:Computername | Out-File -FilePath "output.txt" -Append

Import

To import the data, we retrieve it using Get-Content and store it in a variable. Since it is plain text, no further steps are needed.

$ImportedDataRaw = Get-Content -Path "output.txt"

The disadvantage of this output type is that it is difficult to manipulate the data. The imported text is an array of strings, so we can read specific lines or individual lines from the variable.

– Back to top –

JSON (JavaScript Object Notation)

JSON is a lightweight data interchange format that is easily readable by both humans and machines. JSON uses two structures:

  • Collection of name-value pairs. Think of Hashtable and PSCustomObject.
  • Collection of values. Think of Array and List<T>.

These are universal data structures and you will encounter them in many other (modern) programming or scripting languages. This makes this data type very suitable for exchanging data between different languages.

Export

To export data to the JSON format, we can use the built-in cmdlet ConvertTo-Json. This cmdlet converts the input to the JSON format. We can then write the output to a file using Out-File. Although you can write this to a .txt file, it is recommended to use the .json extension. This allows us to quickly determine the content based on the extension when we read the file again at a later stage. Additionally, using the correct extension makes it easier for other applications to read and process the data.

@{
    User = @{
        FirstName = "John"
        SurName = "Doe"
    }
    PreferedServer = "Server01"
    WorkDays = @(
        "Monday", 
        "Tuesday", 
        "Friday"
    )
} | ConvertTo-Json | Out-File -FilePath "Output.json"

If we then open the file in a text editor, we will see the data in the following notation.

{
  "WorkDays": [
    "Monday",
    "Tuesday",
    "Friday"
  ],
  "PreferedServer": "Server01",
  "User": {
    "SurName": "Doe",
    "FirstName": "John"
  }
}

If it is necessary to maintain the order, you can add the typecasting [ordered] for the hashtable in this example.

ConvertTo-Json also has the -Compress switch. This writes the JSON on one line. This can be useful if you need to write it in a text field.

@{
    User = @{
        FirstName = "John"
        SurName = "Doe"
    }
    PreferedServer = "Server01"
    WorkDays = @(
        "Monday", 
        "Tuesday", 
        "Friday"
    )
} | ConvertTo-Json -Compress

## Output:
{"WorkDays":["Monday","Tuesday","Friday"],"PreferedServer":"Server01","User":{"SurName":"Doe","FirstName":"John"}}

Import

For importing, we will use Get-Content just like with a text file. Then, using ConvertFrom-Json, we can convert the data back to an object. Note that our input was a hashtable, but after conversion, it becomes a PSCustomObject. Since these two data types are very similar, this will not cause problems for most people, but if you want to process the object script-wise, you will need different methods.

In PowerShell 6.0, the option was added to convert this to a hashtable by adding the -AsHashtable switch to ConvertFrom-Json. And from version 7.3, it becomes an OrderedHashtable, preserving the sorting.

$ImportedDataRaw = Get-Content -Path "Output.json" 
$ImportedDataConverted = $ImportedDataRaw | ConvertFrom-Json

$ImportedDataConverted.PreferedServer
Server01

As shown in the example above, we can easily continue working with the data. All actions available for a PSCustomObject or, if we used -AsHashtable, for a hashtable, can be used again after conversion.

– Back to top –

CSV (Comma Separated Values)

CSV is another widely used format. Personally, I am most familiar with this type because it is easy to use in combination with Microsoft Excel. This type is characterized by all values being separated by a delimiter. Originally, a comma (,) was used for this, but many other delimiters can be used.

This type is suitable for use with data in a database-like style. In PowerShell, this is an Array or List filled with PSCustomObject or hashtable objects.

Export

As mentioned in the introduction, we have several options for writing to CSV format:

  • ConvertTo-Csv
  • Export-Csv

ConvertTo-Csv

The first option follows the same method as text and JSON and uses Out-File. Although you can write this to a .txt file, it is recommended to use the .csv extension. Again, this allows us to quickly determine the content and enables external applications to process the file.

@(
  @{
    Status = "Running"
    Name = "App_01"
    DisplayName = "Application 01"
  },
  @{
    Status = "Running"
    Name = "App_02"
    DisplayName = "Application 02"
  },
  @{
    Status = "Stopped"
    Name = "App_03"
    DisplayName = "Application 03"
  }
) | ConvertTo-Csv | Out-File -FilePath "Output.csv"

If we then open the file in a text editor, we will see the data in the following notation.

"Status","DisplayName","Name"
"Running","Application 01","App_01"
"Running","Application 02","App_02"
"Stopped","Application 03","App_03"

If it is necessary to change the delimiter (default: ,), you can do so with -Delimiter. You can specify the desired delimiter as input.

Export-Csv

For the second option, we will write the data directly to a .csv file.

@(
  @{
    Status = "Running"
    Name = "App_01"
    DisplayName = "Application 01"
  },
  @{
    Status = "Running"
    Name = "App_02"
    DisplayName = "Application 02"
  },
  @{
    Status = "Stopped"
    Name = "App_03"
    DisplayName = "Application 03"
  }
) | Export-Csv -Path "Output.csv"

If we then open the file in a text editor, we will see the data in the following notation.

"Status","DisplayName","Name"
"Running","Application 01","App_01"
"Running","Application 02","App_02"
"Stopped","Application 03","App_03"

If it is necessary to change the delimiter (default: ,), you can do so with -Delimiter. You can specify the desired delimiter as input.

Import

For importing, we can choose between two options:

  • ConvertFrom-Csv
  • Import-Csv

If a different delimiter than the default , was chosen, the -Delimiter parameter can be used with both the ConvertFrom-Csv and Import-Csv cmdlets to split with the correct delimiter.

ConvertFrom-Csv

As we have seen with the previous data types, we first retrieve the contents of the file and then convert it.

$ImportedDataRaw = Get-Content -Path "Output.csv" 
$ImportedDataConverted = $ImportedDataRaw | ConvertFrom-Csv

$ImportedDataConverted[1]
Status  DisplayName    Name
------  -----------    ----
Running Application 02 App_02

Import-Csv

If we use Import-Csv, we can use the data directly.

$ImportedDataRaw = Import-Csv -Path "Output.csv" -Delimiter ","

$ImportedDataRaw[2]
Status  DisplayName    Name
------  -----------    ----
Stopped Application 03 App_03
– Back to top –

XML (eXtensible Markup Language)

Extensible Markup Language (XML) is a format that allows you to represent objects and other data in plain text. Like JSON, it is readable by both machines and humans. Although I personally find it a lot more difficult. It is widely used in web applications, but also within other applications. XML requires a strict formatting method.

XML languages use elements and attributes to structure data. The XML specification defines the syntax of elements, attributes, and other structures that can occur in XML files. However, the XML specification does not define names for these elements and attributes, as this choice depends on the purpose of the XML file.

Export

For the XML format, we have multiple options. Here we have three options, with both *-CliXml options giving the same output, but ConvertTo-CliXml requires an additional Out-File.

  • ConvertTo-CliXml
  • Export-Clixml
  • ConvertTo-Xml

ConvertTo-CliXml

The ConvertTo-CliXml cmdlet works the same as the other ConvertTo-* cmdlets we have discussed.

@(
  @{
    Status = "Running"
    Name = "App_01"
    DisplayName = "Application 01"
  },
  @{
    Status = "Running"
    Name = "App_02"
    DisplayName = "Application 02"
  },
  @{
    Status = "Stopped"
    Name = "App_03"
    DisplayName = "Application 03"
  }
) | ConvertTo-CliXml | Out-File -FilePath "Output.xml"

For readability, I have omitted a portion of the output below, but if we open the file in a text editor, we will see the following:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Collections.Hashtable</T>
      <T>System.Object</T>
    </TN>
    <DCT>
      <En>
        <S N="Key">Status</S>
        <S N="Value">Running</S>
      </En>
      <En>
        <S N="Key">DisplayName</S>
        <S N="Value">Application 01</S>
      </En>
      <En>
        <S N="Key">Name</S>
        <S N="Value">App_01</S>
      </En>
    </DCT>
  </Obj>
  <!-- Other 2 objects -->
</Objs>

Export-Clixml

@(
  @{
    Status = "Running"
    Name = "App_01"
    DisplayName = "Application 01"
  },
  @{
    Status = "Running"
    Name = "App_02"
    DisplayName = "Application 02"
  },
  @{
    Status = "Stopped"
    Name = "App_03"
    DisplayName = "Application 03"
  }
) | Export-Clixml -Path output.xml

This gives the same output as ConvertTo-CliXml.

ConvertTo-Xml

As with all ConvertTo-* cmdlets we have discussed, we need to use Out-File to save this to a file. We choose the .xml extension for the same reasons as previously mentioned. This cmdlet requires an additional parameter to write it to a file. We need to add -As String to make it a full-fledged XML.

@(
  @{
    Status = "Running"
    Name = "App_01"
    DisplayName = "Application 01"
  },
  @{
    Status = "Running"
    Name = "App_02"
    DisplayName = "Application 02"
  },
  @{
    Status = "Stopped"
    Name = "App_03"
    DisplayName = "Application 03"
  }
) | ConvertTo-Xml -As String | Out-File -FilePath "Output.xml"

If we then open the file in a text editor, we will see the data in the following notation.

<?xml version="1.0" encoding="utf-8"?>
<Objects>
  <Object Type="System.Collections.Hashtable">
    <Property Name="Key" Type="System.String">Status</Property>
    <Property Name="Value" Type="System.String">Running</Property>
    <Property Name="Key" Type="System.String">DisplayName</Property>
    <Property Name="Value" Type="System.String">Application 01</Property>
    <Property Name="Key" Type="System.String">Name</Property>
    <Property Name="Value" Type="System.String">App_01</Property>
  </Object>
  <Object Type="System.Collections.Hashtable">
    <Property Name="Key" Type="System.String">Status</Property>
    <Property Name="Value" Type="System.String">Running</Property>
    <Property Name="Key" Type="System.String">DisplayName</Property>
    <Property Name="Value" Type="System.String">Application 02</Property>
    <Property Name="Key" Type="System.String">Name</Property>
    <Property Name="Value" Type="System.String">App_02</Property>
  </Object>
  <Object Type="System.Collections.Hashtable">
    <Property Name="Key" Type="System.String">Status</Property>
    <Property Name="Value" Type="System.String">Stopped</Property>
    <Property Name="Key" Type="System.String">DisplayName</Property>
    <Property Name="Value" Type="System.String">Application 03</Property>
    <Property Name="Key" Type="System.String">Name</Property>
    <Property Name="Value" Type="System.String">App_03</Property>
  </Object>
</Objects>

If we had not used -As String, we would have had the following output.

xml                            Objects
---                            -------
version="1.0" encoding="utf-8" Objects

This generates an XML object (XmlDocument) and is fine for working within your console session, but not usable for exporting to a file.

Import

If we want to import the XML data again, we encounter an exception. We have ConvertTo-Xml, but ConvertFrom-Xml does not exist. If we want to get the data converted in this way back into the console, we need to take a few extra steps.

If we used a *-CliXml option, we can choose from the following cmdlets.

  • ConvertFrom-CliXml
  • Import-Clixml

ConvertFrom-Clixml

As we have seen with the previous data types, we first retrieve the contents of the file and then convert it.

$ImportedDataRaw = Get-Content -Path "Output.xml" 
$ImportedDataConverted = $ImportedDataRaw | ConvertFrom-Clixml

$ImportedDataConverted[0]
Name                           Value
----                           -----
Status                         Running
Name                           App_01
DisplayName                    Application 01

Import-Clixml

If we use Import-Clixml, we can use the data directly.

$ImportedDataRaw = Import-Clixml -Path "Output.xml"

$ImportedDataRaw[2]
Name                           Value
----                           -----
Status                         Running
Name                           App_03
DisplayName                    Application 03

(Normal) XML Import

As mentioned above, importing (normal) XML data is a bit more difficult. First, we need to read the data from the file. Then we indicate that it is XML data.

$ImportedDataRaw = Get-Content -Path "Output.xml" 
[xml]$ImportedData = $ImportedDataRaw

Now we have an XmlDocument just like when we did the conversion with ConvertTo-Xml without -As String.

IsPublic IsSerial Name        BaseType
-------- -------- ----        --------
True     False    XmlDocument System.Object
– Back to top –

Advantages and Disadvantages

We have seen several ways to export data from our console to files. So far, we have mainly focused on the technical aspect, but below I have briefly summarized the advantages and disadvantages of each option.

Text (TXT):

  • Files are easy to edit with a text editor.
  • Almost every system and program can open and process text files.
  • No extra characters are added, keeping the file size small.
  • When exporting, you lose your data types (from the source).

JSON (JavaScript Object Notation):

  • Once you understand the notation, it is easy to read.
  • It is a widely used format for data exchange between web servers and clients.
  • Supports nested structures, making it suitable for complex data.
  • Ideal for preserving data types.

CSV (Comma-Separated Values):

  • Easy to create and edit with spreadsheet programs like Microsoft Excel.
  • Supported by many databases and analysis programs.
  • Efficient for storing tabular data and can be processed quickly.

XML (eXtensible Markup Language):

  • Highly flexible and can be extended to meet specific needs.
  • Suitable for storing hierarchical and structured data.
  • Many systems and applications support XML for data exchange and configuration.
  • Importing XML in PowerShell yields different results compared to text, JSON, or CSV. Unless you take extra steps, you cannot convert the data back to, for example, hashtables and arrays.
– Back to top –

Conclusion

Which format you use depends on personal preference. My personal preference is JSON. I have no problems reading the JSON notation and can also manually convert it to a PowerShell notation. Additionally, I like that I can keep the types intact with JSON. This makes it easy to provide a text-based payload and quickly convert it to hashtables and/or arrays.

– Back to top –

Leave a Reply

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

Latest Posts

Beginner Deep Dive Fundamentals Intermediate PowerShell Quick Learnings Troubleshooting

Archive