{"id":1729,"date":"2013-12-03T00:00:00","date_gmt":"2013-11-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/powershell-data-basics-file-based-data\/"},"modified":"2017-10-25T15:16:45","modified_gmt":"2017-10-25T15:16:45","slug":"powershell-data-basics-file-based-data","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/powershell-data-basics-file-based-data\/","title":{"rendered":"PowerShell Data Basics: File-Based Data"},"content":{"rendered":"
\n

Introduction<\/h2>\n

As I have continued working with PowerShell I realized that at the core is the data <\/code>(as with any software really). In the case of PowerShell, this boils down to converting external data\u00a0 into PowerShell objects and vice versa. This is the first of a series of articles that shows you how to import<\/i> almost all of the common data formats you are likely to encounter, and how to export<\/i> to some of them as well.<\/p>\n

Part 1: General Data<\/h2>\n

This article explains how to import data from files of all kinds except for XML, which is covered separately in the next article in this series. The sections below describe a variety of text formats, from fixed-width, variable-width, and ragged-right files to CSV, property lists, INI files, and JSON data, and concludes with a treatment of importing and exporting to Excel.<\/p>\n

Part 2 of this series will \u00a0illustrate the two principal \u00a0technologies available within PowerShell to access XML data, XPath and objects. You will learn how to move XML data into and out of PowerShell along with how to reference, manipulate, and modify it.<\/p>\n

Data by Line<\/h2>\n

Read a Text File in its Entirety<\/h3>\n

Regular readers will know of my fondness for Lewis Carroll; the current topic again draws me unerringly to this inadvertent comment on software methodology:<\/p>\n

‘Begin at the beginning,’ the King said gravely, ‘and go on till you come to the end: then stop.’<\/i><\/p>\n

–Lewis Carroll, Alice’s Adventures in Wonderland<\/b><\/p>\n

In the context of PowerShell, the beginning is where you learn to read a file in PowerShell. My sample file consists of fifteen lines containing the text “one”, “two”, etc., on up to “fifteen”. The Get-Content<\/code> cmdlet is the primary workhorse for bringing data from a file into PowerShell:<\/p>\n\n\n\n\n
\n

Get-Content -Path .\\numbers.txt<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

one<\/code><\/p>\n

two<\/code><\/p>\n

three<\/code><\/p>\n

four<\/code><\/p>\n

five<\/code><\/p>\n

six<\/code><\/p>\n

seven<\/code><\/p>\n

eight<\/code><\/p>\n

nine<\/code><\/p>\n

ten<\/code><\/p>\n

eleven<\/code><\/p>\n

twelve<\/code><\/p>\n

\t\tthirteen<\/code><\/p>\n

\t\tfourteen<\/code><\/p>\n

fifteen<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In the absence of any other direction, Get-Content<\/code> displays its result on the console. You could redirect this output to a file using exactly the same mechanism as in a DOS or Unix or Linux shell …<\/p>\n

Get-Content -Path .\\numbers.txt> newfile.txt <\/pre>\n
… or you could send this output through the pipeline to another process, likewise:<\/p>\n
Get-Content -Path .\\numbers.txt| ... <\/pre>\n
Yet another common outlet is collecting the output into a variable for later processing:<\/p>\n
$numbers = Get-Content-Path .\\numbers.txt <\/pre>\n
Read a Text File Selectively by Index<\/h3>\n
PowerShell uses the $ <\/code>prefix character to indicate a variable. Get-Content<\/code> conveniently stores its output into $numbers as an array. Here are a couple of expressions to illustrate this:<\/p>\n\n\n\n\n
\n
$numbers.Count # report the cardinality<\/code><\/b><\/p>\n
$numbers[0]\u00a0\u00a0\u00a0 # display the first item<\/code><\/b><\/p>\n
$numbers[14]\u00a0\u00a0 # display the last item<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
15<\/code><\/p>\n
one<\/code><\/p>\n
fifteen<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
PowerShell also offers a very handy range operator allowing you to select a portion of the array as a subset:<\/p>\n\n\n\n\n
\n
$numbers[0..3]<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
one<\/code><\/p>\n
two<\/code><\/p>\n
three<\/code><\/p>\n
four<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Applying that concept back to the original file, you can directly index the content of the file by line number-the parentheses are needed to first materialize the array that you then index with the bracket notation:<\/p>\n\n\n\n\n
\n
(Get-Content -Path .\\numbers.txt)[0..3]<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
one<\/code><\/p>\n
two<\/code><\/p>\n
three<\/code><\/p>\n
four<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The array indexing notation gives you the most succinct way to select specific lines.\u00a0 It is almost as though you are doing a pipeline, i.e.  read-a-file | select-lines-from-file<\/code>. You can, in fact, do the same operation with a full-fledged pipeline:<\/p>\n
Get-Content-Path .\\numbers.txt |Select-Object -First 4\u00a0 <\/pre>\n
Here you are passing the file contents to the Select-Object<\/code> cmdlet and instructing it to take the first four elements, just as done in the previous example. That works for lines at the beginning, and you also have a -Last parameter to grab lines from the end of the file, but what about something in the middle? With array notation you can specify an arbitrary range [m..n]<\/code>. With Select-Object<\/code> this equates to two pipelined calls to Select-Object<\/code>. Here is code to demonstrate the equivalence of the two methods:<\/p>\n
$m=5; $n=7\r\nGet-Content .\\numbers.txt | select -First ($n+1) | select -Last ($n-$m+1)\r\n(Get-Content .\\numbers.txt)[$m..$n] \r\n<\/pre>\n
Obviously in this case the array indexing notation is much cleaner and more concise, but this example is valuable to introduce you to ways to manipulate data in PowerShell. You might use this technique to skip the header row when reading in a file, for example.<\/p>\n
Read a Text File Selectively By Search<\/h3>\n
Now consider the scenario where rather than wanting to get specific lines by number you want to get all lines that contain a particular string. Use the  Select-String<\/code> cmdlet (similar to the Linux grep<\/code> command or the DOS findstr<\/code> command).<\/p>\n\n\n\n\n
\n
Get-Content -Path .\\numbers.txt | Select-String teen<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tthirteen<\/code><\/p>\n
 \t\tfourteen<\/code><\/p>\n
fifteen<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You are not limited to string constants. In fact, the default search term is a regular expression unless you specify that you want to match just\u00a0 a simple string with the -SimpleMatch<\/code> parameter . But if your parameter does not contain any regex metacharacters you can omit the “-SimpleMatch<\/code>” label as I have done in the above example. So if you want to select the lines ending in teen that also contain an “i” earlier in the string, use this:<\/p>\n\n\n\n\n
\n
Get-Content -Path .\\numbers.txt | Select-String -pattern \"i.*teen$\"<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tthirteen<\/code><\/p>\n
fifteen<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Data by Fixed Width Fields<\/h2>\n
The PowerShell Approach<\/h3>\n
The next logical step is to not just read a file by lines, but to split up those lines into fields. Say you have a text file where each line is a record and each record consists of, for example, 7 characters for the given name, 10 characters for the surname, and 3 characters for an ID of some kind.<\/p>\n\n\n\n\n
\n
A Fixed Width File<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\t12345671234567890123<\/code><\/p>\n
george  \t\tjetson\u00a0\u00a0\u00a0 5\u00a0  \t\t<\/code><\/p>\n
warren  \t\tbuffett\u00a0\u00a0 123<\/code><\/p>\n
 \t\thoratioalger\u00a0\u00a0\u00a0\u00a0 -99<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/p>\n
#process the file\r\nGet-Content .\\fixedwidth.log | #read each line as a separate object\r\n\u00a0\u00a0 select -Property @{name='ID';expression={$_.Substring(17).Trim()}}, #first column\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='FirstName';expression={$_.Substring(0,7).Trim()}}, #2nd \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='LastName';expression={$_.Substring(7,10).Trim()}} #3rd\r\n<\/pre>\n
<\/p>\n\n\n\n\n
\n
A Fixed Width File Converted to Objects<\/code><\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Id\u00a0 \t\tFirstName LastName\u00a0 <\/code><\/p>\n
--\u00a0 \t\t--------- --------\u00a0 <\/code><\/p>\n
123  \t\t1234567\u00a0\u00a0 1234567890<\/code><\/p>\n
5\u00a0\u00a0 \t\tgeorge\u00a0\u00a0\u00a0 jetson\u00a0\u00a0\u00a0 \t\t<\/code><\/p>\n
123  \t\twarren\u00a0\u00a0\u00a0 buffett\u00a0\u00a0 \t\t<\/code><\/p>\n
-99  \t\thoratio\u00a0\u00a0 alger<\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The Regex Approach<\/h3>\n
\u00a0This approach does use regular expressions and the mere mention of them causes otherwise hearty, courageous, and fearless developers to quake in their boots. Or so I have heard. But for the task at hand what you need to know about regular expressions is simple. Really. For each field in the file you need to give it a name and you need to know its length. Take those two values and plug them into this template:<\/p>\n
(?<field name goes here>.{field length goes here})<\/pre>\n
Repeat that for each field then lay each one down adjacent to the previous one. Here I am representing names with “n” and lengths with “l”:<\/p>\n
(?<n1>.{l1})(?<n2>.{l2})(?<n3>.{l3})<\/pre>\n
Finally, add a caret (^) at the front end and a dollar sign ($) at the rear:<\/p>\n
^(?<n1>.{l1})(?<n2>.{l2})(?<n3>.{l3})$<\/pre>\n
Here is the realization of this for the example at hand:<\/p>\n
$regex= \"^(?<FirstName>.{7})(?<LastName>.{10})(?<Id>.{3})$\"<\/pre>\n
After you define the regex, the code to use it to parse the same text file is almost trivial, but this reads the file and extracts all the fields from each line (record) into an array of PowerShell objects-all thanks to the magic of the built-in -match<\/code> operator.<\/p>\n
function ImportWith-Regex([string]$FilePath, [string]$regex)\r\n{\r\n\u00a0\u00a0\u00a0 Get-Content $FilePath | ForEach-Object {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($PSItem -match $regex)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 New-Object PSObject -Property (Get-RegexNamedGroups $matches)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 }\r\n}\u00a0 \r\n<\/pre>\n
Note that this code needs a supplemental function to do its work, shown next, that collects just the parts of the regex match results that are needed. (Don’t worry about exactly how this works).<\/p>\n
function Get-RegexNamedGroups($hash)\r\n{\r\n\u00a0\u00a0\u00a0 $newHash = @{};\r\n\u00a0\u00a0\u00a0 $hash.keys | ? { $_ -notmatch '^\\d+$' } | % { $newHash[$_] = $hash[$_] }\r\n\u00a0\u00a0\u00a0 $newHash \r\n} \r\n<\/pre>\n
The major advantage of the above approach is that you have complete separation between your data specification (the regex definition) and the code to process it. You can use the same function on a different file by just passing in a different regex and a different file name.<\/p>\n
$regex = \"^(?<FirstName>.{7})(?<LastName>.{10})(?<Id>.{3})$\"\r\nImportWith-Regex .\\fixedwidth.log $regex\r\n<\/pre>\n
However, it is worth revisiting the earlier code-that used substrings-and seeing how you could do it with regular expressions.\u00a0 Just make sure that your regex and your explicit field names in the select<\/code> statement remain in sync.<\/p>\n
$regex = \"^(?<FirstName>.{7})(?<LastName>.{10})(?<Id>.{3})$\"\r\nGet-Content .\\fixedwidth.log |\r\n\u00a0\u00a0 ForEach-Object {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 if ($_ -match $regex) {$matches} else {Throw 'line $_ was corrupt'}\r\n\u00a0\u00a0 } |\r\n\u00a0\u00a0 select -Property @{name='FirstName';expression={$_.FirstName}}, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='LastName'; expression={$_.LastName} }, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='Id';\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 expression={$_.Id}\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\t<\/pre>\n
The Ragged Right Variation<\/h3>\n
One important variation of fixed width files is the ragged right<\/i> format, which defines all columns by width except<\/i> for the last column, which simply runs to the end of the line. Accommodating this format requires just a trivial change to the last code sample. Modify the last capture group in the regular expression to use .*<\/code> instead of .{n}<\/code> as shown below. I have also renamed it from Id<\/code> to Description<\/code> since that is now a more likely field name.<\/p>\n
$regex = \"^(?<FirstName>.{7})(?<LastName>.{10})(?<Description>.*)$\"\r\nImportWith-Regex .\\fixedwidth.log $regex\r\n<\/pre>\n
Here’s the second form:<\/p>\n
$regex = \"^(?<FirstName>.{7})(?<LastName>.{10})(?<Description>.*)$\"\r\nGet-Content .\\fixedwidth.log |\r\n\u00a0\u00a0 ForEach-Object {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 if ($_ -match $regex) {$matches} else {Throw 'line $_ was corrupt'}\r\n\u00a0\u00a0 } |\r\n\u00a0\u00a0 select -Property @{name='FirstName';\u00a0\u00a0 expression={$_.FirstName}\u00a0 }, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='LastName';\u00a0\u00a0 \u00a0expression={$_.LastName}\u00a0\u00a0 }, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{name='Description'; expression={$_.Description}}\r\n\u00a0\r\n<\/pre>\n
Data by Variable Width Fields<\/h2>\n
Importing from CSV<\/h3>\n
Pipe your data or your file into  ConvertFrom-Csv<\/code> (if immediate data) or Import-Csv<\/code> (if file data), to yield an array of PowerShell objects-no muss, no fuss. This first example uses multi-record input from a string constant for clarity; loading data from a file is just as straightforward.<\/p>\n\n\n\n\n
\n
@’<\/p>\n
Shape,Color,Count<\/p>\n
Square,Green,4<\/p>\n
Rectangle,,12<\/p>\n
Parallelogram,,0<\/p>\n
Trapezoid,Black,100<\/p>\n
‘@ |  ConvertFrom-Csv<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Shape\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \t\tColor\u00a0\u00a0\u00a0\u00a0 Count<\/code><\/p>\n
-----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \t\t-----\u00a0\u00a0\u00a0\u00a0 -----<\/code><\/p>\n
Square\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \t\tGreen\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0 \t\t<\/code><\/p>\n
 \t\tRectangle\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \t\t12\u00a0\u00a0 <\/code><\/p>\n
 \t\tParallelogram\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0 \t\t<\/code><\/p>\n
 \t\tTrapezoid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Black\u00a0\u00a0\u00a0\u00a0 \t\t100\u00a0 <\/code> <\/code><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Things to observe from this example:<\/p>\n