PowerShell string manipulation
Whilst trying to automate some reporting automation and analysis , I overcome a requirement to reformat a csv ( loosest sense of the term) so that PowerShell will process it. The following is a useful demonstration of how to manipulate text data and reformat it, also the use of regex (Regular Expression) in PowerShell to identify the current days file.
This has been tested against PowerShell 1.0 on Microsoft Windows Server 2003
##
# A csv Files report is created but you are unable to load it as a csv File due to irregular formatting of the report.
# this will serve as a good example of string manipulation.
#
# Issues with csv file
# remove top 6 lines, header information not required.
# headers contain , but are not quoted, so you now have more columns than data
# trailing , stop PowerShell loading csv as you can not have a blank header
# the data is includes a % sign. this then stop it being a number when imported into SQL / Excel
#———————————————————–
#
#
$tmpfile = “temp.csv”
$mydat = (get-date ((get-date).adddays(-1))-uformat “%Y%m%d”) # get yesterdays date in the correct format
$filter = [regex] “^Daily-View.*$mydat[0-9]{6}.csv$” # Use regex to find today’s file
$csvfile = Get-ChildItem -Path “c:Joetest*.csv” | Where-Object {$_.Name -match $filter} # return 1 filename# use PowerShell to drop the first 6 lines of unwanted text at the beginning of the file
#
$csvContent = get-content $csvfile$csvContent | select -last ($csvContent.count -6) | Out-File $tmpfile
# replace “, %” with ” %”
#
$csvContent = “”
$csvContent = get-content $tmpfile
$csvContent[0] = $csvContent[0].replace(“, %”,” %”)# remove trailing , on header line
#
$csvContent[0] = $csvContent[0].substring(0,($csvContent[0].length)-1) # remove the last char on the line# remove all the “%” signs from the data ( but not the header)
#
$counter = 0
foreach ($line in $csvContent)
{
if ($counter -ne 0) # skip the header line
{
$csvContent[$counter] =$csvContent[$counter].replace(“%”,”")
}
$counter++
}# output the plain txt file
#
$csvContent | out-file $tmpfile# now you can import the file as a csv with no errors
#
$csvdata = import-csv $tmpfile
$csvdata# ————————————–
# now you can do stuff with the data here
# ————————————–# Remove old temp file
#
if (test-path $tmpfile)
{
remove-item -path $tmpfile -force
}
