Opticon OPN2001 – CSV file data manipulation using PowerShell

CSV file RAW data should be like this =

BarCode Number, Time of Scan, Date of Scan
20120711001, 21:00:00, 2012-07-11
321530, 21:01:01, 2012-07-11
123456, 21:02:02, 2012-07-11
20120711001, 22:00:00, 2012-07-11
123456, 22:01:01, 2012-07-11
321530, 22:02:02, 2012-07-11

After the table modification with the PowerShell script, it should result as follow =

Mission ID, Person ID, Start Time, Start Date, Stop Time, Stop Date
20120711001, 20120711001, 21:00:00, 2012-07-11, 22:00:00, 2012-07-11
20120711001, 321530, 21:01:01, 2012-07-11, 22:01:01, 2012-07-11
20120711001, 123456, 21:02:02, 2012-07-11, 22:02:02, 2012-07-11

The basic PowerShell cmdlet to read and write CSV file are:

  • Import-Csv Cmdlet – Read in a Comma-Separated Values File
    Import-Csv c:\scripts\test.txt
  • Export-Csv Cmdlet – Saving Data as a Comma-Separated Values File
    Export-Csv c:\scripts\test.txt
  • Filter result before Importing
    Import-Csv c:\scripts\test.txt | Where-Object {$_.department -eq "Finance"}
  • Get-Content Cmdlet – Reading a Text File
    Get-Content c:\scripts\test.txt

    Blog example to manipulate data inside PowerShell:

  • parse-textObject
  • Typecasting imported CSV data
    import-csv test.csv | set-type -type_hash @{id=[int];workingset=[int];cpu=[float]} | ogv
  • Append column data
    Import-Csv d:\temp\so\csv1.txt -Delimiter ‘;’ | % { $_.col3 = ‘append\’ +$_.col3; $_ } | Export-Csv d:\temp\so\csv2.txt -Delimiter ‘;’
    or
    Import-Csv -Delim ';' cols.csv | Foreach {$_.col3 = "prepend\$($_.col3)";$_} | Export-Csv cols2.csv -Delim ';' -NoTypeInformation
  • Replacing Data in .CSV Field
    $SCRIPT:IMP = Import-CSV C:\Tools\PSScripts\ShellAuditSubSet.csv
    $IMP | foreach-object { $_.Message
    $CleanReturn = (CleanData ($_.Message))
    if($Messy -cne "")
    {
    $_.Message = $CleanReturn
    Write-Host "MESSAGE DATA IS " + $_.Message
    }
    }
  • Manipulate array data gathered from import-csv
  • 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