This PowerShell script is part of a group of CSV Tools I have written. The script is a simple one that simply removes the last line of an inputted CSV file. It can obtain the CSV file either by being directly passed the name of a CSV file, by being given a text file list or finally by searching a directory for all CSV files.
I wrote this particular script in conjunction with my Mass NTFS Compression Tool as I was using a large Treesize report of over 3 million lines. I didn’t particularly fancy throwing 3 million lines at a script in one hit. I used a piece of software called GSplit to split the CSV file into multiple files, however due to the way the software works, it adds an extra line at the end of the file to merge the split copies back into the original at a later date. I had no need for this functionality and, while it wasn’t a major problem, I didn’t want my other scripts to throw errors because they didn’t understand this last line.
Usage: .\CSV-RemoveLastLine.ps1 [-CSV path\to.csv] [-List path\to\csvlist.txt] [-Dir path\to\directory]
Download CSV Tool: Remove Last Line v1.1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
################################################################################################# # # # CSV Tool: Remove Last Line v1.1 # # # # Written by: Mike Oldfield # # Date: 31/05/2016 # # # # This PS1 simply removes the last line of a specified CSV file or list of CSV files # # # # Usage: .\CSV-RemoveLastLine.ps1 [-CSV path\to.csv] [-List path\to\csvlist.txt] # # [-Dir path\to\directory] # # # ################################################################################################# ############################# # # # PARAMETERS # # # ############################# # Set some avaialble paramters. This bit must come first. param( [string]$CSV, # -CSV: Specifies the CSV file you want to edit [string]$List, # -List: Specifies a list of CSV files you want to edit [string]$Dir # -Dir: Specifies a directory to search for CSV files ) ############################# # # # VARIABLES # # # ############################# $scriptVer = "v1.1" # What version of the script is this? Used for header info $scriptAuth = "Mike Oldfield" # Who wrote the script? $scriptLastUpdate = "02/06/2016" # When was the script last updated? ############################# # # # HEADER # # # ############################# # Write a pretty header Write-Host "`r`n##################################################`r`n" -ForegroundColor darkcyan -NoNewLine Write-Host "##### " -ForegroundColor darkgray -NoNewLine Write-Host "CSV Tool: Remove Last Line $scriptVer " -ForegroundColor gray -NoNewLine Write-Host "#####`r`n##### " -ForegroundColor darkgray -NoNewLine Write-Host "Written by: $scriptAuth " -ForegroundColor gray -NoNewLine Write-Host " #####`r`n##### " -ForegroundColor darkgray -NoNewLine Write-Host "Last Updated: $scriptLastUpdate " -ForegroundColor gray -NoNewLine Write-Host " #####`r`n" -ForegroundColor darkgray -NoNewLine Write-Host "##################################################`r`n" -ForegroundColor darkcyan ############################# # # # SCRIPTY BIT # # # ############################# if ($CSV) { # Tell us that we're removing the last line... Write-Host "Removing last line from $CSV ... " -NoNewLine # Open up the CSV file $CSVContent = Get-Content $CSV # Remove the last line $CSVContent = $CSVContent[0..($CSVContent.count - 2)] # Write the CSV file back to the original path. Use UTF8 encoding $CSVContent | Out-File $CSV -Encoding UTF8 # Tell us we're done Write-Host "OK!`r`n" -ForegroundColor green # Stop processing any more Exit } else { # Check if we asked to search a directory for CSV files if ($Dir){ # If we did, grab the name of the directory and search for any files with a .csv extension # Only retrieve the file names, we don't need the full path. $CSVList = Get-ChildItem "$Dir" -Force -Filter "*.csv" -ErrorAction Stop | select name } elseif ($List) { # If we did, grab the list of files and open it up $CSVList = Get-Content $List } else { # Not sure how we've made it this far! Write-Host "Something is missing! We didn't find -CSV, -Dir or -List?`r`n" -ForegroundColor red # Kill the script as we can't do anything now... Exit } # Count the number of CSV files found $CSVCount = $CSVList | measure # For each CSV file found in the directory/list... foreach ($CSVFile in $CSVList) { # Tell us that we're removing the last line... Write-Host "Removing last line from $($CSVFile.Name) ... " -NoNewLine # Check if we did a directory listing earlier if ($Dir) { # If so, quickly add the path to the front of the CSV file name $CSVFile.Name = "$Dir\$($CSVFile.Name)" } # Open up the CSV file $CSVContent = Get-Content $($CSVFile.Name) # Remove the last line $CSVContent = $CSVContent[0..($CSVContent.count - 2)] # Write the CSV file back to the original path. Use UTF8 encoding $CSVContent | Out-File $($CSVFile.Name) -Encoding UTF8 # Tell us we're done Write-Host "OK!" -ForegroundColor green } # Display the counted number of CSV files on the screen Write-Host "`r`nTotal Files:" $CSVCount.Count "`r`n" # Kill the script in case someone asked for loads of different things Exit } |
