Powershell Filter Data and Copy Data to new .csv file



  • How to filter data and copy the selective data to the new .csv according to our need ?

    Any powershell commands is there ?

    I have .csv file with the content of Sl.No ,User details ,login id and i need to filter the login id and then copy to new .csv file

    Is that possible to do by powershell?



  • I use Foreach loop and pass the value to an Array if it meeds if() criteria. Then output the $true result back to the same CSV. I'll post the code later today.

    Edit: can you post your csv data? Other wise I'll give you my data which has 4 column

    Edit2: Also, for my script to works you need to manually change the if() statement manually each run. Easier to use Powershell ISE or any other powershell IDE. If you want to make it fancy then a custom textbox to return the value you are looking for is not a bad idea.



  • @stess

    Hmmm.. I don't know how to post code here. 😛

    $csvFile = ".\etc.csv" #dummy file. Change it to whereevery your file is at.
    $csvImport = Import-Csv $csvFile
    
    # Create Array for Exporting out data
    $csvArray = "" #this clear for testing purpose. But it works in production environment as well.. so I didn't bother to remove it.
    $csvArray = [System.Collections.ArrayList]@()
    
    # Entry count
    $lineCounter = 1
    
    # Foreach Loop
    Foreach ($csvImportedItem in $csvImport){
    $lineCounter++ #this let me know which line in the entry is the problem.
    $importedName = $csvImportedItem.Name
    $importedID = $csvImportedItem.ID
    $importedDept = $csvImportedItem.Department
    $importedNote = $csvImportedItem.Note
    if($importedNote -eq "C"){
    $csvArray += $csvImportedItem
    "[$lineCounter] $importedName $importedID $importedDept $importedNote`n" #For outputing result. Testing purpose
    $csvImportedItem #For outputing result. Testing purpose
    
    
    }
    }
    $csvArray #For outputing result before exporting to new CSV
    #$csvArray | Export-Csv $NEWPATH -NoTypeInformation #destinate new path.
    
    Name ID Department Note
    Caroline Bates 10035 Sales A
    Danny Harrison 11523 Customer Service B
    Armando Kelley 16423 Sales C
    Dorothy Colon 10245 HR B
    Joanna Sutton 19853 Accounting C

    Neat!



  • @stess

    This site uses Markdown: https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet

    To post code, you use three ``` before and after your code. Single line or above and below your block of code.



  • @tim_g
    Thanks! site bookmarked



  • If there is any possibility to copy the new content of the data from. csv to smtp mail by powershell???



  • @tim_g said in Powershell Filter Data and Copy Data to new .csv file:

    @stess

    This site uses Markdown: https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet

    To post code, you use three ``` before and after your code. Single line or above and below your block of code.

    This git hub is for?



  • @lakshmana

    You mean send mail using whatever the data in the CSV is?

    $SmtpServer = "smtp.com"
    $SmtpServerPort = "25 or 587"
    $SmtpFrom = "[email protected]"
    $SmtpTo = "[email protected]"
    $SmtpBody = "$csvArray"
    $SmtpSubject = "Subject"
    
    Send-MailMessage -SmtpServer $SmtpServer -Port $SmtpServerPort -From $SmtpFrom -To $SmtpTo -subject $SmtpSubject -body $SmtpBody
    


  • @stess said in Powershell Filter Data and Copy Data to new .csv file:

    @lakshmana

    You mean send mail using whatever the data in the CSV is?

    $SmtpServer = "smtp.com"
    $SmtpServerPort = "25 or 587"
    $SmtpFrom = "[email protected]"
    $SmtpTo = "[email protected]"
    $SmtpBody = "$csvArray"
    $SmtpSubject = "Subject"
    
    Send-MailMessage -SmtpServer $SmtpServer -Port $SmtpServerPort -From $SmtpFrom -To $SmtpTo -subject $SmtpSubject -body $SmtpBody
    

    Thank you so much



  • @lakshmana
    You should look more into technet articles. There are plenty of guide, tips and tricks, etc.



  • @stess said in Powershell Filter Data and Copy Data to new .csv file:

    @stess

    Hmmm.. I don't know how to post code here. 😛

    $csvFile = ".\etc.csv" #dummy file. Change it to whereevery your file is at.
    $csvImport = Import-Csv $csvFile
    
    # Create Array for Exporting out data
    $csvArray = "" #this clear for testing purpose. But it works in production environment as well.. so I didn't bother to remove it.
    $csvArray = [System.Collections.ArrayList]@()
    
    # Entry count
    $lineCounter = 1
    
    # Foreach Loop
    Foreach ($csvImportedItem in $csvImport){
    $lineCounter++ #this let me know which line in the entry is the problem.
    $importedName = $csvImportedItem.Name
    $importedID = $csvImportedItem.ID
    $importedDept = $csvImportedItem.Department
    $importedNote = $csvImportedItem.Note
    if($importedNote -eq "C"){
    $csvArray += $csvImportedItem
    "[$lineCounter] $importedName $importedID $importedDept $importedNote`n" #For outputing result. Testing purpose
    $csvImportedItem #For outputing result. Testing purpose
    
    
    }
    }
    $csvArray #For outputing result before exporting to new CSV
    #$csvArray | Export-Csv $NEWPATH -NoTypeInformation #destinate new path.
    
    Name ID Department Note
    Caroline Bates 10035 Sales A
    Danny Harrison 11523 Customer Service B
    Armando Kelley 16423 Sales C
    Dorothy Colon 10245 HR B
    Joanna Sutton 19853 Accounting C

    Neat!

    if there "ticket id" and "Assigned date" is there in heading means??



  • @lakshmana said in Powershell Filter Data and Copy Data to new .csv file:

    @stess said in Powershell Filter Data and Copy Data to new .csv file:

    @stess

    Hmmm.. I don't know how to post code here. 😛

    $csvFile = ".\etc.csv" #dummy file. Change it to whereevery your file is at.
    $csvImport = Import-Csv $csvFile
    
    # Create Array for Exporting out data
    $csvArray = "" #this clear for testing purpose. But it works in production environment as well.. so I didn't bother to remove it.
    $csvArray = [System.Collections.ArrayList]@()
    
    # Entry count
    $lineCounter = 1
    
    # Foreach Loop
    Foreach ($csvImportedItem in $csvImport){
    $lineCounter++ #this let me know which line in the entry is the problem.
    $importedName = $csvImportedItem.Name
    $importedID = $csvImportedItem.ID
    $importedDept = $csvImportedItem.Department
    $importedNote = $csvImportedItem.Note
    if($importedNote -eq "C"){
    $csvArray += $csvImportedItem
    "[$lineCounter] $importedName $importedID $importedDept $importedNote`n" #For outputing result. Testing purpose
    $csvImportedItem #For outputing result. Testing purpose
    
    
    }
    }
    $csvArray #For outputing result before exporting to new CSV
    #$csvArray | Export-Csv $NEWPATH -NoTypeInformation #destinate new path.
    
    Name ID Department Note
    Caroline Bates 10035 Sales A
    Danny Harrison 11523 Customer Service B
    Armando Kelley 16423 Sales C
    Dorothy Colon 10245 HR B
    Joanna Sutton 19853 Accounting C

    Neat!

    if there "ticket id" and "Assigned date" is there in heading means??

    use " "
    So

    $importedID = $csvImportedItem.ID
    

    becomes

    $importedID = $csvImportedItem."ticket id"
    

    Keep the variable simple and easy to understand.



  • @stess i get error as "Warning: one or more headers were not specified. Defaults names starting with H have been used in place of any missing headers"

    there is no data copied here to. csv


Log in to reply