Consolidate data with import CSV

Question

I'm trying to find a way to gather data before export to take additional data from a file and consolidate then export.

My code is like: looks for users and computers from many sources and consolidate data → create array of 2 column (name,computer) → export that data to output.log

Because the data I'm looking for dynamically changes from time to time I wish to ran the script multiple times a day, so next time run get the data from output.log into array → continue gathering new data and ADD them into the existent output.log.

At the moment I'm stuck where every time I run the code it overwrites the output.log.

My code is like:

Set-Variable -Name Computer -Value @("pc1","pc2")
Set-Variable -Name LogNames -Value @("something")

$el_c = @()
foreach ($comp in $Computer) {
  foreach ($log in $LogNames) {
    $el = ...    # get data I need from $comp
    $el_c += $el  #consolidating
  }
}

$el_c | %{
  $_ | select @{n='Name';e={$_.Properties[0].value}}, @{n='Computer';e={$_.Properties[1].value}}
} | Export-Csv "C:\test\OutputRaw.log"
$input = 'C:\test\OutputRaw.log'  #TO FILTER OUT DUPLICATION
$inputCsv = Import-Csv $input | Sort-Object * -Unique 
$inputCsv | Export-Csv "C:\test\OutputFinal.log" -NoTypeInformation

Output is:

"Name","Computer"
"Dan","PC1"
"Tom","PC2"

How can I implement that before extract to file ALSO get the data from "output.log" and merge/add/consolidate into the newly gathered data?

How do you want to "consolidate" the data? Do you just want to keep appending to `output.log`? Or should new data be omitted if it matches (doesn't match?) some existing data? — Ansgar Wiechers, Aug 21 '17 at 13:02
the structure of the output should be the same, one column of name's and another column of Computer's. because the import ed data is the same that was exported hours ago I wish just to ADD the new data into the file. — Daniel A, Aug 21 '17 at 13:05
That doesn't answer my question. Do you want to add ALL new data or just SOME new data? — Ansgar Wiechers, Aug 21 '17 at 13:17
As you see in the code when it's query for data it's "select object" of Name and Computer then export into the file, next time when it does just add the newly gathered (select name+computer) data into the existing file (like -append) but somehow to avoid duplication — Daniel A, Aug 21 '17 at 13:41

score 0 · Answer 1 · answered Aug 21 '17 at 13:12

0

Use the -Append Parameter of Export-CSV:

Export-Csv "C:\test\Output.log" -Append

answered Aug 21 '17 at 13:12

Paul

5,524
1
22
39

Almost good, the problem it will add the new data to the bottom of the file and I still stuck with potential duplications – Daniel A Aug 21 '17 at 13:38

iRon · Answer 2 · 2017-08-22T09:24:31.417

0

Presuming that a log (I called it Log.csv) is already created ($Computer | Export-CSV .\Log.csv):

Import-CSV .\Log.csv | LeftJoin $Computer Name -Merge {$Right.$_} | Export-CSV .\Log.csv

For details on LeftJoin (Join-Object), see: https://stackoverflow.com/a/45483110/1701026

edited Aug 22 '17 at 09:24

answered Aug 21 '17 at 14:01

iRon

20,463
10
53
79

score 0 · Accepted Answer · answered Aug 21 '17 at 14:19

Thank to help of Paul, I used -Append then just added another filter duplication at the end.

$el_c | %{
  $_ | select @{n='Name';e={$_.Properties[0].value}}, @{n='Computer';e={$_.Properties[1].value}}
} | Export-Csv "C:\test\OutputRaw.log"
$input = 'C:\test\OutputRaw.log'
$inputCsv = Import-Csv $input | Sort-Object * -Unique
$inputCsv | Export-Csv "C:\test\OutputFinal.log" -Append -NoTypeInformation
$input = 'C:\test\OutputFinal.log'
$inputCsv = Import-Csv $input | Sort-Object * -Unique
$inputCsv | Export-Csv "C:\test\OutputFinal.log"  -NoTypeInformation

score 0 · Answer 4 · answered Aug 21 '17 at 14:40

Use a pipeline and Compare-Object. Move the select statement into the inner foreach loop.

$Computer = 'pc1', 'pc2'
$LogNames = 'something'

$csv = 'C:\test\OutputFinal.log'
$ref = Import-Csv $csv

$Computer | ForEach-Object {
  $comp = $_
  foreach ($log in $LogNames) {
    ... |      # get data from $comp
      select @{n='Name';e={$_.Properties[0].value}},
             @{n='Computer';e={$_.Properties[1].value}}
  }
} | Where-Object {
  Compare-Object $_ $ref -Property 'Name','Computer' -IncludeEqual -ExcludeDifferent
} | Export-Csv $csv -Append -NoType

Consolidate data with import CSV

4 Answers4