Read csv file that has | between column

Tagged: 

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Ron Ron 5 months, 2 weeks ago.

  • Author
    Posts
  • #60154
    Profile photo of nidhin ck
    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
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    Get-Help Import-Csv -Parameter Delimiter

    • #60163
      Profile photo of nidhin ck
      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
      Profile photo of nidhin ck
      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
      Profile photo of Max Kozlov
      Max Kozlov
      Participant

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

    • #60186
      Profile photo of Ron
      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.