CODEWITHSUNDEEP
powershell
ca9163d9
ca9163d9

Reputation: 29179

Using powershell to transform CSV file

I have CSV files which have a lot of columns. I need to transform several columns, for example, some date columns have text string of "Missing" and I want to replace "Missing" to an empty string, etc.

The following code may work but it will be a long file since there are a lot of columns. Is it a better way to write it?

Import-Csv $file | 
  select @( 
      @{l="xxx"; e={ ....}}, 
      # repeat many times for each column....
  ) | export-Csv

Upvotes: 1

Views: 2001

Answers (2)

dan-gph
dan-gph

Reputation: 16909

You could use an imperative style rather than a pipelined style:

$records = Import-Csv $file

foreach ($record in $records) 
{
    if ($record.Date -eq 'Missing')
    {
        $record.Date = ''
    }
}

$records | Export-Csv $file

Edit: To use a pipelined style, you could do it like this:

import-csv $file | 
   select -ExcludeProperty Name1,Name2 -Property *,@{n='Name1'; e={"..."}},@{n='Name2'; e={'...'}}

The * is a wildcard that matches all properties. I couldn't find a way to format this code in a nicer way, so it is kind of ugly looking.

Upvotes: 3

Jon Tirjan
Jon Tirjan

Reputation: 3694

If all you want to do is a find-replace, you don't really need to read it as a CSV.

You could do this instead:

Get-Content $file | %{$_.ToString().Replace("Missing", "")} | Out-File $file

Upvotes: 1

Related Questions