CODEWITHSUNDEEP
powershellcsv
Brad Tenenholtz
Brad Tenenholtz

Reputation: 13

Import and modify multiple CSV files in Powershell

Thanks ahead of time. I'm trying to import multiple .csv files, add a column to them, then write to that column based on the value of another column

What I'm trying to do is follow this logic:

My code is here:

Function getTag ([string]$vuln){
   if($vuln -like "adobe-apsb-")
   {return "Adobe Flash"}
   if($vuln -like "jre-")
   {return "Java"}
   else
   {return "No vulnerability code available"}
}

$in = "D:\Splunk\Inbound"
$out = 'D:\Splunk\Outbound'
Get-ChildItem $in -Filter *.csv |
ForEach-Object{
    Import-Csv $_ | Select-Object *,@{Name='Tag';Expression={getTag $_.'Vulnerability ID'}} | Export-Csv $_.Name

}

Right now, $_ is coming through as a string, not a CSV, and I believe that's my problem. Does anyone have a good way to access that csv file from inside the nested loop?

Upvotes: 0

Views: 1114

Answers (1)

user6811411
user6811411

Reputation:

  • Your problem with using -like is a missing wildcard.
  • I guess the Outbound folder shall contain the modified csv?
  • These endless command lines aren't necessary, neither in a script nor in the shell.

For me this is much better readable (and it works) : 

Function getTag ([string]$vuln){
    if ($vuln -like "adobe-apsb-*")
        {return "Adobe Flash"}
    if ($vuln -like "jre-*")
        {return "Java"}
    else
        {return "No vulnerability code available"}
}

$in  = "D:\Splunk\Inbound"
$out = "D:\Splunk\Outbound\"

Get-ChildItem $in -Filter *.csv |
    ForEach-Object{
        Import-Csv $_.FullName | 
            Select-Object *,@{
                Name='Tag';
                Expression={getTag $_.'Vulnerability ID'}
            } | Export-Csv -path $($out+$_.Name) -notype
    } 

> tree . /f
│
├───Inbound
│       test.csv
│
└───Outbound
        test.csv

> gc .\Inbound\test.csv
"A","Vulnerability ID","C"
0,"adobe-apsb-blah",2
5,"jre-trash",9

> gc .\Outbound\test.csv
"A","Vulnerability ID","C","Tag"
"0","adobe-apsb-blah","2","Adobe Flash"
"5","jre-trash","9","Java"

Upvotes: 1

Related Questions