Author Archives: Blogger

Posted on by

Importing and Exporting CSV and XML Files in PowerShell

The Import-Csv cmdlet reads a CSV file and outputs a list of custom PowerShell objects, one for each row of the input data. PowerShell uses the first row of the CSV file as the objects’ properties, and the subsequent lines of the file are the output objects. For example, if you run the command.

Import-Csv Sample.csv
If the CSV file you want to import doesn’t have a header row, you can use the -Header parameter to name the object properties. That is, if Sample1.csv was missing the first line (the header row), you’d use a command like this instead:
Import-Csv Sample.csv -Header DisplayName,EmailAddress
Import-Csv uses the comma character as its default delimiter, but you can use the -Delimiter parameter to specify a different delimiter character. For example, if Sample.csv used a tab character as the delimiter, you’d use this command:
Import-Csv Sample.csv -Delimiter "`t"
Because Import-Csv outputs PowerShell objects, you can then use other PowerShell cmdlets to process the objects. For example, suppose you want to sort the output by DisplayName, but you only want to output the Mail property from each object. To do this, you can use the Sort-Object and Select-Object cmdlets:
Import-Csv Sample.csv | Sort-Object DisplayName |   Select-Object Mail
You can also pass these objects along to the ForEach-Object cmdlet for processing:
Import-Csv Sample.Csv | ForEach-Object {
                                '"{0}" <{1}>' -f $_.DisplayName,$_.Mail

}
This command uses the -f operator to output a formatted string for each object and produces the output shown in Figure 3. If you’re unfamiliar with how to use the ForEach-Object, Sort-Object, and Select-Object cmdlets, see “PowerShell Basics: Filtering Objects” and “PowerShell Basics: Select-Object.”

Exporting CSV FilesSometimes you need to create a CSV file from PowerShell output objects. To do so, you pipe PowerShell’s output to the Export-Csv cmdlet and specify a filename. PowerShell will then write the output objects to a CSV file. It really is that simple, with one minor caveat. By default, Export-Csv writes a line starting with the string #TYPE as the first line of the CSV file. Export-Csv’s -NoTypeInformation parameter omits this extra line of output, so I usually include this parameter.For example, suppose you want to create a copy of Sample.csv sorted by the DisplayName property. All you need to do is import the file, pipe its contents to the Sort-Object cmdlet, then export the contents to a new CSV file:

Import-Csv Sample.csv | Sort-Object DisplayName | Export-Csv Sample-Sorted.csv -NoTypeInformation
Note that Export-Csv can output any PowerShell objects, not just objects produced from using Import-Csv. For example, consider the command:

 

Get-ChildItem | Sort-Object Length |                                 Select-Object FullName,LastWriteTime,Length |                                 Export-Csv Data.csv -NoTypeInformation

 

This command creates a CSV file containing the files in the current directory, sorted by file size. Note that this command uses the Select-Object cmdlet to select each file’s full filename, last write time, and file size (length), so these three properties will be the columns in the CSV file.

 

Posted on by

Search string in log file with powershell

Search string use “or”
cat test.txt | where-object {$_.contains(“CREATE”)-or$_.contains(“date”)}

Search string use “and”
cat test.txt | where-object {$_.contains(“CREATE”)-and$_.contains(“date”)}

Search string and export to a test file
cat test.txt | where-object {$_.contains(“CREATE”)-or$_.contains(“date”)} | out-file test2.txt

Export to a CSV file
Get-Process | Export-Csv c:\scripts\test.csv