How To Import Or Export Data Using PowerShell

In today’s article, I will cover a very common need to import/read data from text file or CSV (comma separated values) file, and export data into CSV file using PowerShell.

I will also show you one of my own CmdLets (Get-ComputerInfo CmdLet) that do the job and explain to you how I have implemented text and CSV files in my own projects that might be useful for your projects as well.

Here is the example code that we will use:

$computers = Get-Content -Path 'C:\Temp\Computers.txt'

$objects = @()


foreach ($computer in $computers){

    $servers = Import-Csv -Path 'C:\Temp\Servers.csv' | Where-Object -FilterScript { $_.hostname -eq "$computer" } | Select-Object -Property hostname, ipaddress, logicalname, environment
    $objects += $servers 

}

$objects | Export-Csv -ErrorAction Stop -path 'C:\Temp\ServersCopy.csv' -noTypeInformation -Delimiter ";" -Force

$objects | Out-GridView

Text And CSV Files Used

For this example, I have used two files and both are saved in C:\Temp folder:

• Computers.txt – a text file
• Servers.csv – a CSV file

The text file contains a list of servers as you can see on the screenshot.

The CSV file contains the same list of servers with additional data that further describe each server:

• hostname – the name of the server (same as in the text file).
• ipaddress – IP address of the server.
• logicalname – the logical name of the server (APP – Application, WEB – Web, INT – Integration, FILE – File, ARCHIVE – Archive, SCAN – Scan Server, etc).
• environment – Environment which server belongs to (PROD – Production, TEST – Test, COURSE- Course, etc).

Import Data From Text File Into PowerShell

To import data from a text file into PowerShell we use Get-Content CmdLet. In the example above we read the text file and save the result in variable $computers that will be used later.

The syntax is very simple we call Get-Content and provide value to the Path parameter where our text file has been located.

$computers = Get-Content -Path 'C:\Temp\Computers.txt'

Here is the result of running only Get-Content CmdLet, the CmdLet simple reads the content of the text file.

Let’s quickly look at what the data type of the result is and what the properties and methods that we can use to manipulate the data are.

$computers | Get-Member

As you can see from the screenshot below (open in new Tab if the screen is too small for you), as a result of running Get-Content CmdLet, we got strings as expected and we have all the properties and methods that come with String data type.

Export/Write Data From PowerShell Into Text File

In order to export/write data from PowerShell into the text file, we use Out-File CmdLet usually with the Append parameter together.

INFO: I have written an article that has a great implementation of Out-File CmdLet so please read the section Write-ErrorLog CmdLet Explained (PROCESS block code) and if you want to learn more about PowerShell Error Handling and writing errors in an external log file please read the whole article How To Log PowerShell Errors And Much More

Here is the screenshot from that article and Log file created using Out-File CmdLet.

Import Data From CSV (Comma Separated Values) File Into PowerShell

We import data from CSV file into PowerShell using Import-Csv CmdLet like in the following example:

Import-Csv -Path 'C:\Temp\Servers.csv' | Select-Object -Property hostname, ipaddress, logicalname, environment

As a result, we get the content from the CSV file which is the list of servers with additional info for each of the servers (ipaddress, logicalname, environment).

In our example for fun, I have read the CSV file for each server using the $computer variable provided from the text file as input and saved the result first in $servers variable and then into $objects array variable.

$computers = Get-Content -Path 'C:\Temp\Computers.txt'

$objects = @()


foreach ($computer in $computers){

    $servers = Import-Csv -Path 'C:\Temp\Servers.csv' | Where-Object -FilterScript { $_.hostname -eq "$computer" } | Select-Object -Property hostname, ipaddress, logicalname, environment
    $objects += $servers 

}

Let’s quickly look at what the data type of the result is and what the properties and methods that we can use to manipulate the data are.

$servers | Get-Member

As you can see from the screenshot below, as a result of running Import-Csv CmdLet, we got PSCustomObjects with methods and properties. Notice that as properties we have all the additional info from CSV file (environment, hostname, ipaddress, logicalname).

Export/Write Data From PowerShell Into CSV (Comma Separated Values) File

To Export the data we use Export-Csv CmdLet like in this example where we read the data from CSV file and pipeline to Export-Csv CmdLet to create a copy of the same file.

Import-Csv -Path 'C:\Temp\Servers.csv' | Export-Csv -ErrorAction Stop -path 'C:\Temp\ServersCopy.csv' -noTypeInformation -Delimiter ";" -Force

Here is the result of the export in the Excel sheet.

INFO: If you want to learn how to export data into Excel sheet and format as nice table please read the article How To Create, Write And Save An Excel File Using PowerShell

Here is the Excel report example created with my own Save-ToExcel CmdLet.

In the example, at the beginning of this article, we have used the array in variable $objects to send down the pipeline, anyway, the outcome is the same and copy CSV file has been created.

$objects | Export-Csv -ErrorAction Stop -path 'C:\Temp\ServersCopy.csv' -noTypeInformation -Delimiter ";" -Force

INFO: PowerShell Pipelining is a very important concept and I highly recommend you to read the article written on the subject. PowerShell Pipeline Concept Explained With Awesome Examples. Here I have shown in many examples the real power of PowerShell using the Pipelining.

What Are The Other Export/Import Possibilities

Besides text file and CSV file we can import/export XML file using Import-Clixml and Export-Clixml CmdLets.

INFO: I have written CmdLets that use Export-Clixml and Import-Clixml CmdLets and I will write separate articles abouth them so bookmark my blog and come back to read about them soon.

UPDATE: To Export XML files using PowerShell please read the article: “How To Export XML File In PowerShell“.

UPDATE: To Import XML files using PowerShell please read the article: “How To Import XML File In PowerShell“.

How Did I Implement Text And CSV Files In My Projects

I have written my own Get-ComputerInfo CmdLet that implements Import-Csv CmdLet to read the data from the CSV file. Get-ComputerInfo CmdLet is part of the Efficiency Booster PowerShell Project. This project is a library of CmdLets that helps us to accomplish our everyday IT tasks more efficiently.

If you want to follow me along please download the source code for CmdLets from here.

Here is the example syntax for Get-ComputerInfo CmdLet:

Get-ComputerInfo -computername "localhost" -client "OK" -solution "FIN"

CmdLet will read the date from the CSV file about the localhost computer and return as result additional info about it from the CSV file.

But before we dive into the code let me explain the logic of implementing text and CSV files.

CSV Files

I use the CSV file as a repository (database) for the additional info about the all servers that we want to maintain using the CmdLets. The name of the CSV file is important in my projects and I use the following pattern when naming the file.

Two Letters for Client + Two/Three Letters for Solution + fixed value “servers”.

For example, OKFINservers.csv file is for a client with OK initials, FIN stands for a financial solution (I use HR for Human Resource solution) and the “servers” part is fixed.

My IT experience comes from ERP solutions so I am used to Financial and HR solutions but you can use other names for solutions that are more appropriate for your field of expertise.

The content of the CSV file is very similar to the example I have shown at the begging of this article.

Text Files

I have separate text files that contain only the list of server names and they are a subset of CSV file. These text files are used as inputs to decide which servers will be processed with certain CmdLet. This gives me the possibility to segment all the servers for example by the environment (Test, Course, Acceptance, Production, etc.) so I have text files for only Test servers or only Production servers or Course servers or all the servers in the environment.

I do not have a strict naming convention for these text files but in order to be organized, I usually name them following similar naming convention as for CSV file. For example,

• OKFINTestservers.txt for Test environment servers,
• OKFINProdservers.txt for Production environment servers,
• OKFINCourseservers.txt for Course environment servers,
• OKFINservers.txt for all servers and all environments, etc.

Basically, I segment the CSV repository of all servers with the lists in text files based on the need.

The content of text files is very similar to the content of the example from the beginning of this article.

Location Of Text And CSV Files

The location of CSV and text files is also important for encapsulation and deployment reasons.

I like to organize my CmdLets into Modules and on top of that to have some encapsulation I put the text and CSV files in the Modules folder in their own separate 01servers folder as you can see on the screenshot.

This gives me the possibility to give just the name of the text file as an input parameter to my CmdLets and not the whole path especially if that path depends on the name of the user that runs the CmdLets.

INFO: If you want to learn more about PowerShell Modules and CmdLets please read the following articles How To Create A PowerShell Module (Different Approach) and How To Create A Custom PowerShell CmdLet (Step By Step)

Implementation Of The Concept

I will illustrate the implementation of the concept with an example.

For example, if I want to get CPU properties for all the test servers I would call Get-CPUInfo CmdLet and as input parameter pass text file name, for example, OKFINTestservers.txt then in the implementation of the CmdLet the text file is read with Get-Content CmdLet and for each server will be read additional information about the server using Get-ComputerInfo CmdLet (IP address, Environment, Logical Name). All the additional information read using Get-ComputerInfo CmdLet are implemented with Import-Csv CmdLet.

As illustration here is the call to Get-CPUInfo CmdLet with the result that returns.

Get-CPUInfo -filename "OKFINservers.txt" -errorlog -client "OK" -solution "FIN" | Out-GridView

Here are the code snippets of the implementation:

Step 1 – Read the text file in Get-CPUInfo CmdLet using Get-Content CmdLet. That will create the list of servers ($computers) that we want to process with our Get-CPUInfo CmdLet

BEGIN {

    if ( $PsCmdlet.ParameterSetName -eq "FileName") {

        if ( Test-Path -Path "$home\Documents\WindowsPowerShell\Modules\01servers\$filename" -PathType Leaf ) {
            Write-Verbose "Read content from file: $filename"
            $computers = Get-Content( "$home\Documents\WindowsPowerShell\Modules\01servers\$filename" )        
        } else {
            Write-Warning "This file path does NOT exist: $home\Documents\WindowsPowerShell\Modules\01servers\$filename"
            Write-Warning "Create file $filename in folder $home\Documents\WindowsPowerShell\Modules\01servers with list of server names."
            break;
        }
       
    }

}

Step 2 – Read additional info about each server from the text file (Environment, Logical Name, IP Address). Call to Get-ComputerInfo CmdLet from Get-CPUInfo CmdLet Process block.

PROCESS { 

    foreach ($computer in $computers ) {
        
        if ( $computer -eq 'localhost' ) {
            $computer = $env:COMPUTERNAME
            Write-Verbose "Replace localhost with real name of the server."
        }
        
        $computerinfo = Get-ComputerInfo -computername $computer -client $client -solution $solution
        $hostname = $computerinfo.hostname
        $env = $computerinfo.environment
        $logicalname = $computerinfo.logicalname
        $ip = $computerinfo.ipaddress
     }
}

Step 3 – Read Additional server info in Get-ComputerInfo CmdLet (PROCESS block) using Import-Csv CmdLet. Result of Get-ComputerInfo CmdLet is returned to Get-CPUInfo CmdLet.

PROCESS {               
        
        Write-Verbose "Import data for $client - $solution - $computername"
        $filenameprefix = "$client$solution"
        $constant = "servers.csv"
        $filename = "$filenameprefix$constant"
        $importfile = "$home\Documents\WindowsPowerShell\Modules\01servers\$filename"
        
        
        if ( ( Test-Path -Path $importfile -PathType "Leaf" ) ) {

            if ( $computername -eq 'localhost' ) {
                $computername = $env:COMPUTERNAME
                Write-Verbose "Replace localhost with real name of the server."
            }
        
            Import-Csv $importfile | Where-Object -FilterScript { $_.hostname -eq "$computername" } | Select-Object -Property hostname, ipaddress, logicalname, environment         
        
        }
}

Step 4 – Get-CPUInfo CmdLet further process server by server and returns the result. (Look the screenshot for the beginning of this section).

Get-ComputerInfo CmdLet Explained

Input Parameters

As input parameters we have:

• computername – the name of the server that we want to search for additional data in CSV file (environment, IP address, logical name).
• client – part of encapsulation and CSV file name (usually two letters abbreviation of your client)
• solution – part of encapsulation and CSV file name (I use FIN for Financial and HR for Human Resource)

Function Get-ComputerInfo {
[CmdletBinding()]
param (
    [Parameter(Mandatory=$true,
                ValueFromPipelineByPropertyName=$true, 
                HelpMessage="Computer name.")]
    [string]$computername,
    
    [Parameter(Mandatory=$true, 
                HelpMessage="Client for example OK = O client, BK = B client")]
    [string]$client,
     
    [Parameter(Mandatory=$true,
                HelpMessage="Solution, for example FIN = Financial, HR = Human resource")]
    [string]$solution 
)
}

INFO: To know more about PowerShell Parameters and Parameter Sets with some awesome examples please read the following articles How To Create Parameters In PowerShell Step By Step and How To Use Parameter Sets In PowerShell Functions.

BEGIN Block

In the BEGIN block we:

• Test the existence of 01server folder where CSV files are located
• if the 01server folder doesn’t exist then create one

BEGIN { 
        
        Write-Verbose "BEGIN section"

        $serverlistfolder = "$home\Documents\WindowsPowerShell\Modules\01servers"
        
        if  ( !( Test-Path -Path $serverlistfolder -PathType "Container" ) ) {
            
            Write-Output "Create server names lists folder in: $serverlistfolder"
            New-Item -Path $serverlistfolder -ItemType "Container" -ErrorAction Stop
        
        }

}

PROCESS Block

In the PROCESS block we:

• Test the CSV file existence.
• Replace the localhost input parameter value with the real name of the local machine.
• Import CSV file using Import-Csv CmdLet.
• Error Handle the errors that occur, for example, CSV file doesn’t exist.

PROCESS {               
        
        Write-Verbose "Import data for $client - $solution - $computername"
        $filenameprefix = "$client$solution"
        $constant = "servers.csv"
        $filename = "$filenameprefix$constant"
        $importfile = "$home\Documents\WindowsPowerShell\Modules\01servers\$filename"
        
        
        if ( ( Test-Path -Path $importfile -PathType "Leaf" ) ) {
        
            if ( $computername -eq 'localhost' ) {
                $computername = $env:COMPUTERNAME
                Write-Verbose "Replace localhost with real name of the server."
            }

            Import-Csv $importfile | Where-Object -FilterScript { $_.hostname -eq "$computername" } | Select-Object -Property hostname, ipaddress, logicalname, environment         
        
        } else {
        
            Write-Warning "Get-ComputerInfo CmdLet failed."
            Write-Warning "List of server names file $importfile does not exist."
            Write-Warning "Read Get-ComputerInfo CmdLet help to find out more info."
            
            $errormsg = "List of server names file $importfile does not exist."
            $exception = "Read Get-ComputerInfo CmdLet help to find out more info."
            
            $stacktrace = $_.ScriptStackTrace
            $failingline = "Test-Path -Path $importfile -PathType Leaf"
            $positionmsg = $_.InvocationInfo.PositionMessage
            $pscommandpath = $_.InvocationInfo.PSCommandPath
            $failinglinenumber = "103"
            $scriptname = "GetComputerInfo.ps1"

            Write-Verbose "Start writing to Error log."
            
            Write-ErrorLog -hostname "Get-ComputerInfo CmdLet was failing." -env "ALL" -logicalname "ALL" -errormsg $errormsg -exception $exception -scriptname $scriptname -failinglinenumber $failinglinenumber -failingline $failingline -pscommandpath $pscommandpath -positionmsg $pscommandpath -stacktrace $stacktrace
                
            #Write-ErrorLog -hostname "Get-ComputerInfo was failing." -errormsg $errormsg -exception $exception -scriptname "GetComputerInfo.ps1"
            Write-Verbose "Finish writing to Error log."
            
        }
}

INFO: To understand BEGIN, PROCESS, and END blocks in PowerShell please read PowerShell Function Begin Process End Blocks Explained With Examples.

Comment-Based Help Section

For every one of my own CmdLets, I write Comment-Based help as well.

INFO: If you want to learn how to write comment-based Help for your own PowerShell Functions and Scripts please read these articles How To Write PowerShell Help (Step by Step). In this article How To Write PowerShell Function’s Or CmdLet’s Help (Fast), I explain the PowerShell Add-on that helps us be fast with writing help content.

Useful PowerShell Articles about Export/Import Data

Here are some useful articles and resources:

• Get-Content
• Out-File
• Import-Csv
• Export-Csv
• Import-Clixml
• Export-Clixml
• ConvertTo-Csv
• ConvertFrom-Csv

Get-ComputerInfo CmdLet Source Code

DISCLAIMER: Get-ComputerInfo function is part of the Efficiency Booster PowerShell Project and as such utilize other CmdLets that are part of the same project. So the best option for you in order for this function to work without any additional customization is to download the source code of the whole project from here.

INFO: My best advice to every PowerShell scripter is to learn writing own PowerShell Advanced Functions and CmdLets and I have written several articles explaining this, so please read them. How To Create A Custom PowerShell CmdLet (Step By Step). Here I explain how to use PowerShell Add-on Function to be faster in writing PowerShell Functions How To Write Advanced Functions Or CmdLets With PowerShell (Fast).

Here is the source code of the whole Get-ComputerInfo CmdLet

<#
.SYNOPSIS
Get additional information about server. (IPAddress, Environment, Logical Name)
.DESCRIPTION
Get additional information about server: 
- Environment (Production, Acceptance, Test, Course...)
- Logical Name (Application - APP, Web - WEB, Integration - INT, Scan - SCAN, Terminal Server - TS, FTP - FTP server)
- IPAddress
It is important that csv file with list of servers has been saved in folder: 
    $home\Documents\WindowsPowerShell\Modules\01servers

Name pattern of csv file should be client + solution + servers.csv 
for example OKFINservers.csv => Client = OK - O client; Solution = FIN - Financial solution ; servers
or another example BKHRservers.csv => Client = BK - B client; Solution = HR - Humane resource solution; servers

CSV file should look like:
hostname,ipaddress,logicalname,environment
APP100001,192.168.1.120,APP1,PROD
APP100002,192.168.1.121,APP2,PROD
FTP000001,192.168.1.122,FTP1,PROD
WEB000001,192.168.1.150,WEB01,PROD
APP110001,192.168.2.120,APP1,TEST


Steps are:
- Create .csv file with suggested name pattern
- Save .csv file in folder 01servers of PowerShell modules folder
- Write date in .csv for hostname,ipaddress,logicalname,environment

.PARAMETER computername
DNS Name of server.
.PARAMETER client
OK - O client
BK - B client
.PARAMETER solution
FIN - Financial solution
HR - Human Resource solution
.EXAMPLE
Get-ComputerInfo -computername 'APP100001' -client 'OK' -solution 'FIN'
.EXAMPLE 
Get-ComputerInfo -computername 'APP100025' -client 'BK' -solution 'HR'

.INPUTS
System.String

.OUTPUTS
Selected.System.Management.Automation.PSCustomObject

Result: Hostname, IPadresse, LogicalName, Environment

.NOTES
FunctionName : Get-ComputerInfo
Created by   : Dejan Mladenovic
Date Coded   : 10/31/2018 19:06:41
More info    : http://improvescripting.com/

.LINK
How To Import Or Export Data Using PowerShell

Import-Csv
#>
Function Get-ComputerInfo {
[CmdletBinding()]
param (
    [Parameter(Mandatory=$true,
                ValueFromPipelineByPropertyName=$true, 
                HelpMessage="Computer name.")]
    [string]$computername,
    
    [Parameter(Mandatory=$true, 
                HelpMessage="Client for example OK = O client, BK = B client")]
    [string]$client,
     
    [Parameter(Mandatory=$true,
                HelpMessage="Solution, for example FIN = Financial, HR = Human resource")]
    [string]$solution 
)

BEGIN { 
        
        Write-Verbose "BEGIN section"

        $serverlistfolder = "$home\Documents\WindowsPowerShell\Modules\01servers"
        
        if  ( !( Test-Path -Path $serverlistfolder -PathType "Container" ) ) {
            
            Write-Output "Create server names lists folder in: $serverlistfolder"
            New-Item -Path $serverlistfolder -ItemType "Container" -ErrorAction Stop
        
        }

}
PROCESS {               
        
        Write-Verbose "Import data for $client - $solution - $computername"
        $filenameprefix = "$client$solution"
        $constant = "servers.csv"
        $filename = "$filenameprefix$constant"
        $importfile = "$home\Documents\WindowsPowerShell\Modules\01servers\$filename"
        
        
        if ( ( Test-Path -Path $importfile -PathType "Leaf" ) ) {

            if ( $computername -eq 'localhost' ) {
                $computername = $env:COMPUTERNAME
                Write-Verbose "Replace localhost with real name of the server."
            }
        
            Import-Csv $importfile | Where-Object -FilterScript { $_.hostname -eq "$computername" } | Select-Object -Property hostname, ipaddress, logicalname, environment         
        
        } else {
        
            Write-Warning "Get-ComputerInfo CmdLet failed."
            Write-Warning "List of server names file $importfile does not exist."
            Write-Warning "Read Get-ComputerInfo CmdLet help to find out more info."
            
            $errormsg = "List of server names file $importfile does not exist."
            $exception = "Read Get-ComputerInfo CmdLet help to find out more info."
            
            $stacktrace = $_.ScriptStackTrace
            $failingline = "Test-Path -Path $importfile -PathType Leaf"
            $positionmsg = $_.InvocationInfo.PositionMessage
            $pscommandpath = $_.InvocationInfo.PSCommandPath
            $failinglinenumber = "103"
            $scriptname = "GetComputerInfo.ps1"

            Write-Verbose "Start writing to Error log."
            
            Write-ErrorLog -hostname "Get-ComputerInfo CmdLet was failing." -env "ALL" -logicalname "ALL" -errormsg $errormsg -exception $exception -scriptname $scriptname -failinglinenumber $failinglinenumber -failingline $failingline -pscommandpath $pscommandpath -positionmsg $pscommandpath -stacktrace $stacktrace
                
            #Write-ErrorLog -hostname "Get-ComputerInfo was failing." -errormsg $errormsg -exception $exception -scriptname "GetComputerInfo.ps1"
            Write-Verbose "Finish writing to Error log."
            
        }
}
END {

        Write-Verbose "END section"

}
}
#region Execution examples
#This one will return: Hostname, IPadresse, LogicalName, Environment
#Get-ComputerInfo -computername "localhost" -client "OK" -solution "FIN"

#This one will fail
#Get-ComputerInfo -computername "APP100001" -client "SK" -solution "FIN" -Verbose
#endregion