How to write output to specific column of CSV

Article
2018-02-08

Question

_{Thursday, February 8, 2018 5:30 AM}

Hi There,

I am trying to compare two CSV files two specific column(5th column) and if the data match found it will write a message on 6th column of one file(Current file) but I am unable to write the output to 6th column.

My code is as below:-

$Previous_file = import-csv Event_Note_2018-02-06.csv | Select-Object "ServerName"
$Todays_file = import-csv Event_Note_2018-02-07.csv| Select-Object "ServerName"

foreach ($a in $Previous_file.ServerName)
  {
    foreach ($b in $Todays_file.ServerName)

     {
         if ($a -eq $b)

          {

             write-host "Matched" $a -ForegroundColor Green


          }

      }
  }

Currently its only comparing the data and showing matching output in console but I need this to be added to Column E of current csv file when it matches $a with $b like below

All replies (15)

_{Friday, February 9, 2018 4:41 PM ✅Answered}

Your $serversPreviousFile_comment is a collection of values, because you have added all the comments from all the Comments rows in $previousFile.

So, when you find a match, you add all those comments into $a.Comments.

That is why you see System.Object[] because it represents a collection of values, not just one value.

If you find a match, you have to add only that one comment, which is related to the current server ($a.ServerName) from the loop.

You need one extra loop, something like:

foreach ($a in $todaysFile) {
  foreach ($b in $PreviousFile) {
    if ($a.ServerName -eq $b.Servername) {
     $a.comments = $b.comments
     $todaysFile | Export-CSV $here\$Todays_filename -notypeinformation
     }
    }
   }

This way you will update the server's comments in today's file with the proper comment from the previous file

Please click on Propose As Answer or to mark this post as and helpful for other people. This posting is provided AS-IS with no warranties, and confers no rights.

_{Thursday, February 8, 2018 6:03 AM}

$a.ServerName
$b.ServerName

help Import-Csv -Online

\(ツ)_/

_{Thursday, February 8, 2018 6:34 AM}

I am not facing any issue while Importing the CSV file as you can see I am already using Import csv in my above code.I need some suggestion to write the output to a specific column in csv file.I don't see any options in Export-CSV for this .

_{Thursday, February 8, 2018 6:38 AM}

$a.ServerName = 'something'

Which is what I showed you. You need to read the help because you do not understand what a CSV is and how it works.

\(ツ)_/

_{Thursday, February 8, 2018 6:57 AM | 1 vote}

Hi,

your csv is the representation of a data table, where each line is a dataset and columns are datafields or properties of each dataset. By loading with Import-CSV, your lines become objects with properties (from the columns). These objects are stored in an array.

You have to iterate over the array, not over a special property as you did.

$PreviousFile = import-csv "Event_Note_2018-02-06.csv"
$TodaysFile = import-csv "Event_Note_2018-02-07.csv"

$ServersPreviousFile = $PreviousFile | select -expandProperty ServerName

foreach($a in $TodaysFile){
   if($a.Servername -in $ServersPreviousFile){$a.Comments = "Matched"}
}

$TodaysFile | Export-CSV "SomeFilePath.csv" -delimiter ";" -notypeinformation

_{Thursday, February 8, 2018 7:38 AM}

Try something like this:

$Previous_file = import-csv Event_Note_2018-02-06.csv | Select-Object "ServerName"
$Todays_file = import-csv Event_Note_2018-02-07.csv| Select-Object "ServerName"

$collectionObj = New-Object PSObject

foreach ($a in $Previous_file.ServerName)
  {
    foreach ($b in $Todays_file.ServerName)

     {
         if ($a -eq $b)

          {


             write-host "Matched" $a -ForegroundColor Green
            $collectionObj | Add-Member -MemberType NoteProperty -Name 'Date' -Value $a.Date
            $collectionObj | Add-Member -MemberType NoteProperty -Name 'Time' -Value $a.Time
            $collectionObj | Add-Member -MemberType NoteProperty -Name 'ServerName' -Value $a.ServerName
            $collectionObj | Add-Member -MemberType NoteProperty -Name 'Status' -Value $a.Status
            $collectionObj | Add-Member -MemberType NoteProperty -Name 'Comments' -Value 'Matched'
            $collectionObj | Export-Csv 'C:\temp\Report.csv' -NoTypeInformation -Append

          }

      }
  }

If you find a match, it will be added into the new .csv report.

Now the question is, do you want only the results where you have a match, or you also want to have the result where you don't have a match?

Please click on Propose As Answer or to mark this post as and helpful for other people. This posting is provided AS-IS with no warranties, and confers no rights.

_{Thursday, February 8, 2018 8:50 AM}

@Vlad817263: Your solution is as wrong as it can be. By using select-Object in the first 2 lines, $previous_file and $todays_file will contain only this single property. If this wouldn't be enough, there is again a selection of ServerName in both loop definitons. Hence, $a and $b contain simply ServerName property. These are the mistakes of Chinmay Nayak.

But what are you expecting to get from $a.Date, $a.Time and so on? There is nothing. $a is a simple string!

And, even if we solve this (like I did in my solution): Why are you creating a custom object that is identical to $a? And why in this inconvenient way?

_{Thursday, February 8, 2018 9:12 AM}

I recommend skipping all of the old VB programming and use PowerShell to do this.

Here is the PowerShell method.

$servers = import-csv Event_Note_2018-02-06.csv | select -Expand ServerName
import-csv Event_Note_2018-02-07.csv |
    ForEach-Object{
       if($_.Servername -in $servers){$_.Comments = 'Matched'}
    } |
    Export-CSV SomeFilePath.csv -notypeinformation

If we need to add "Comment" then we would do this.

$servers = import-csv Event_Note_2018-02-06.csv | select -Expand ServerName
import-csv Event_Note_2018-02-07.csv |
    select *,Comment |
    ForEach-Object{
       if($_.Servername -in $servers){$_.Comments = 'Matched'}
    } |
    Export-CSV SomeFilePath.csv -notypeinformation

You can see that PowerShell is better able to do this if you forget about things like VBScript and VB and just use PS.

As we say in the programming domain .... "take the pipe" - The PosH pipe.

\(ツ)_/

_{Thursday, February 8, 2018 9:13 AM}

P.S. - CSVs use commas which are the default. Switching to a semi-colon will likely cause chaos.

\(ツ)_/

_{Thursday, February 8, 2018 9:20 AM}

My mistake. I realized he filtered only 'ServerName' for both previous day and today.

I was creating a custom object because I didn't know if he wants to use data from Previous or Today csv file.

I have to practice more I guess.

Please click on Propose As Answer or to mark this post as and helpful for other people. This posting is provided AS-IS with no warranties, and confers no rights.

_{Thursday, February 8, 2018 9:30 AM | 1 vote}

Doesn't matter. I made a point of not providing the complete solution so the user would actually take time to learn some PowerShell. Writing solutions ends up creating techs who never learn PowerShell and who keep posting the same faulty and obsolete code.

Add-Member hasn't been needed to create objects since PS2 and PS 2 is no longer supported.

Best to spend some time learning modern PowerShell. We are currently at PowerShell 6; a long way from the PS2 of 10 years ago. PS 3 and later have gone through serious changes and improvements.

\(ツ)_/

_{Thursday, February 8, 2018 12:21 PM}

You can also do it this way

$PreviousFile = import-csv "Event_Note_2018-02-06.csv"
$TodaysFile = import-csv "Event_Note_2018-02-07.csv"

$ServersPreviousFile = $PreviousFile | select -expandProperty ServerName

$TodaysFile | where{$_.Servername -in $ServersPreviousFile} | foreach{$_.Comment = 'Matched'}

$TodaysFile | Export-CSV "SomeFilePath.csv" -delimiter ";" -notypeinformation

I don't think, that putting everything in one pipeline is more modern. For debugging purposes, I would really prefer this programming style.

_{Thursday, February 8, 2018 12:30 PM}

You can also do it this way
$PreviousFile = import-csv "Event_Note_2018-02-06.csv"
$TodaysFile = import-csv "Event_Note_2018-02-07.csv"

$ServersPreviousFile = $PreviousFile | select -expandProperty ServerName

$TodaysFile | where{$_.Servername -in $ServersPreviousFile} | foreach{$_.Comment = 'Matched'}

$TodaysFile | Export-CSV "SomeFilePath.csv" -delimiter ";" -notypeinformation
I don't think, that putting everything in one pipeline is more modern. For debugging purposes, I would really prefer this programming style.

Foolish. You are still using a pipeline but yours is much slower.

PowerShell debugs pipelines just like code. Yours is just a more complicate version of what I wrote. It works but I see no advantage. You are just slowing things down by at least a factor of three. Besides. what is there that needs to be debugged?

\(ツ)_/

_{Friday, February 9, 2018 2:58 PM}

Thank you hpotsirhc for the suggestion.Its working for me now.But I tried to modify the script a little bit and instead of hard coding "comment" I tried to put the previous files comment section to copy to current date file whenever it matches the server name but the value is coming as 'System.Object[]'

Below is my complete code

$yesterday = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
$Today = (Get-Date).ToString("yyyy-MM-dd")
$here = (Get-Item -Path ".\" -Verbose).FullName
$yesterday_filename= 'Event_Note'+ '_'+$yesterday+'.csv'
$Today_filename = 'Event_Note'+ '_'+$Today+'.csv'



 #$searcher = Get-Childitem –Path $here\$Today_filename -Recurse -ErrorAction SilentlyContinue | select -expandProperty Name
 $searcher = Get-ChildItem -path $here | select -ExpandProperty Name



 #will search for file presence on directory
 if ($Today_filename -in $searcher)
 {
 
 
        #Imoprting the CSV files
        $PreviousFile = import-csv $here\$yesterday_filename
        $TodaysFile = import-csv $here\$Today_filename
        $ServersPreviousFile_ServerName = $PreviousFile | select -expandProperty ServerName
        $ServersPreviousFile_comment = $PreviousFile | select -expandProperty Comments


        #Loop started for comparing servers from yesterday failes
        foreach($a in $TodaysFile)
            {
                if($a.Servername -in $ServersPreviousFile_ServerName)
                    {
                    
                       
                        $a.Comments = $ServersPreviousFile_comment
                         
                      
                      
                    }


                #Server name compared and added to comment box 
                $TodaysFile | Export-CSV $here\$Today_filename -notypeinformation
            }
   }

else {
        write-host "File not present in directory.Needs to be copied from Traffic"  -ForegroundColor Yellow
     }

and output is coming below

_{Friday, February 9, 2018 4:53 PM}

Thank you lot Vlad817263 .I put an extra loop as you suggested and it worked.

Share via

How to write output to specific column of CSV

Question

All replies (15)

Additional resources