help on export fields in CSV

Welcome Forums General PowerShell Q&A help on export fields in CSV

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

 
Participant
2 months, 2 weeks ago.

  • Author
    Posts
  • #104647

    Participant
    Points: 0
    Rank: Member

    hello

    i use the code below

    $PC = import-csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv' | Select-Object -ExpandProperty host

    function get-onlinetest {
    Param(
    [System.String[]]
    $ComputerName
    )
    $timeStamp = Get-Date
    foreach ($Computer in $ComputerName) {
    if (Test-Connection -ComputerName $Computer -Quiet -Count 1) {
    [PSCustomObject]@{
    ComputerName = $Computer
    Online = $true
    TimeStamp = $timeStamp
    }
    }
    else {
    [PSCustomObject]@{
    ComputerName = $Computer
    Online = $false
    TimeStamp = $timeStamp
    }
    }
    }
    }

    get-onlinetest -ComputerName $pc |
    Export-Csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL-Out.csv' -Delimiter ',' -NoTypeInformation -Append

    in my CSV file, i have added 4 fields : buiding,floor,gate,localisation
    how to do for doing that fields to appear in "NZDL-Out.csv"
    sorry but i m totally rookie
    rgds

  • #104749

    Participant
    Points: 217
    Helping Hand
    Rank: Participant

    If you are importing here:

    $PC = import-csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv' | Select-Object -ExpandProperty host

    .. and exporting back to the same file

    Export-Csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL-Out.csv' -Delimiter ',' -NoTypeInformation -Append

    … and you've modified the NZDL.csv file to have these extra values (added 4 fields : buiding,floor,gate,localisation), then you already have this in the file to begin with or are you talking about some other CSV not shown here?

    So, I don't get what you are after.
    What does ...
    'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv'
    ... look like on this import?

    What are you expecting of that same file on export.

    Provide input / output examples.

    Have you looked at the guidance in the built-in help files?
    'docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6'
    Specically the examples:

    Example 6: Export and append script properties
    Example 7: Select properties to export

  • #104771

    Participant
    Points: 160
    Helping Hand
    Rank: Participant

    Assuming your original CSV has multiple columns and all you are trying to do is add a column to see if a host is online, you can do it with calculated expressions:

    $results = Import-Csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv' | 
               Select-Object *,
                             @{Name="Online";Expression={Test-Connection -ComputerName $_.Host -Quiet -Count 1}},
                             @{Name="TimeStamp";Expression={Get-Date}}
    
    $results | Export-CSV -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL-Out.csv' -NoTypeInformation
    
  • #104800

    Participant
    Points: 0
    Rank: Member

    hi and thanks

    your code works but it changes the configuration of NZDL.csv and it dont keep the history
    this CSV has to be configurated with the fields in the order below :

    ComputerName,Online,TimeStamp,buiding,floor,gate,localisation

    so is it possible to adapt only the original please??
    thanks a lot

  • #104929

    Participant
    Points: 0
    Rank: Member

    nobody for helping me please???

    • #105037

      Participant
      Points: 145
      Helping Hand
      Rank: Participant

      You could use a PSCustomObject to keep the order of the properties ... based on Robs code suggestion you could do something like this:

      $results = Import-Csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv' |
          ForEach-Object {
              [PSCustomObject][Ordered]@{
                  ComputerName = $_.ComputerName
                  Online = $(Test-Connection -ComputerName $_.ComputerName -Quiet -Count 1)
                  TimeStamp = $_.TimeStamp
                  buiding = $_.buiding
                  floor = $_.floor
                  gate = $_.gate
                  localisation = $_.localisation
              }
          }
      
      $results | Export-CSV -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL-Out.csv' -NoTypeInformation
  • #108130

    Participant
    Points: 0
    Rank: Member

    many thanks

  • #108131

    Participant
    Points: 0
    Rank: Member

    just another question

    if i want to replace online by ping and computer name by host i do this???

    $results = Import-Csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv' |
    ForEach-Object {
    [PSCustomObject][Ordered]@{
    host = $_.host
    Ping = $(Test-Connection -ComputerName $_.host -Quiet -Count 1)
    TimeStamp = $_.TimeStamp
    buiding = $_.buiding
    floor = $_.floor
    gate = $_.gate
    localisation = $_.localisation
    }
    }

    $results | Export-CSV -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL-Out.csv' -NoTypeInformation

  • #108242

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    if i want to replace online by ping and computer name by host i do this???

    hmmm ... are you really waiting for confirmation here before you just try it? 😉 If you try it and it works it's fine.

  • #108244

    Participant
    Points: 0
    Rank: Member

    olaf

    sorry i just need a last update....

    in fact i just need one CSV file

    in this file i need the fields below

    all the fields are static except Ping and TimeStamp

    i have always 10 host name so i want always 10 lines and not more

    so it means that i have to crush each line (just for fields Ping and Timestamp) each time the script runs

    host,Ping,TimeStamp,Building,Floor,Gate,Localisation
    NZDL302005321,"True","23/07/2018 08:53:23",G39,1,271,Armoire beige / Face poste 271B

    could you help me to do this please??

  • #108247

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    But you already have the answer. If you need the same elements in your resulting csv file like you have in your source csv file and you just like to update two elements, you extend the hashtable to all 10 elements and reference 8 of the elements from the source csv file directly and calculate 2 of the elements new like you've seen it the code Rob and I posted above.

    • #108248

      Participant
      Points: 0
      Rank: Member

      Pearhaps my explanations are not clear but i just want one CSV file in exit. The idea is that the script copy All the fields i want (static and dynamic fields) in the CSV... sorry but powershell is à stranger for me...

    • #108287

      Participant
      Points: 145
      Helping Hand
      Rank: Participant

      ... sorry but powershell is à stranger for me...

      So you might do a little step back and take your time to learn the very basics of Powershell. It will probably save you from a lot of wasted time and frustration.
      An easy and slightly entertaining way to start with Powershell is the free of charge video course from Microsoft Virtual Academy – Getting Started With Powershell. It will answer most of your beginner questions.

    • #108433

      Participant
      Points: 0
      Rank: Member

      Olaf
      I have planned To learn Powershell as soon as possible

      Sorry but i need this script quickly...

      But every time an expert answer me i try To understand the code and it help me a lot for my future training.....

  • #108275

    Participant
    Points: 160
    Helping Hand
    Rank: Participant

    Please explain your issue. Provide examples of the input file and expected output. Don't worry about Powershell logic, we need to understand what you are starting with and what you want when the script is executed.

  • #108277

    Participant
    Points: 0
    Rank: Member

    Hi

    So I start again from the beginning

    Please see the file in attachment

    https://www.cjoint.com/c/HHltlK7R1SW

    With the PowerShell script, i just need to copy the field value of "Online" and "TimeStamp" (in red in the file) in the CSV file

    All other fields are always the same

    So every time the script is played, i need to have the 13 lines copied in the CSV file after the existing 13 lines with the new value of "Online" and "TimeStamp"

    Is it clear or not please??

     

  • #108283

    Participant
    Points: 160
    Helping Hand
    Rank: Participant

    I think I understand. First, the CSV you posted is NOT in CSV format. When I took your file and attempted import, this is what I got:

    host         : NZDL302011883,"True","23/07/2018 08:53:23",G44,1,A330 SYS.,Bureau derriere monte-charges / Face A330 TC5
    Online       : 
    TimeStamp    : 
    Building     : 
    Floor        : 
    Gate         : 
    Localisation : 
    
    host         : NZDL302013110,"True","23/07/2018 08:53:23",G44,1,A380 SYS.,Sur pied / Control room A330 face alveole 6
    Online       : 
    TimeStamp    : 
    Building     : 
    Floor        : 
    Gate         : 
    Localisation 
    

    I removed all of the " and it imported properly, so it should look like this:

    host         : NZDL302011883
    Online       : True
    TimeStamp    : 23/07/2018 08:53:23
    Building     : G44
    Floor        : 1
    Gate         : A330 SYS.
    Localisation : Bureau derriere monte-charges / Face A330 TC5
    
    host         : NZDL302013110
    Online       : True
    TimeStamp    : 23/07/2018 08:53:23
    Building     : G44
    Floor        : 1
    Gate         : A380 SYS.
    Localisation : Sur pied / Control room A330 face alveole 6
    

    Once you have a good CSV source, then it sound like you want to use the first 13 rows as your source and you want to re-test the online status and update the date\time and append new 13 rows to existing file.

    $csvPath = 'C:\Scripts\test.csv'
    $csv = Import-Csv -Path $csvPath
     
    #Take the first 13 rows and update Online and TimeStamp          
    $newContent = $csv | 
                  Select -First 13 -Property Host,
                                             @{Name='Online';Expression={Test-Connection -ComputerName $_.host -Quiet -Count 1}},
                                             @{Name='TimeStamp';Expression={Get-Date}},
                                             Building,
                                             Floor,
                                             Gate,
                                             Localisation
    
    $newCSV = @() #Create new array
    $newCSV += $csv #Append previous csv
    $newCSV += $newContent #Append new rows
    
    #Overwrite the existing CSV with the new CSV
    $newCSV |
    Export-CSV -Path $csvPath -NoTypeInformation
    

    This code would take the original file, basically copy the first 13 rows and append it to bottom of the file, so you would have 26, then 39 and so on when the script is run.

    • #108434

      Participant
      Points: 0
      Rank: Member

      Hi Rob

      Thanks for your help

      I m going To test it as soon as possible and i keep you aware 😉

    • #110021

      Participant
      Points: 0
      Rank: Member

      Hello Rob

      I just need two very little changes 😉

      1) When the line are copied in the CSV, all the fields are separated with " tags. Is it possible to avoid it please?

      2) When the append of the new 13 lines occurs I just want that these 13 lines crushed the previous existing 13 lines. So i want always 13 lines in my CSV file and not more

      Could you help me and after it will be perfect 😉

  • #108449

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    I have planned To learn Powershell as soon as possible

    I'm glad to hear that. Good for you.

    Sorry but i need this script quickly...

    But every time an expert answer me i try To understand the code and it help me a lot for my future training.....

    That's the point (at least for me). It's like you telling me you need to go through New York with a construction vehicle but you've never had any lession in a driver school. You're gonna hurt people and probably yourself as well. 😉
    Without knowing at least the basics you're not going to understand the help we're trying to give to you.

  • #108451

    Participant
    Points: 0
    Rank: Member

    Yes i have forgotten To tell you that in parallel i study the basics

    I hope in a few months the student i am Will be in the USA 😀

  • #110026

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    For 1)
    Actually not. It's part of the csv format definition and it identifies text. All tools or applications able to import or export csv files should be able to deal with it.

    For 2)
    You've got several code examples only one of it uses the parameter -Append. If you don't like to append the new data don't use it. You can use the parameter -Force to overwrite the exisiting file.

  • #110126

    Participant
    Points: 0
    Rank: Member

    Thanks Olaf

    1) I was afraid that " syntax may cause issue and make the CSV file not recognized as a CSV file... For information I use the data of the CSV file with a BI tool

    2)

    In the last script of Rob below there is not an append parameter. So I dont now how to force the overwriting??

    PowerShell.exe -ExecutionPolicy Bypass
    $csvPath = 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv'
    $csv = Import-Csv -Path $csvPath

    #Take the first 13 rows and update Ping and TimeStamp
    $newContent = $csv |
    Select -First 13 -Property Host,
    @{Name='Ping';Expression={Test-Connection -ComputerName $_.host -Quiet -Count 1}},
    @{Name='TimeStamp';Expression={Get-Date}},
    Building,
    Floor,
    Gate,
    Localisation

    $newCSV = @() #Create new array
    $newCSV += $csv #Append previous csv
    $newCSV += $newContent #Append new rows

    #Overwrite the existing CSV with the new CSV
    $newCSV |
    Export-CSV -Path $csvPath -NoTypeInformation

  • #110128

    Participant
    Points: 573
    Helping Hand
    Rank: Major Contributor

    Putting below line inside a script will not be in effect.

    PowerShell.exe -ExecutionPolicy Bypass

    This is used when you have to call a script using PowerShell.exe by bypassing the current execution policy.

    PowerShell.exe -ExecutionPolicy Bypass -File c:\somescript.ps1

    and I would request you to use code posting tags(How to format code in this forum) to post code as it makes others easy to read and understand the code.

    and for point 2: you can use -Force parameter of Export-CSv. you can do a Get-Help Export-CSV -Full for the documentation.

  • #110129

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    I have to echo what kvprasoon said ... please please please use the code posting tags. You don't even have to type them, you can click on them, they are named "pre" and you can find them right above the text you enter in the post editor.

    I don't know what's your native language but in the code you posted you have twice an "append" in your comments!! If you don't want to append the old data don't do it

    $csvPath = 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv'
    $csv = Import-Csv -Path $csvPath | 
        Select-Object -Property Host, Building, Floor, Gate, Localisation,
            @{Name='Ping';Expression={Test-Connection -ComputerName $_.host -Quiet -Count 1}},
            @{Name='TimeStamp';Expression={Get-Date}}
    $csv |
        Export-CSV -Path $csvPath -NoTypeInformation -Force

    I would like to recommend for you to urgently start to learn the very basics of Powershell. What you're asking here are beginner questions. You would save yourself from a lot of wasted time and frustrations.

  • #110183

    Participant
    Points: 0
    Rank: Member

    Hi

    Sorry, I will use code posting tags in the future

    Concerning the code, it doesn't works because there is no changes in the CSV file...

    The file is available here :

    https://cjoint.com/c/HHCsszTXcyf

    Only the fields Timestamp and ping change every time the script runs and everytime they change I want to overwrite the old data for these fields...

  • #110185

    Participant
    Points: 0
    Rank: Member

    Is this code is OK for doing what I want??

    
    $csvPath = 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\NZDL.csv'
    $csv = Import-Csv -Path $csvPath
    
    #Take the first 13 rows and update Ping and TimeStamp
    $newContent = $csv |
    Select -First 13 -Property Host,
    @{Name='Ping';Expression={Test-Connection -ComputerName $_.host -Quiet -Count 1}},
    @{Name='TimeStamp';Expression={Get-Date}},
    Building,
    Floor,
    Gate,
    Localisation
    
    $newCSV = @() #Create new array
    $newCSV += $csv #Append previous csv
    $newCSV += $newContent #Append new rows
    
    #Overwrite the existing CSV with the new CSV
    $newCSV |
    Export-CSV -Path $csvPath -NoTypeInformation -Force
    
    
  • #110188

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    Am I wrong or are you posting the same code again and again? 😉 Does the code do what you want? I have updated my code suggestion in my last post.
    Please take your time and start to learn the basics of Powershell from scratch. You wull learn how to debug your scripts as well. Run your code line by line in the ise and check what's in the variables you use.

  • #110225

    Participant
    Points: 0
    Rank: Member

    No its not the same code, I added -Force parameter

    But it doesnt do what I want

    I doesnt understand why your code do nothing

    And please understand that this specific scripts is urgent for me, I have not enough time to learn Powershell actually but it will better since one month....

    So I will understand it at this time....

    • #110228

      Participant
      Points: 145
      Helping Hand
      Rank: Participant

      To watch the MVA video course "Getting Started with Powershell" takes about 8 or 10 hours or so. I assume you would have time for this already.
      It's almost impossible that my code suggestion does "nothing". No error? No messages in the console? Nothing? Did you try to debug it?

    • #110233

      Participant
      Points: 0
      Rank: Member

      It works now...

      The script is launched automatically by another tool and the issue was here...

      My data are well overwritted....

      Thanks a lot

  • #110231

    Participant
    Points: 573
    Helping Hand
    Rank: Major Contributor

    @rosse There is no benchmark for an individual about PowerShell knowledge. We all including you are learning it everyday, what @js was suggesting you is to have the very basics first. The video tutorial in Microsoft Virtual Academy(PowerShell 3.0 Jump Start) is just few hours. that will give you a lot of information.

    Please check below code is worth for you or not.

    $Csv = Import-Csv -Path $CsvPath
    $NewData = $Csv | Foreach-Object -Process {
                $PropertyHash = @{
                    Host         = $_.Host
                    TimeStamp    = (Get-Date)
                    Ping         = (Test-Connection -ComputerName $_.host -Quiet -Count 1)
                    Building     = $_.Building
                    Floor        = $_.Floor
                    Gate         = $_.Gate
                    Localisation = $_.Localisation
                }
    
                New-Object -TypeName PSObject -Property $PropertyHash
    }
    $NewData | Export-Csv -Path $CsvPath -Force -NoTypeInformation
    
  • #110239

    Participant
    Points: 145
    Helping Hand
    Rank: Participant

    face palm

    😉 😀

You must be logged in to reply to this topic.