How to Append a PowerShell Output to an XLS Excel File
Windows PowerShell is a command line-based scripting tool that comes with the Windows operating system. Administration tasks are performed using cmdlets (command-lets), which are commands that perform one simple function. Files can be imported directly into Microsoft Excel with the "File," "Open" menu option or through the "Get External Data" options in the Data tab.
Other People Are Reading
Instructions
-
-
1
Click "Start," "All Programs," "Accessories" and "Windows PowerShell"; select "Windows PowerShell ISE." This will open the Integrated Script Environment for Windows PowerShell.
-
2
Type "cd\" in the "Command Pane" (the pane with the > prompt) and press "Enter" to move to the root of the "C:" drive. Type "md testdirectory" and press enter to create a test directory within the "C:" drive. Substitute "C:" with the preferred drive letter for documents. Type "cd testdirectory" to move into the new directory.
-
-
3
Type "get-process | export-csv .\processes.csv" and press the "Enter" key. This command will put the output of "get-process" into a new CSV file. Leave the PowerShell window open and in Windows click "Start," and then type "Excel" in the "Search text" box and press "Enter."
-
4
Click the "Excel" button and choose "Open" from Microsoft Excel. Click the down arrow next to the "All Files *.*" option and choose "Text Files (.prn,.txt.csv)." Navigate to the new test directory and double click the "processes.csv" file to open it. Note that the columns are named and the delimited file opened cleanly in Excel.
-
5
Click the "Excel" button, and choose "Save As..." Type in the new file name of "processes2.csv" to make a copy of the file and select the "Save" button. Click the "Yes" button to "Keep the workbook in this format." There are now two CSV files to work with.
-
6
Bring the PowerShell application back to the foreground. In the command pane, type "add-content -path processes.csv -value (get-content processes2.csv)" and press the "Enter" key. Open the "processes.csv" file again inside Excel. The "processes2.csv" file is now appended on the "processes.csv" file.
-
7
Navigate to the link entitled "TechNet: Convert CSV to XSLX" in References. Copy the script showing with the gray background. Bring PowerShell back to the Windows foreground; right-click in the script pane and choose "Paste." Click the "Run Script" green arrow icon to execute the script.
-
8
Click in the command pane and type "ConvertCSV-ToExcel -inputfile "processes.csv" -output "processes.xlsx" "; press the "Enter" key. The CSV file has been converted to a XLSX file.
-
1
Tips & Warnings
PowerShell scripts are typically saved as .ps1 files.
A CSV file is a comma separated value text file. The comma is the default delimiter that Excel understands as a break and moves to the next column. A CSV file can be created in any text editor.
The script created in the tutorial was not saved. To save it for future reference, click "File," and select "Save As" from Windows PowerShell.
PowerShell Scripts are restricted by default. You may need to modify Get-ExecutionPolicy to run scripts.
Windows PowerShell is a very powerful tool. Use caution when typing in commands.
References
Resources
- Photo Credit Photodisc/Photodisc/Getty Images
