PowerShell string manipulation

2017-12-08T14:28:48+00:00 August 18th, 2010|PowerShell|0 Comments

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
    }

Leave A Comment

like what you see? 

Sign-up to our newsletter and never miss out on the latest blogs, events and tech news from the world of risual
SUBSCRIBE!
Give it a try, you can unsubscribe anytime.