Author Posts

December 14, 2016 at 5:17 am

Hi Experts,

I need to extract AD Account name from an csv file that is in below format. In this csv file all the 3 lines has an "AD Account" name nidhinck. Whats the command i need to use to pull only the AD Account column from this csv file

3 Employee Terminations Approved and Processed by 2016-12-07

They are as follows:

"Employee #| Legacy ID| Cost Center| Job Title| Manager ID| Customer Dimention ID| Customer Dimention Description| AD Account| Last Name| First Name| Email Address| Hire Date| Term Date| Business Site| Termination Reason "
"123 |199 |800 PURCHASING [Local Cost Center] |Materials Analyst |6852 | | |nidhinck |ck |nidhin |nidhinck@outlook.com |2000-09-25 |2016-12-06 |US South Carolina Columbia |"
"124 |19505 |900 ENGINEERING OPERATIONS [Local Cost Center] |Test Technician |6852 | | |nidhinck |ck |nidhin |nidhinck@outlook.com |2000-09-25 |2016-12-06 |US South Carolina Columbia |"
"125 |19332 |100 ENGINEERING OPERATIONS [Local Cost Center] |Test Technician |6852 | | |nidhinck |ck |nidhin |nidhinck@outlook.com |2000-09-25 |2016-12-06 |US South Carolina Columbia |"

December 14, 2016 at 6:52 am

Get-Help Import-Csv -Parameter Delimiter

December 14, 2016 at 8:07 am

-delimiter will work only if i remove the first 4 lines form this csv file. Is there any way to remove or discard the first 4 lines and process the rest

December 14, 2016 at 8:30 am

Got it

 $a = (Get-Content "D:\Scripts\PwrShell\20161207-01.csv" | Select-Object -Skip 4) | Set-Content "D:\Scripts\PwrShell\Nidhintest.csv"
$b = Import-Csv "D:\Scripts\PwrShell\Nidhintest.csv" -Delimiter "|"
$b | where {$_."AD Account" -like "nidhinck*"}

December 14, 2016 at 8:33 am

$X = Import-CSV  -delimiter "|"
$X | Select AD_Account 

I think table headers should not contain spaces so you can transform it into an object and play with it :).
Let me know if this helps,
Sergiu

December 14, 2016 at 1:44 pm

Instead of saving you file to other temp. file you can use ConvertFrom-Csv with the same parameter

December 14, 2016 at 2:03 pm

You also need to strip the first and last double quote. This is ugly but should work.

$csv=get-content file.csv | select -skip 4 | foreach {$x=$_.trim();$x.substring(1,$x.length-2)} | convertfrom-csv -Delimiter "|"

$csv | ft -auto