Powershell script to remove white-spaces on a CSV file and convert the time entry to 24-hour format

Posted on Updated on

#Here is a script to remove white-spaces on a CSV file and convert the time entry to 24-hour format.

$fileInput = <location and filename of the input file>

$fileOutput = <location and filename of the output file>

$delimeter = ‘,’

$header1 = ‘#Phone,Original Record,Client ID,Sub-Campaign,Device Attempted,Device Locale,Date Time,Last State,Last State Seconds,Call Seconds,Call Result,Best Contact’

# check if input file exist

if (!(test-path $fileinput)) {

write-host “File not found” exit

}

# check if output file already exist, delete file if true

if (test-path $fileoutput) {

Remove-Item $fileoutput

}

# import and build CSV in memory

#note single word IDs work better, I.E phone otherwise they need to be named with quotes

$header = “Phone”,”Original Record”,”Client ID”,”Sub-Campaign”,”Device Attempted”,”Device Locale”,”Date Time”,”Last State”,”Last State Seconds”,”Call Seconds”,”Call Result”,”Best Contact”

$csv = import-csv -path $fileInput -header $header

$csvTest = @();

# Display function for military time

# Input String

# Output String

function DisplayMilitaryDateTime( $InputDateString ){

[System.DateTime]$convertedDate = [System.DateTime]::Parse( $InputDateString );

#5/16/2014 13:00:02

return $convertedDate.ToString(“MM/dd/yyyy HH:mm:ss”);

}

Add-Content -Path $fileOutput -Value $header1

# all the clean and conversion of data would be here

foreach($line in $csv){

$outLine = “”;

# cleanup data and convert it

$outLine += $line.Phone.Trim(” “) + $delimeter ;

$outLine += $line.”Original Record”.Trim(” “)+ $delimeter;

$outLine += $line.”Client ID”.Trim(” “)+ $delimeter;

$outLine += $line.”Sub-Campaign”.Trim(” “)+ $delimeter;

$outLine += $line.”Device Attempted”.Trim(” “)+ $delimeter;

$outLine += $line.”Device Locale”.Trim(” “)+ $delimeter;

#note the function call

$outLine += DisplayMilitaryDateTime( $line.”Date Time”.Trim(” “) ) + $delimeter;

#this is added so another ‘,’ is added

$outLine += $delimeter; $outLine += $line.”Last State”.Trim(” “) + $delimeter;

$outLine += $line.”Last State Seconds”.Trim(” “) + $delimeter;

$outLine += $line.”Call Seconds”.Trim(” “) + $delimeter;

$outLine += $line.”Call Result”.Trim(” “) + $delimeter;

$outLine += $line.”Best Contact”.Trim(” “);

 

# manually build the output line and append it to the file

Add-Content -Path $fileOutput -Value $outLine }

 

#finally export it

# $csv | Export-Csv -Path $fileOutput -Delimiter ‘,’ -Encoding UTF8 -NoTypeInformation

 

#delete input file after successful conversion

Remove-Item $fileinput

Advertisements

Leave a Reply

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