Powershell Filter Data and Copy Data to new .csv file

Lakshmana

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?

stess

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

@stess

~~Hmmm.. I don't know how to post code here. :P~~

$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!

Obsolesce

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

stess

@tim_g
Thanks! site bookmarked

Lakshmana

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

Lakshmana

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

stess

@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

Lakshmana

@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

stess

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

Lakshmana

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

@stess

~~Hmmm.. I don't know how to post code here. :P~~

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

stess

@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. :P~~

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

Lakshmana

@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