Converting a folder of text files into a well formatted CSV spreadsheet with PowerShell


We are in the process of changing over our Print Server and the first step on the plan was to get some data from all the computers on my network about which printers they had installed. This needed to grab the data from all users on all PCs so the only logical way to do this was to create logon script below and let it run for a week. In a previous post I discussed how PowerShell Export-CSV turns lovely tables in to garbage but now I needed to get a CSV from hundreds of separate text files …

Step 1: Gather end user printer info

Printer connections in our company are set up on a per-user basis. This means each person that logs on to a PC has a different set of printers. I could have written a script or use Group Policy to delete all existing printers and add in the relevant new ones but testing this proved unreliable on the infrastructure here. To get around this I decided to log all of the connections each user has so this can be used for reference and see how big the scope of this project is. The script printer-audit-logon-script.bat runs when every user logs on. All this one line script does is to run a VBscript (Output-Printers.vbs) but instead of outputting the info to the local screen it writes it to a text file on a network share. The file it creates has the local user and computername for easy identification

Filename: printer-audit-logon-script.bat

cscript //NOLOGO "\\FileServer\LogonAudit\Output-Printers.vbs">"\\FileServer\LogonAudit\printers-%USERNAME%-%COMPUTERNAME%.txt

Filename: Output-Printers.vbs

' Script to log any network printers v1
' Runs on XP and above, doesn't require admin rights
' Author: Thom McKiernan, last updated 13/02/2013Option Explicit
On Error Resume Next
Dim objNetwork, objPrinter, intDrive, intNetLetter Dim objWMIService, colPrinters, strComputer
' Sets the target computer name, '.' means local computer 
strComputer = "." 
'Enumerate Printer connectons 
Set objNetwork = CreateObject("WScript.Network") Set objPrinter = objNetwork.EnumPrinterConnections 
' Extra section to troubleshoot 
If objPrinter.Count = 0 
Then WScript.Echo "No Printers Mapped" 
Wscript.Quit(0) 
End If 
' List Network Printers Set objWMIService = GetObject ("winmgmts:\\" & strComputer & "\root\cimv2")
Set colPrinters = objWMIService.ExecQuery ("Select * From Win32_Printer Where Local = FALSE")
For Each objPrinter in colPrinters
Wscript.Echo objPrinter.Name
Next
'Finds the Default Printer 
Set colPrinters = objWMIService.ExecQuery ("Select * From Win32_Printer Where Default = TRUE")
For Each objPrinter in colPrinters 
Wscript.Echo "Default Printer: " & objPrinter.Name
Next
Wscript.Quit(1)

This gives you output similar to the following

Filename: printers-billg-PC167.txt

\\old-print-server\businfo2
\\old-print-server\custsrv1
\\old-print-server\custsrv3
\\new-print-server\custsrv3
\\new-print-server\Custsrv1
Default Printer: \\old-print-server\custsrv3

Step 2: Create & modify a CSV using PowerShell

While the information gathered above is useful for picking out an individual user’s printer connections, I needed something more flexible, like a CSV file, so I could create a PivotTable in Excel to summarise all the data easily. Fortunately, PowerShell can manipulate the text in these files to combine everything together in one flat file

The full script, Active Printer Connections CSV.ps1, is below but I thought it would be useful to go through it step by step afterwards to show and explain the different PowerShell cmdlets involved

Filename: Active Printer Connections CSV.ps1

$Host.UI.RawUI.WindowTitle = "Creating a CSV of Active Printer Connections"
# ---------------------------------------------------------------------------------------------------------
# Searches the 'LogonAudit' folder (populated by Printers GPO) for computers/users that don't use miami
# It then edits the CSV so it can be manipulated better in Excel
# Author: Thom McKiernan, Last Updated 06/03/2013 11:40
# ---------------------------------------------------------------------------------------------------------
$SearchFolder = "\\FileServer\LogonAudit\*.txt" # You don't need the '*.txt' bit on the end if that's all the folder contains
$CSVFilePath = "c:\working\1-Printer-Connections.csv" # the script will fail if this file is locked/open e.g. in Excel
# Search each txt file in the path and store all of the lines and filenames that don't match the pattern
$File = Select-String -Path $SearchFolder -Pattern new-print-server, default, dymo, zebra -NotMatch | select line, filename
# Loop through the results and remove any unnecessary characters
$File | foreach-object {
$_.line = $_.line.replace("\\","")
$_.line = $_.line.replace("\","%")
$_.filename = $_.filename.replace("printers-","")
#I've used a '%' so I can easily find and replace the character later on
$_.filename = $_.filename.replace("-","%")
$_.filename = $_.filename.replace(".txt","")
}
#Export all of the changes as a CSV file
$File | export-csv -path $CSVFilePath -NoTypeInformation
# Store content of CSV file
$csv = Get-Content -path $CSVFilePath
# Replace the first line so columns are named properly
$csv = $csv -replace "Line`",`"Filename","Print Server`",`"Printer`",`"Username`",`"Client"
# Replace '%' with ',' to separate properly. The backtick '`' escapes the '"' so it literally inserts a '"' into the string 
$csv = $csv -replace "%","`",`""
# Saves the new content back to the original CSV file with the correct encoding
$csv | Out-File $CSVFilePath -Encoding utf8
# Open the file in default CSV viewer
Invoke-Item $CSVFilePath

Explanation

  • $Host.UI.RawUI.WindowTitle =
    • This cmdlet gives the console window a Title while the script is running. I put a title on all my scripts so the end-user has some clue about what is running on there screen
  • $SearchFolder =<path>
    • This is the path to the folder where all the text files are stored. My folder also had some other files in it so I used the *.txt wildcard to only bother reading the text files
  • $File = Select-String -Path $SearchFolder -Pattern new-print-server, default, dymo, zebra -NotMatch | select line, filename
    • $File: This is the object that will store all of the text that I want to keep.
    • Select-String: This cmdlet looks in a specified path and retrieves any lines of text that matches a pattern. In my case, each file may contain several different old print server names and the new print server too. It was easier for me to specify what I wanted the search to ignore so I simply add the -NotMatch parameter
    • | select line, filename: This bit makes sure $File only has the text and the filename, ignoring all the other properties that Select-String gathers (like line number)

So far, if I stopped at this point, $File would look like the following

Line Filename
 ---- --------
 \\old-server\businfo1 printers-administrator-PC140.txt
 \\old-server\Itserv2 printers-agambars-pc124.txt
 \\old-server\custsrv2 printers-castlen-pc521.txt

It looks OK but it can’t be easily formatted as a CSV without tweaking each line to add a few more commas and extra column names. I actually want it to look more like this

"Print Server","Printer","Username","Client"
 "old-server","Itserv2","billg","pc287
 "old-server","acc1","billg","pc287"
 "old-server","custsrv3","bobm","pc233"

Once it started looking like that I could save it as a CSV and start manipulating it. At first I started doing this manually by opening the file in Notepad++ and doing a search and replace on it but then I realised that PowerShell could do the boring repetitive bit for me 😀

  • $File | foreach-object {
    • This section will loop through every line in the $File object held in memory
    • $_.line = $_.line.replace(“\\”,””)
      • I didn’t need the \\ before the server names so the line above replaces it with a comma, as I also want this to be one column in my final CSV
    • $_.line = $_.line.replace(“\”,”%”)
      • I don’t need the trailing \ after the server name but, instead of just removing it, I’ve used a % so I can easily find and replace the character later on
    • $_.filename = $_.filename.replace(“printers-“,””)
      • This bit trims the printers- text off from the filename, replacing it with a comma to separate the data properly
  • $File | export-csv -path $CSVFilePath -NoTypeInformation

This gets me part of the way. I use the export-csv cmdlet above to create a CSV file out of all the lines of text. However, if I tired opening it up in Excel it would still file to convert the text into the columns I wanted. Also, I need to replace the first line of the CSV file to have correctly named and additional column headers

  • $csv = Get-Content -path $CSVFilePath
    • The Get-Content cmdlet will get all of the CSV file we just created and store it in memory
  • $csv = $csv -replace “Line`”,`”Filename“,”Print Server`”,`”Printer`”,`”Username`”,`”Client
    • This replaces the first line of the CSV so the columns get named properly. It looks a bit confusing as the CSV file uses quotation marks as text qualifiers. I’ve coloured it above to help you see what is getting changed. The backtick (` key next to the number 1 on a UK keyboard) escapes the quotation mark so it literally inserts a quote into the string rather than symbolising the end of the command
  • $csv = $csv -replace “%“,”`”,`”
    • Similarly to the previous line, I’m now replacing all the % characters I put in manually with a bit of text that will separate the data properly
  • $csv | Out-File $CSVFilePath -Encoding utf8
    • The Out-File cmdlet then saves the new content back, overwriting the original CSV file. You many have thought, like I did, that you would need to use the Export-CSV cmdlet but that actually messes up the structure by keeping the original quotation marks in.
    • The -Encoding parameter needs to be set to utf8 to ensure the file is recognised as a CSV file not just plain text
  • Invoke-Item $CSVFilePath
    • The final command opens the CSV for viewing. As my default CSV viewer is Excel, I can see it has properly formatted the columns so I can now start getting soe info from the data straight away

Conclusion

As with most scripting, this is only one way to skin the cat. I was really pleased that PowerShell and a bit of clever thinking saved me the chore of manually moving text around. The script runs so quickly and therefore saves me a bit of time to concentrate on the task at hand! This is also another useful feature that you could help your end-users with their daily tasks. How many of them are wasting time on properly formatting text so it can be fed into various systems? Let me know in the comments if you find this useful or have any better solutions in your toolbox

Advertisements

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s