CSV File
Explore how to read and handle CSV files in both PowerShell and Python. Understand the use of PowerShell's Import-CSV cmdlet and Python's csv module to parse and process tabular comma-separated data effectively. This lesson helps you compare and apply file handling techniques in both scripting languages for efficient data exchange.
We'll cover the following...
Introduction
Exchange of information into and out of programs is a requirement for most programming and scripting languages worldwide. The most common approach for this information exchange is through text files. One of the most popular formats for exchanging such data is the CSV format.
What is CSV?
CSV is short for Comma-Separated Values, which is tabular data that has been saved as plaintext data separated by commas (,) or any other delimiter. For example, we have data in the following table that has header and data elements. This data can be converted into CSV data where each row of a table is a new line and has data separated with a comma.
Tabular Data:
| Column1 | Column2 | Column3 |
|---|---|---|
| Data1 | Data2 | Data3 |
| Data1 | Data2 | Data3 |
CSV Data:
Column1,Column2,Column3
Data1,Data2,Data3
Data1,Data2,Data3
Reading a CSV File
Let’s take a look at how to read CSV files.
Using PowerShell cmdlet Import-CSV
Reading CSV data from a file is fairly easy in PowerShell. All we need to do is use the Import-CSV cmdlet with a valid file path. PowerShell will read the CSV file and convert it to PowerShell objects and return objects on our console. Let’s suppose we have the following CSV data saved as filename Service.csv.
"Name","Status"
"WinDefend","Stopped"
"WinHttpAutoProxySvc","Running"
"Winmgmt","Running"
"WinRM","Running"
To read or parse the CSV formatted files into PowerShell objects, run Import-CSV <File Name>.
Import-Csv C:\Temp\Service.csv
Using Python’s csv Library
Python has an inbuilt csv library to work with CSV files that can both read and write data from/to the CSV files. First, we import the csv module and open the CSV file in read-only mode so that the file can be read. Once that is done, we pass the file handler object to the reader() function of the csv library which will parse the CSV format. Then we iterate through each row of parsed CSV files, as demonstrated in the following example.
Please note that the named parameter delimiter in the reader() function is totally optional because the default separator character, or delimiter, is the comma ( , ). We can also use other popular delimiters, though, which include the tab (\t), colon (:), and semicolon (;) characters.
In the above example, each row is a list of items per column, which means we can even access each column in a row as an index of a list. In the following example, column-1 and column-2 are accessed and processed using row[0] and row[1] respectively.
Another approach is to read the CSV data directly into a dictionary using the DictReader class of csv library instead of processing a list of individual string elements on each row of the CSV file. Once the content of CSV file is converted to Python dictionary, we can access the individual elements of a row by passing the name of the column as key to the dictionary, as demonstrated in the following example: