How to Import and Export Delimited Files, like CSV, in PowerShell

In this tutorial, we will see how we can use PowerShell to import and export CSV file's data.

Managing CSV Files using Import/Export-CSV in PowerShell


We have two important cmdlets import-csv/export-csv which are widely used while working with CSV. Before moving to how to manage these type of files, First look at what is CSV file:

A Comma Separated Values (CSV) file is a plain text file that contains a list of data. These files are often used for exchanging data between different applications. For example, databases and contact managers often support CSV files.

A CSV file has a fairly simple structure. It’s a list of data separated by commas as you can see in below screenshot. We have file "email.csv" having email address and name separated by comma. You can use notepad/excel to open thses file.

Now lets move back to our main topic, how to manage csv files with PowerShell. 

Reading CSV Files with Import-Csv

Importing of csv file is pretty straight forward. First move to directory where file is present and execute below command with file name or you can pass file name with complete path to import data in powershell.


you can also store data in variable like below and use it wherever required:


If your file have records with lots of columns and you need to import only few of them use Select-Object.

Pipe the output of Import-Csv to Select-Obejct and pass column name to import.


Export-Csv : Saving CSV Files with PowerShell

As Import-Csv allows you to read data from csv files and use it in PowerShell scripts as per your requirement. You can use Export-Csv cmdlet to save the output of your scripts in csv file.

By default, Export-Csv will overwrite any file using the same name But you can use it to add lines to an existing file using the Append parameter. When the Append parameter is used, the input object must have each of the fields listed in the CSV header or an error will be thrown unless the Force parameter is used.

Below, we have pipelined the output of Get-Process to Export-Csv to save it in processfile.csv.




Suppose we don't want to store complete output. We can use Select-Object to filter data.




Export-CSV and the #TYPE String

By default, using Export-CSV with no additional parameters will include a #TYPE string at the top of your CSV file. This string is then followed by the type of object Export-Csv received.


To remove this string, use the NoTypeInformation parameter. This parameter removes this string from the CSV entirely.


When we try to append data to existing file and there is mismatch between existing column and the ones we are trying to append, error will be thrown.



When Force and Append parameters are combined, objects that contain mismatched properties can be written to a CSV file. Only the properties that match are written to the file. The mismatched properties are discarded.

Summary
Using the Import-CSV and Export-CSV PowerShell cmdlets allow you to easily work with CSV files. These are two useful cmdlets that you should use frequently when dealing with objects and CSV files.

For live demo of topic: 





Comments

Post a Comment

Popular Posts

How to generate a GUID in PowerShell

Image
 If you ever need a unique id in your script, you can create GUID (globally unique identifier) In PowerShell 4.0 and earlier you can use [guid] class with its static method. In PowerShell 5.0 and later, you can use New-Guid cmdlet: You can use Get-Member to know its object type. You can also store GUID into a variable like below If you want to get GUID as string, use ToString  method or Guid  property. Both return output of string type, you can verify this using Get-Member .
Read more

PowerShell Arithmetic Operators

Image
In this tutorial we will see about PowerShell Arithmetic Operators and how PowerShell is dealing with them. Arithmetic operators are used to perform numeric calculations. As well as numeric calculations, the addition operator may also be used with strings, arrays, and hashtables. Multiplication operator may also be used with strings and arrays. Below is the list of PowerShell arithmetic operators. Operator Description Example + Adds numeric values 4 + 6 concatenates a string, arrays, and hash tables “CS” + “Tutorials” – Subtracts numeric values 4 – 2 Makes a number negative -98 * Multiple numeric values 4 * 2 copy string, arrays to the specified number of times “!” * 4 / Divides numeric values 4 / 2 % Gives remainder after division 7 % 3 For live demo, watch below video. I have demonstrated the use of PowerShell arithmetic operators by  creating calculator. Along with operators, Switch statement and While loop also used.  
Read more