Hey, Scripting Guy! How Do I Create a CSV File from Within Windows PowerShell?
Hey Scripting Guy! I am having a problem trying to create a CSV file from within Windows PowerShell. I thought at first I could use the Write-Host cmdlet to do this. I came up with this command:
Write-Host “col1,col2,col3” >> “C:fsotest.csv”
But when I go to the test.csv file, it is empty. I am really stuck. I have spent more than 10 hours searching the Internet and have come up with nothing but sore fingers. Help!
— WS
Hello WS,
You know a trumpetfish will spend hours hunting for useful items also. A trumpet fish will commonly feed on small fish and even crustaceans. They have a small jaw opening, so they will sneak up on their supper and suck them into their mouth. It is a process that is kind of like eating sushi through a straw. They are really patient and also good at camouflaging themselves. The following picture is a yellow trumpet fish I saw while I was scuba diving off the coast of Maui.
WS, one of the cool things about being a trumpet fish is that you rarely get stuck. This is obviously due to the long cylindrical nature of his design. Scripters, on the other hand, often get stuck. Perhaps we should spend more time eating sushi through straws? It is amazing how persistent the comma-separated value (CSV) file format has been. Personally, I love it because it is simple, easy to understand, and easy to work with. The easy way to write to a Microsoft Excel spreadsheet or a Microsoft Access database, or to create a table in Microsoft Word is to use a CSV file. It is no wonder, then, that one of the consistent themes that comes through the scripter@microsoft.com e-mail box is related to attempts to work with CSV files. A few years ago, we wrote a really good article about creating CSV files from within VBScript. It was called, rather intuitively, How Can I Create a CSV File?
Without cmdlet support for selecting objects and exporting to a CSV file format, one might be tempted to use the filesystemobject from VBScript fame. One could then follow the How Can I Create a CSV File? script from that old Hey, Scripting Guy! article and make a rather easy translation from VBScript to Windows PowerShell. An example of such a script is the FSOBiosToCsv.ps1 script.
FSOBiosToCsv.ps1
$path = “c:fsobios1.csv”
$bios = Get-WmiObject -Class win32_bios
$csv = “Name,Version`r`n”
$csv +=$bios.name + “,” + $bios.version
$fso = new-object -comobject scripting.filesystemobject
$file = $fso.CreateTextFile($path,$true)
$file.write($csv)
$file.close()
In the FSOBiosToCsv.ps1 script, the first thing that you do is assign a path for the CSV file that will be created. Next, you use the Get-WmiObject cmdlet to retrieve an instance of the Win32_Bios WMI class. This class returns information about the BIOS on the local computer. The resulting BIOS information is stored in a variable named $bios. This is seen here:
$bios = Get-WmiObject -Class win32_bios
The next thing you do is create the header for the CSV file. The bios1.csv file will contain two pieces of information: the name and the version of the BIOS. You next want to have data appear on the second line. To do this, you need to insert a carriage return and a new line (similar to the VBCRLF from VBScript). The `r creates a carriage return, and the `n creates the new line character. This is shown here:
$csv = “Name,Version`r`n”
On the line that follows the header information, you will need to add the data itself to the file. Because the entire Win32_Bios WMI object is stored in the $bios variable, and because there is only one instance of a BIOS on a computer, you can directly access each of the property values through the $bios variable. You want to separate each property value with a comma (that is the point of a CSV file after all). To do this you will use concatenation to glue the comma between the two property values. This is seen here:
$csv +=$bios.name + “,” + $bios.version
Now for the really cool part: If you loved working with the FileSystemObject in VBScript, you do not have to give it up. You can create the object by using the New-Object cmdlet. This is seen here:
$fso = new-object -comobject scripting.filesystemobject
To create a text file using the FileSystemObject you use the CreateTextFile method. The returned textfile object is stored in the variable $file as seen here.
$file = $fso.CreateTextFile($path,$true)
The last thing to be done is to write to the text file. Use the Write method for this operation:
$file.write($csv)
$file.close()
When you run the script, nothing is displayed. This is because there is no confirmation message included in the script. Often when I am writing automation scripts that retrieve information from different computers, I have them write information to CSV files because I do not want to clutter the script with confirmation messages. Imagine running a script against 1,000 remote computers. Dealing with 1,000 confirmation messages would be somewhat annoying. However, when you go to the path location, you will find the Bios1.csv file. The contents of this file are seen here:
WS, it is no secret that cmdlet support makes working with Windows PowerShell a breeze. If you need to check the status of the BITS service, it is easiest to use the Get-Service cmdlet as shown here:
Get-Service –name bits
To find information about the explorer process, you can use the Get-Process cmdlet. This is seen here:
Get-Process -Name explorer
If you need to stop a process, you can easily use the Stop-Process cmdlet:
Stop-Process -Name notepad
You can even check the status of services on a remote computer, by using the –computername switch from the Get-Service cmdlet as seen here:
Get-Service -Name bits -ComputerName vista
If you are working in a cross-domain scenario where authentication would be required, you will not be able to use Get-Service or Get-Process because those cmdlets do not have a –credential parameter. You would need to use one of the remoting cmdlets in Windows PowerShell 2.0 such as Invoke-Command, which will allow you to supply an authentication context.
Instead of replicating a VBScript into Windows PowerShell syntax, you can check the BIOS information on a local computer, and save the information to a comma-separated value file with just a few lines of code. An example of such a script is the ExportBiosToCsv.ps1 script.
ExportBiosToCsv.ps1
$path = “c:fsobios.csv”
Get-WmiObject -Class win32_bios |
Select-Object -property name, version |
Export-CSV -path $path –notypeinformation
The first line of the ExportBiosToCsv.ps1 script is essentially the same as the first line of the previous script (the only difference is the name of the text file). The remainder of the script takes advantage of Windows PowerShell features. After obtaining the BIOS information, the instance of the Win32_Bios WMI class is pipelined to the next cmdlet instead of being stored in a variable. The use of the pipeline is one of Windows PowerShell’s strongest features, and it generally results in significant performance improvements over storing results into a variable and then accessing the properties. This is seen here:
Get-WmiObject -Class win32_bios |
Next, you use the Select-Object cmdlet to retrieve the two properties that are of interest: the name and the version of the BIOS. These two property values are then sent along the pipeline as well. This is seen here:
Select-Object -property name, version |
Last, you will use the Export-CSV cmdlet to create the CSV file. The notypeinformation switched parameter is used to keep the CSV file from having a comment written on the first line that describes the source of the file. The use of the notypeinformation switched parameter can save you a lot of frustration when you try to import the CSV file into other programs. This is seen here:
Export-CSV -path $path –notypeinformation
When you run the ExportBiosToCsv.ps1 script, a file named bios.csv is created in the location that you specified for the $path variable. The contents of this file are seen here:
WS, you can see that by using the Windows PowerShell cmdlets, common tasks such as creating CSV files can be made much easier than they were in the past. Speaking of making things easier, if you want to keep up with all the things that are going on the Script Center, you can follow us on Twitter. You can also join the Scripting Guys group on Facebook and find lots of other friends who are interested in scripting. If you ever get stuck again while you are working on a script, you should post your question on the Official Scripting Guys Forum. There are some really smart scripters who hang out there.
This brings our discussion about handling output to an end. Join us tomorrow as we open up the mail bag, and select an assortment of questions that require short answers. Yes, it is time for Quick-Hits Friday! Be there. Aloha!
Ed Wilson and Craig Liebendorfer, Scripting Guys
Light
Dark
0 comments