How do I process multiple CSV files in Powershell and give them output names?

Question

So I'm trying to process CSV files, then giving the output new name. I can do it with one file by explicitly specifying the file name. But is there a way / wildcard I can use to make the script to process multiple files at the same time? Let's just say I want to process anything with .csv as an extension. Here's my script that's used to process a specific file

$objs =@();
$output = Import-csv -Path D:\TEP\FilesProcessing\Test\file1.csv | ForEach { 
$Object = New-Object PSObject -Property @{
        Time = $_.READ_DTTM
        Value = $_.{VALUE(KWH)}    
        Tag  = [String]::Concat($_.SUBSTATION,'_',$_.CIRCUITNAME,'_',$_.PHASE,'_',$_.METERID,'_KWH')

    }   
    $objs += $Object;
} 
$objs 
$objs | Export-CSv -NoTypeInformation D:\TEP\FilesProcessing\Test\file1_out.csv

Bill_Stewart · Accepted Answer · 2018-09-13 21:06:25Z

1

You can combine Get-ChildItem and Import-Csv.

Here's an example that specifies different input and output directories to avoid name collisions:

$inputPath = "D:\TEP\FilesProcessing\Test"
$outputPath = "D:\TEP\FilesProcessing\Output"
Get-ChildItem (Join-Path $inputPath "*.csv") | ForEach-Object {
  $outputFilename = Join-Path $outputPath $_.Name
  Import-Csv $_.FullName | ForEach-Object {
    New-Object PSObject -Property @{
      "Time"  = $_.READ_DTTM
      "Value" = $_.{VALUE(KWH)}
      "Tag"   = "{0}_{1}_{2}_{3}_KWH" -f $_.SUBSTATION,$_.CIRCUITNAME,$_.PHASE,$_.METERID
    }
  } | Export-Csv $outputFilename -NoTypeInformation
}

Note that there's no need for creating an array and repeatedly appending it. Just output the custom objects you want and export afterwards.

edited Sep 13, 2018 at 21:06

answered Sep 13, 2018 at 20:57

Bill_Stewart

24.9k5 gold badges55 silver badges70 bronze badges

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

Comments

Dave Sexton · Accepted Answer · 2018-09-13 21:11:42Z

1

Use the Get-Childitem and cut out all the unnecessary intermediate variables so that you code it in a more Powershell type way. Something like this:

Get-CHhilditems 'D:\TEP\FilesProcessing\Test\*.csv' | % {
    Import-csv $_.FullName | % {
        New-Object PSObject -Property @{
            Time = $_.READ_DTTM
            Value = $_.{VALUE(KWH)}    
            Tag  = '{0}_{1}_{2}_{3}_KWH' -f $_.SUBSTATION, $_.CIRCUITNAME, $_.PHASE, $_.METERID
        }
    } | Export-CSv ($_.FullName -replace '\.csv', '_out.csv') -NoTypeInformation
}

answered Sep 13, 2018 at 21:11

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

5 Comments

fokusfocus Over a year ago

Thanks! There's a typo on the Get-Childitem , but once that's fixed it works! :)

Bill_Stewart Over a year ago

This would seem to be a rather copycat answer of what I already wrote...

Dave Sexton Over a year ago

Yes I know - spooky. But arrived at independently I can assure you. Great minds think alike!

Bill_Stewart Over a year ago

I am concerned about a name collision in this solution. If the processing creates an _out.csv file in the same directory, it would appear that Get-ChildItem would enumerate it and the loop would continue indefinitely.

Dave Sexton Over a year ago

Agreed, I wouldn't do that way, but I left in to mirror the output of the question.

Jordan · Accepted Answer · 2018-09-13 21:24:30Z

The Get-ChildItem is very useful for situations like this.

You can add wildcards directly into the path:

Get-ChildItem -Path D:\TEP\FilesProcessing\Test\*.csv

You can recurse a path and use the provider to filter files:

Get-ChildItem -Path D:\TEP\FilesProcessing\Test\ -recurse -include *.csv

This should get you what you need.

$Props = @{
    Time = [datetime]::Parse($_.READ_DTTM)
    Value = $_.{VALUE(KWH)}
    Tag  = $_.SUBSTATION,$_.CIRCUITNAME,$_.PHASE,$_.METERID,'KWH' -join "_"
}  
$data = Get-ChildItem -Path D:\TEP\FilesProcessing\Test\*.csv | Foreach-Object {Import-CSV -Path $_.FullName}
$data | Select-Object -Property $Props | Export-CSv -NoTypeInformation D:\TEP\FilesProcessing\Test\file1_out.csv

Also when using Powershell avoid doing these things:

$objs =@();
$objs += $Object;

Collectives™ on Stack Overflow

How do I process multiple CSV files in Powershell and give them output names?

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related