locked
Powershell read CSV file line by line and only first header RRS feed

  • Question

  • I have a CSV file, which reads like this:

    read, book

    read1, book1

    read2, book2

    I want to read only the first value, before comma one line at time,

    meaning first time it will be

    read

    read1

    read2

    no headers and i want to pass that value to a variable for a if condition.

    I have script it read line by line but it reads both values before coma and after line by line, so instead of having three values i'm ending with 6 values.

    $file = Import-CSV c:\script\server.csv

    $file | ForEach-Object {
            foreach ($property in $_.PSObject.Properties) 
    {        
    $property.Name
    $property.Value
    #replace = $property.Value

    }

        }


    Thursday, April 23, 2015 10:48 AM

Answers

  • # Assuming a CSV as such:
    # Name, Book
    # name1, book1
    # name2, book2
    # name3, book3
    
    $csv = Import-Csv C:\users\Administrator\Desktop\test.csv
    
    # This retrieves *all* 'cells' in the column 'Name' (which happens to be the first column)
    # $csv.Name
    
    # If we want to evaluate one 'cell' at a time (line by line)
    # We just pass it to a ForEach-Object
    # Notice I'm still preserving .Name which means only the column 'Name'
    # is up for evaluation. In this case, the column 'Name' is the first column.
    
    $csv.Name | ForEach-Object {
        # In here, we do whatever want to the 'cell' that's currently in the pipeline
        # For now, let's just output it.
        $_
    
        # And to prove that we processed each 'cell' one at a time, let's also output...
        "Hello World"
    }
    
    # So if this works, we should have the follow
    # Name1
    # Hello World
    # Name2
    # Hello World
    # Name3
    # Hello World

    Here's some code that should help explain this better.

    Notice I saved the contents of the CSV all in memory, so when you're ready to read the second column (if you want to read it that is) you don't need to do Import-Csv again, you just do the same thing I did above, but for the second column $csv.Book.

    Thursday, April 23, 2015 3:34 PM

All replies

  • Assuming that your csv has two (or more) columns, I would advice to use headers.

    That way you can use the header to just call the collumn you want:

    server.csv:

    reading, books

    read, book

    read1, book1,
    read2, book2

    now reading and books are the header names.

    $file = Import-CSV c:\script\server.csv 

    $file.reading will hold what you want. (and can of course be piped)

    Is there a specific reason you don't want headers?


    Thursday, April 23, 2015 11:46 AM
  • Hi,

    Assuming csv file looks like the following one :

    read,book
    read1,book1
    read2,book2

    There are 2 ways to obtain what you expect :

    1. By importing CSV file :
    $file = Import-Csv .\input.csv 
    foreach ($line in $file)
    {
        $line.Read | out-host;
    }

    This operation displays the following output :

    read1
    read2
    1. Without headers :
    $file = Get-Content .\input.csv
    for ($i = 1; $i -lt $file.Length; $i++)
    { 
        ([string]$file[$i]).Split(',')[0] | out-host;
    }

    produces the following output :

    read1
    read2

    Regards,

    Régis

    • Proposed as answer by Régis Lainé Thursday, April 23, 2015 1:09 PM
    Thursday, April 23, 2015 1:09 PM
  • Read first half of line one line at a time:

    Get-Content c:\script\server.csv |%{$_.Split(',')[0]}

    This will read all lines including first line..

    To do the same with CSV you need to supply custom header

    Import-Csv c:\script\server.csv -header Col1,Col2 | %{$_.Col1}

    Are you sure you want to output the first line?


    \_(ツ)_/

    Thursday, April 23, 2015 1:17 PM
    Moderator
  • I can work with headers
    Thursday, April 23, 2015 2:29 PM
  • i need to out put one line at a time, and only the first column
    Thursday, April 23, 2015 2:30 PM
  • If you can work with headers then do so as it will make your life easier.

    with a csv file called server.csv like this:

    headername1, headername2
    read, book
    read1, book1,
    read2, book2

    and this bit of code 

    $file = Import-CSV c:\script\server.csv #output to host, file or directly pipe the command above. foreach($cell in $file.headername1){

    if($cell -eq $something){

    }

    }

    will evaluate the content of each cell to $something.

    This is because Powershell will grab the first row and claim that as a header name.

    So whatever you put in cell A1 in excell will end up as name of the first collumn and its corresponding property (e.g. $file.A1 oor $file.headername1 if you will).


    • Edited by Koen Halfwerk Thursday, April 23, 2015 2:59 PM extended answer
    Thursday, April 23, 2015 2:56 PM
  • You are all still missing the point and the question.  If the first line is a header then jusy use Import. If you want the first line as data then add the header in the import:

    Import-Csv c:\script\server.csv -header col1,col2 | %{$_.Col1}

    This will extract all of the first column values. No need for any fancy wring of many lined scripts. It is just a  normal PS operation.


    \_(ツ)_/

    Thursday, April 23, 2015 3:08 PM
    Moderator
  • # Assuming a CSV as such:
    # Name, Book
    # name1, book1
    # name2, book2
    # name3, book3
    
    $csv = Import-Csv C:\users\Administrator\Desktop\test.csv
    
    # This retrieves *all* 'cells' in the column 'Name' (which happens to be the first column)
    # $csv.Name
    
    # If we want to evaluate one 'cell' at a time (line by line)
    # We just pass it to a ForEach-Object
    # Notice I'm still preserving .Name which means only the column 'Name'
    # is up for evaluation. In this case, the column 'Name' is the first column.
    
    $csv.Name | ForEach-Object {
        # In here, we do whatever want to the 'cell' that's currently in the pipeline
        # For now, let's just output it.
        $_
    
        # And to prove that we processed each 'cell' one at a time, let's also output...
        "Hello World"
    }
    
    # So if this works, we should have the follow
    # Name1
    # Hello World
    # Name2
    # Hello World
    # Name3
    # Hello World

    Here's some code that should help explain this better.

    Notice I saved the contents of the CSV all in memory, so when you're ready to read the second column (if you want to read it that is) you don't need to do Import-Csv again, you just do the same thing I did above, but for the second column $csv.Book.

    Thursday, April 23, 2015 3:34 PM
  • Somebody please read the whole question. Think about it before answering.


    \_(ツ)_/

    Thursday, April 23, 2015 4:55 PM
    Moderator
  • jrv, my point was merely to demonstrate how the columns become properties of an object when imported via Import-Csv and that they can be iterated one at a time using a ForEach-Object (as an example).

    You had already answered the part about the headers which does not appear to be the bit that the OP appears to be having most issues with coming to grasps with, so I didn't feel it important to repeat that.

    Thursday, April 23, 2015 4:59 PM
  • Can i ask you jrv what does % , $_ mean ? 

    I have been reading about this but it seem s that you might know the most straight forward answer.

    Thanks

    Thursday, November 9, 2017 7:59 AM
  • 1..10 | %{$_ % 3}

    0..29 | %{$_ % 5}


    \_(ツ)_/


    Thursday, November 9, 2017 8:08 AM
    Moderator
  • $_ represents the current object on the pipeline – if you want to know why $_ was chosen you'll have to read PowerShell in Action! ... To recap $_ (or $psitem) is used to represent the current object on the pipeline. You can us it in commands that are performing an action on every object on the pipeline

    basicly It means the Item your exploring in your script 

    $csv.Name
    | ForEach-Object
    {
    $_
    }


    each item intern becomes $_ so you can manipulate it in side them squirrely brackets.



    • Edited by Kheg Tuesday, January 22, 2019 7:23 PM
    Tuesday, January 22, 2019 7:22 PM
  • $_ represents the current object on the pipeline – if you want to know why $_ was chosen you'll have to read PowerShell in Action! ... To recap $_ (or $psitem) is used to represent the current object on the pipeline. You can us it in commands that are performing an action on every object on the pipeline

    basicly It means the Item your exploring in your script 

    $csv.Name
    | ForEach-Object
    {
    $_
    }


    each item intern becomes $_ so you can manipulate it in side them squirrely brackets.



    Invalid syntax.


    \_(ツ)_/

    Tuesday, January 22, 2019 9:52 PM
    Moderator
  • than you! it worked
    Monday, June 10, 2019 8:57 PM