none
Read CSV row 1 columns and save them to variables. RRS feed

  • Question

  • Hi, i would like to read data from csv files, only from row 1 and few columns and save them to variables. Now I have done it like this:

    Get-ChildItem -Path C:\path | ForEach-Object -Process {
        $YourContent = Get-Content -Path $_.FullName
        $YourVariable = $YourContent | Select-Object -First 1
        $YourContent | Select-Object -Skip 1 | Set-Content -Path $_.FullName
    }

    Problem is that now my variable prints out  Elvis;867.5390;elvis@geocities.com. So I would like to save each column to own variable. Elvin could be columnA, Number B, mail C. 

    Example what csv could look

    Elvis | 867.5309     | Elvis@Geocities.com
    Sammy | 555.1234     | SamSosa@Hotmail.com

    Monday, October 29, 2018 12:08 PM

Answers

  • $myVariable = Get-ChildItem C:\path\*.xlsx |
        Import-Csv -Delimiter '|' |
        select -First 1 


    \_(ツ)_/

    • Marked as answer by Quiet Mouse Tuesday, October 30, 2018 1:16 PM
    Monday, October 29, 2018 7:36 PM
    Moderator
  • There is a distinct difference between "CSV files" and "any other txt file". You know that, right?

    If it is a proper CSV file you can do it like this:

    $CompleteFileContent = Import-Csv -Path "<Path to you CSV file>" -Delimiter "`t"
    $FirstRow = $CompleteFileContent | Select-Object -First 1
    $ContentWithoutFirstRow = $CompleteFileContent | Select-Object -Skip 1
    $ContentWithoutFirstRow | Export-Csv -Path "<Path to you CSV file>" -Delimiter "`t"
    In this example I assumed you're using a tab as the delimiter in your CSV file.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    Tuesday, October 30, 2018 7:46 AM

All replies

  • If you have real CSV files you should use the according cmdlets like Import-CSV and Export-CSV. That would make your life much easier. ;-)

    And please format your code as code here in the forum. Thanks.

    Get-ChildItem -Path C:\path -Filter *.csv | 
        ForEach-Object -Process {
            $YourContent = Import-Csv -Path $_.FullName 
            $YourVariable = $YourContent | Select-Object -First 1
            $YourContent | Select-Object -Skip 1 | Export-Csv -Path $_.FullName -Force
        }


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Monday, October 29, 2018 12:15 PM
  • Looks like he's using semi-colons as the separator. If that's the case, add "-Delimiter ';'" to the Import-CSV cmdlet.

    Also, shouldn't the "$YourVariable" variable be what's piped to the Export-CSV? And what about that "-Skip 1"? Is the Select-Object even necessary if $YourVariable is being exported? There should only be one "row" in that.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, October 29, 2018 7:04 PM
  • $myVariable = Get-ChildItem C:\path\*.xlsx |
        Import-Csv -Delimiter '|' |
        select -First 1 


    \_(ツ)_/

    • Marked as answer by Quiet Mouse Tuesday, October 30, 2018 1:16 PM
    Monday, October 29, 2018 7:36 PM
    Moderator
  • Also, shouldn't the "$YourVariable" variable be what's piped to the Export-CSV? And what about that "-Skip 1"?

    To be really honest I wasn't completely sure what the OP exactly wanted. ;-)  He might be able to clarify it a little.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Monday, October 29, 2018 7:47 PM
  • Thanks for the answers! 

    I try to clarify a bit more what i was trying to do. 

    If I have csv or any other txt file, i would want to read the first row of the file.  The row contains more than one piece of information.  I want also save each piece of information to Variables. After saving information to variables, I would like to delete the row.

    Example:

    Car    Model      Year

    Ford   Fiesta      2015

    Audi   A6           2018

    In this example, i would like to save Ford, Fiesta and 2015 to variables (row 1)($Card, $Model, $Year) and after it delete the row.  The 2nd row should not be deleted, because it is used later on



    • Edited by Quiet Mouse Tuesday, October 30, 2018 7:43 AM
    Tuesday, October 30, 2018 7:35 AM
  • I showed you how to get the first row of a CSV file.  The first row of a CSV is the second line in the file.

    $myvariable contains every first row an it is a collection  of variables one for each column.

    Please understand that we don't write custom scripts on request.  If you are not a trained tech then you will have to hire a consultant for your issue.


    \_(ツ)_/

    Tuesday, October 30, 2018 7:40 AM
    Moderator
  • There is a distinct difference between "CSV files" and "any other txt file". You know that, right?

    If it is a proper CSV file you can do it like this:

    $CompleteFileContent = Import-Csv -Path "<Path to you CSV file>" -Delimiter "`t"
    $FirstRow = $CompleteFileContent | Select-Object -First 1
    $ContentWithoutFirstRow = $CompleteFileContent | Select-Object -Skip 1
    $ContentWithoutFirstRow | Export-Csv -Path "<Path to you CSV file>" -Delimiter "`t"
    In this example I assumed you're using a tab as the delimiter in your CSV file.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    Tuesday, October 30, 2018 7:46 AM