Import and modify multiple CSV files in Powershell

Question

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:

Loop through CSVs
For each CSV:
Add a new column
Look for some words.
If you see the words, write a different word in the new column
End loop
End loop

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?

It looks like you need to move the | export-csv to the end of your pipeline rather than put it inside of the loop. — Mike Garuccio
– Mike Garuccio, Commented Nov 15, 2016 at 17:44

user6811411 · Accepted Answer · 2016-11-15 19:31:40Z

1

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"

answered Nov 15, 2016 at 19:31

user6811411

Sign up to request clarification or add additional context in comments.

2 Comments

Brad Tenenholtz Over a year ago

What language is that?

user6811411 Over a year ago

The upper part is the same powershell, the lower is simply a console screen copy.

Collectives™ on Stack Overflow

Import and modify multiple CSV files in Powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related