Read csv file that has | between column

This topic contains 6 replies, has 4 voices, and was last updated by  Ron 12 months ago.

  • Author
    Posts
  • #60154

    nidhin ck
    Participant

    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 |"

  • #60162

    Max Kozlov
    Participant

    Get-Help Import-Csv -Parameter Delimiter

    • #60163

      nidhin ck
      Participant

      -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

    • #60169

      nidhin ck
      Participant

      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*"}
    • #60178

      Max Kozlov
      Participant

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

    • #60186

      Ron
      Participant

      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
  • #60171
    $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

You must be logged in to reply to this topic.