help on export fields in CSV

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

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?
Export-Csv (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs
Specically the examples:

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

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

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

nobody for helping me please???

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

many thanks

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

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.

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??

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.

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…

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.

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??

 

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.

... 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.

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…

Hi Rob

Thanks for your help

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

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. :wink:
Without knowing at least the basics you’re not going to understand the help we’re trying to give to you.

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 :grinning: