Import CSV file with 3 columns into three separate arrays

Welcome Forums General PowerShell Q&A Import CSV file with 3 columns into three separate arrays

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
1 month, 2 weeks ago.

  • Author
    Posts
  • #120594

    Participant
    Points: 20
    Rank: Member

    I have a simple CSV file with 3 columns, and a header for row#1 (Name, Department, Region). I run the following command in Powershell but am not getting the values for each column into their respective arrays. I was hoping to import all cell values under Name column into a $name array, $department array and $region array. So far no luck.

    $csv = Import-CSV 'c:\users\myname\documents\resultdata.csv'

    $name = $csv.Name

    $department = $csv.Department

    $region = $csv.Region

    When i run the command $csv[2], it shows me the header and then the 3 values for Name, department and region. Something like this below. But if i run just $name, it does not show me anything, i was expecting a list of all names one line at a time. What am i doing wrong please? Thanks a bunch

    Name              Department         Region

    _ _ _               _ _ _ _ _ _          _ _ _ _

    James             Engineering         Arizona

  • #120595

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    What version of Powershell are you running? $csv.Name is an implicit loop, which was introduced in version 3. Another simple method that should work regardless of the version is -ExpandProperty:

    $obj = @()
    $obj += [pscustomobject]@{
        Name = 'Rob'
        Department = 'IT'
        Region = 'Florida'
    }
    
    $obj += [pscustomobject]@{
        Name = 'James'
        Department = 'Engineering'
        Region = 'Arizona'
    }
    
    #Version 3 or higher
    $obj.Name
    
    #All versions
    $obj | Select-Object -ExpandProperty Name
    
    #Another option is an explicit loop
    $obj | foreach{$_.Name}
    

    Output:

    PS C:\Users\Rob> $obj.Name
    
    Rob
    James
    
    PS C:\Users\Rob> $obj | Select-Object -ExpandProperty Name
    Rob
    James
    
    PS C:\Users\Rob> $obj | foreach{$_.Name}
    
    Rob
    James
    
  • #120603

    Participant
    Points: 20
    Rank: Member

    Hi Rob. Thanks for your reply. I'm running PS version 5.1. I'm a bit confused from your reply. I have all the data already in the CSV file and would just like to import the 3 columns into 3 unique arrays in Powershell. Your code seems to show them being manually added one row at a time? Please correct me if i'm wrong. Can you tell me what i'm doing wrong not to get the 3 columns into 3 arrays? Thank you again sir.

  • #120606

    Participant
    Points: 20
    Rank: Member

    Nevermind 🙂 I figured out what you did. I entered $csv | select -expandproperty Name and it worked. Thank you Rob

     

    Hi Rob. Thanks for your reply. I'm running PS version 5.1. I'm a bit confused from your reply. I have all the data already in the CSV file and would just like to import the 3 columns into 3 unique arrays in Powershell. Your code seems to show them being manually added one row at a time? Please correct me if i'm wrong. Can you tell me what i'm doing wrong not to get the 3 columns into 3 arrays? Thank you again sir.

  • #120649

    Participant
    Points: 264
    Helping Hand
    Rank: Contributor

    would just like to import the 3 columns into 3 unique arrays in Powershell

    Powershell does a lot of things implicitly for you without asking for. 😉
    If you import some data from a CSV file you actually already have three single arrays:

    $Source = @'
    Name,Department,Region
    James,Engineering,Arizona
    Paul,Marketing,Florida
    Ann,Human Ressources,New York
    Chuck,CEO,San Francisko
    '@
    $CSV = $Source | ConvertFrom-Csv

    That code created some structured data in form of a table with 3 columns and 4 data sets. (looks like this)

    PS:>$CSV
    
    Name  Department       Region       
    ----  ----------       ------       
    James Engineering      Arizona      
    Paul  Marketing        Florida      
    Ann   Human Ressources New York     
    Chuck CEO              San Francisko

    You can access each single data set by addressing it with its index like this:

    $CSV[2]

    Result will be the third data set (index starts with 0)

    PS:>$CSV[2]
    
    Name Department       Region  
    ---- ----------       ------  
    Ann  Human Ressources New York

    And ... like you already did you can access each single column by its name like this:

    $CSV.Name

    ... and the result would be this:

    PS:>$CSV.Name
    James
    Paul
    Ann
    Chuck
  • #120831

    Participant
    Points: 20
    Rank: Member

    Thanks much for your reply as well Olaf!

You must be logged in to reply to this topic.