Copying Log Files to Excel with Powershell
At work we use a third party hard drive sanitation program to wipe hard drives for our internal and external customers. Upon completion of the wipe process, the program generates tilde delimited .txt files and moves them to a shared folder on a log server. Each log contains 88 fields of data and as you can imagine are not very easy to read. I was asked to make the logs more presentable for our customers to read during audits. Being there are hundreds of logs created daily my first thought was to script the solution using Microsoft Powershell's Import-CSV and Export-CSV cmdlets to convert the logs to .csv files so they can be opened in Excel without having to use Excel's import wizard. This can be accomplished from the root of the share containing the logs as follows:
gci *.txt | ForEach-Object { (import-csv $_ -delimiter "~") | Export-Csv $_ -NoTypeInformation }
gci *.txt | rename-item -newname { $_.name -replace ".txt",".csv" }
Here we are listing all .txt files in the current working directory and piping the output to the ForEach-Object cmdlet to loop through each file and import to .csv using "~" as the delimiting character to separate each field. Notice "Import-Csv" is in parenthesis. This instructs Powershell to import every object in the pipeline to csv first before sending any of the output to the Export-Csv cmdlet. Without the parenthesis Powershell will attempt to output an object in the pipeline at the same time it's trying to input an object to the Export-Csv cmdlet which ends up causing problems. It's worth mentioning in case your not familiar, $_ is Powershell's pipeline variable. It represents every object as it's passed through the pipeline. In this case every .txt file one after another as it's passed through the pipeline to Export-Csv. I used the -NoTypeInformation parameter to eliminate the data Type information that ended up being written to the .csv files after conversion. Here is an example of a .csv opened in Excel without using the -NoTypeInformation parameter. The PS Type info is visible in the first row which of course we don't want.
So far all is well and good. We have converted all tilde delimited .txt files to comma separated fields and renamed every file by using the -replace option on the .name property of each object to rename the files as .csv. However if you look again at the .csv opened in the graphic above you will notice the column width is not autofit by default. Unfortunately there is no way to correct this while remaining a .csv file so we must look to alternative methods. I first tried to create an Excel.Application ComObject using Powershells New-Object cmdlet to automate opening each .csv in Excel setting "Auto Fit Column Width" and saving the file as .xls but found I couldn't do this without Excel formatting certain fields in undesired ways. Additionally I kept getting the save-as pop up when closing the ComObject even though I had specified the saveas($saveName)method. After a little research I found out you had copy the contents of the .csv file to excel.application ComObject. Again, rather than performing this on only one file I had to process multiple .csv files at once requiring another foreach loop as follows:
Write-Host -ForegroundColor green "Copying contents of CSV's to excel workbooks."
$csvs = gci "\\pathToShare\DR\$loc\*.csv"
foreach ($csv in $csvs) {
$processes = Import-Csv -Path $csv
$excel = New-Object -ComObject excel.application
$excel.visible = $false
$workbook = $excel.workbooks.add()
$excel.cells.item(1,1) = "Computer ID"
$excel.cells.item(1,2) = "Custom field"
$excel.cells.item(1,3) = "Date/Time"
$excel.cells.item(1,4) = "Drive #"
$excel.cells.item(1,5) = "Vendor"
$excel.cells.item(1,6) = "Drive Model"
$excel.cells.item(1,7) = "Drive Serial"
}
Here we are not quite copying any data from the .csv file but rather setting up the headers for the spreadsheet. $excel.cells.item(1,2) = "Custom field" for example will create the field "Custom field" in row 1 of column B and so on. We then move on to copying data from the .csv to each corresponding field like this:
$i = 2
foreach ($process in $processes) {
$excel.cells.item($i,1) = $process."Computer ID"
$excel.cells.item($i,2) = $process."Custom field"
$excel.cells.item($i,3) = $process."Date/Time"
$excel.cells.item($i,4) = $process."Drive #"
$excel.cells.item($i,5) = $process."Drive Model"
$excel.cells.item($i,6) = $process."Vendor"
$excel.cells.item($i,7) = $process."Drive Serial"
}
We can begin copying data from our .csv objects stored in the $processes variable by running a foreach loop and specifying the desired field by referring to each object's properties in corresponding order to the fields we set up earlier in the workbook. Then we adjust the column width of the workbook by calling the autofit() method:
$workbook.activesheet.usedrange.entirecolumn.autofit()
Now we save each file as .xlsx. At first I experienced some difficulties saving each item in the batch while retaining the original name but figured out I could use a similar method used earlier of renaming the files from log.txt to log.csv. That is by replacing the filetype extension of each objects .name property with .xlsx.
$saveName = $csv -replace '.csv','.xlsx'
After saving our work it's time to close the workbook, quit Excel, and clean up memory:
$saveName = $csv -replace '.csv','.xlsx' $workbook.saveas($saveName) $excel.workbooks.close( } $excel.Quit() Remove-Variable -Name excel rm "\\pathToShare\DR\$loc\*.csv" Write-Host -ForegroundColor green "The script is finished running.`nPress 'Enter' to exit." Read-Host [gc]::collect() [gc]::WaitForPendingFinalizers()
Here is the completed script:
$checkPath = Test-Path X:
if ($checkPath -eq "TRUE") {
$loc = Read-Host "Enter folder name of log files to be processed beginning with a backslash: "
Set-Location "\\pathToShare\DR\$loc"
$backups = (gci *.txt).Count
gci *.txt | ForEach-Object { Copy-Item $_ "$($_).bak" }
Move-Item *.bak "\\pathToShare\DR\logs_backup"
$backupFolder = "\\pathToShare\DR\logs_backup"
Write-Host -ForegroundColor green "Backed up $backups original log files to $backupFolder."
sleep -Seconds 3
Write-Host -ForegroundColor green "Converting log files to CSV."
gci *.txt | ForEach-Object { (import-csv $_ -delimiter "~") | Export-Csv $_ -NoTypeInformation }
gci *.txt | rename-item -newname { $_.name -replace ".txt",".csv" }
}
else {
Write-Host -ForegroundColor red "Path does not exist.`nPlease check your path and retry the script."
Read-Host
}
Write-Host -ForegroundColor green "Copying contents of CSV's to excel workbooks."
$csvs = gci "\\pathToShare\DR\$loc\*.csv"
foreach ($csv in $csvs) {
$processes = Import-Csv -Path $csv
$excel = New-Object -ComObject excel.application
$excel.visible = $false
$workbook = $excel.workbooks.add()
$excel.cells.item(1,1) = "Computer ID"
$excel.cells.item(1,2) = "Custom field"
$excel.cells.item(1,3) = "Date/Time"
$excel.cells.item(1,4) = "Drive #"
$excel.cells.item(1,5) = "Vendor"
$excel.cells.item(1,6) = "Drive Model"
$excel.cells.item(1,7) = "Drive Serial"
$excel.cells.item(1,8) = "Drive Size"
$excel.cells.item(1,9) = "Action Result"
$excel.cells.item(1,10) = "Action Duration"
$excel.cells.item(1,11) = "User name"
$excel.cells.item(1,12) = "Action"
$excel.cells.item(1,13) = "Number of wipes"
$excel.cells.item(1,14) = "Drive Errors"
$excel.cells.item(1,15) = "Dirty Sectors"
$excel.cells.item(1,16) = "MotherBoard Vendor"
$excel.cells.item(1,17) = "Motherboard Product"
$excel.cells.item(1,18) = "CPU 1"
$excel.cells.item(1,19) = "CPU 2"
$excel.cells.item(1,20) = "CPU 3"
$excel.cells.item(1,21) = "CPU 4"
$excel.cells.item(1,22) = "RAM"
$excel.cells.item(1,23) = "NIC 1"
$excel.cells.item(1,24) = "NIC 2"
$excel.cells.item(1,25) = "NIC 3"
$excel.cells.item(1,26) = "NIC 4"
$excel.cells.item(1,27) = "Optical Drive 1"
$excel.cells.item(1,28) = "Optical Drive 2"
$excel.cells.item(1,29) = "Optical Drive 3"
$excel.cells.item(1,30) = "Optical Drive 4"
$excel.cells.item(1,31) = "Hard Drive 1"
$excel.cells.item(1,32) = "Hard Drive 2"
$excel.cells.item(1,33) = "Hard Drive 3"
$excel.cells.item(1,34) = "Hard Drive 4"
$excel.cells.item(1,35) = "Hard Drive 5"
$excel.cells.item(1,36) = "Hard Drive 6"
$excel.cells.item(1,37) = "Hard Drive 7"
$excel.cells.item(1,38) = "Hard Drive 8"
$excel.cells.item(1,39) = "Hard Drive 9"
$excel.cells.item(1,40) = "Hard Drive 10"
$excel.cells.item(1,47) = "Video Card 1"
$excel.cells.item(1,48) = "Video Card 2"
$excel.cells.item(1,49) = "Multimedia Card 1"
$excel.cells.item(1,50) = "USB1 Ports"
$excel.cells.item(1,51) = "USB2 Ports"
$excel.cells.item(1,52) = "Motherboard Test"
$excel.cells.item(1,53) = "Processor Test"
$excel.cells.item(1,54) = "Memory Test"
$excel.cells.item(1,55) = "Display Test"
$excel.cells.item(1,56) = "Keyboard Test"
$excel.cells.item(1,57) = "Computer Vendor"
$excel.cells.item(1,58) = "Computer Model"
$excel.cells.item(1,59) = "Computer Serial"
$excel.cells.item(1,60) = "HPA Detected"
$excel.cells.item(1,61) = "HPA Removed"
$excel.cells.item(1,62) = "DCO Detected"
$excel.cells.item(1,63) = "DCO Removed"
$excel.cells.item(1,64) = "Secure Erase Passes"
$excel.cells.item(1,65) = "Secure Erased Enhanced Passes"
$excel.cells.item(1,66) = "user1"
$excel.cells.item(1,67) = "user1-name"
$excel.cells.item(1,68) = "user2"
$excel.cells.item(1,69) = "user2-name"
$excel.cells.item(1,86) = "Action Start Time"
$excel.cells.item(1,87) = "Action End Time"
$excel.cells.item(1,88) = "USB3 Ports"
$i = 2
foreach ($process in $processes) {
$excel.cells.item($i,1) = $process."Computer ID"
$excel.cells.item($i,2) = $process."Custom field"
$excel.cells.item($i,3) = $process."Date/Time"
$excel.cells.item($i,4) = $process."Drive #"
$excel.cells.item($i,5) = $process."Drive Model"
$excel.cells.item($i,6) = $process."Vendor"
$excel.cells.item($i,7) = $process."Drive Serial"
$excel.cells.item($i,8) = $process."Drive Size"
$excel.cells.item($i,9) = $process."Action Result"
$excel.cells.item($i,10) = $process."Action Duration"
$excel.cells.item($i,11) = $process."User name"
$excel.cells.item($i,12) = $process."Action"
$excel.cells.item($i,13) = $process."Number of wipes"
$excel.cells.item($i,14) = $process."Drive Errors"
$excel.cells.item($i,15) = $process."Dirty Sectors"
$excel.cells.item($i,16) = $process."MotherBoard Vendor"
$excel.cells.item($i,17) = $process."MotherBoard Product"
$excel.cells.item($i,18) = $process."CPU 1"
$excel.cells.item($i,19) = $process."CPU 2"
$excel.cells.item($i,20) = $process."CPU 3"
$excel.cells.item($i,21) = $process."CPU 4"
$excel.cells.item($i,22) = $process."RAM"
$excel.cells.item($i,23) = $process."NIC 1"
$excel.cells.item($i,24) = $process."NIC 2"
$excel.cells.item($i,25) = $process."NIC 3"
$excel.cells.item($i,26) = $process."NIC 4"
$excel.cells.item($i,27) = $process."Optical Drive 1"
$excel.cells.item($i,28) = $process."Optical Drive 2"
$excel.cells.item($i,29) = $process."Optical Drive 3"
$excel.cells.item($i,30) = $process."Optical Drive 4"
$excel.cells.item($i,31) = $process."Hard Drive 1"
$excel.cells.item($i,32) = $process."Hard Drive 2"
$excel.cells.item($i,33) = $process."Hard Drive 3"
$excel.cells.item($i,34) = $process."Hard Drive 4"
$excel.cells.item($i,35) = $process."Hard Drive 5"
$excel.cells.item($i,36) = $process."Hard Drive 6"
$excel.cells.item($i,37) = $process."Hard Drive 7"
$excel.cells.item($i,38) = $process."Hard Drive 8"
$excel.cells.item($i,39) = $process."Hard Drive 9"
$excel.cells.item($i,40) = $process."Hard Drive 10"
$excel.cells.item($i,47) = $process."Video Card 1"
$excel.cells.item($i,49) = $process."Multimedia Card 1"
$excel.cells.item($i,50) = $process."USB1 Ports"
$excel.cells.item($i,51) = $process."USB2 Ports"
$excel.cells.item($i,52) = $process."Motherboard Test"
$excel.cells.item($i,53) = $process."Processor Test"
$excel.cells.item($i,54) = $process."Memory Test"
$excel.cells.item($i,55) = $process."Display Test"
$excel.cells.item($i,56) = $process."Keyboard Test"
$excel.cells.item($i,57) = $process."Computer Vendor"
$excel.cells.item($i,58) = $process."Computer Model"
$excel.cells.item($i,59) = $process."Computer Serial"
$excel.cells.item($i,60) = $process."HPA Detected"
$excel.cells.item($i,61) = $process."HPA Removed"
$excel.cells.item($i,62) = $process."DCO Detected"
$excel.cells.item($i,63) = $process."DCO Removed"
$excel.cells.item($i,64) = $process."Secure Erase Passes"
$excel.cells.item($i,65) = $process."Secure Erase Enhannced Passes"
$excel.cells.item($i,66) = $process."user1"
$excel.cells.item($i,67) = $process."user1-name"
$excel.cells.item($i,68) = $process."user2"
$excel.cells.item($i,69) = $process."user2-name"
$excel.cells.item($i,86) = $process."Action Start Time"
$excel.cells.item($i,87) = $process."Action End Time"
$excel.cells.item($i,88) = $process."USB3 Ports"
}
$workbook.activesheet.usedrange.entirecolumn.autofit()
$saveName = $csv -replace '.csv','.xlsx'
$workbook.saveas($saveName)
$excel.workbooks.close()
}
$excel.Quit()
Remove-Variable -Name excel
rm "\\pathToShare\DR\$loc\*.csv"
Write-Host -ForegroundColor green "The script is finished running.`nPress 'Enter' to exit."
Read-Host
[gc]::collect()
[gc]::WaitForPendingFinalizers()