Editing CSV File - Deleting Entire row based on condition VIA Powershell

Question

Need delete an entire row of an excel file (.CSV), when value of column A or value of Column C is null.

This is what i have so far :

$ab = New-Object -comobject excel.application 
$bc = $ab.Workbooks.Open("C:\Users\abc\Desktop\New.csv")
$cd = $bc.Worksheets.Item(1)
$used = $cd.usedRange 
$lastCellCnt = $used.SpecialCells($xlCellTypeLastCell) 
$row = $lastCell.row

From the above code i get the number of records, was thinking of applying a loop to delete the records. For example :

if A2 == null or C2 == null then delete entire row

Matt · Accepted Answer · 2015-04-24 15:37:28Z

You are currently trying to do this with the Excel COM object but your input file appears to be a CSV. If that is the case you do not need to use New-Object -comobject excel.application

Using Import-CSV in PowerShell is the preferential way of dealing with these. You don't mention the structure of the file but we are going to assume you have headers. If not this can be slightly tweaked.

$path = "C:\Users\abc\Desktop\New.csv"
$scrubbed = Import-CSV $path | Where-Object{$_.name -and $_.address}
$scrubbed | Export-Csv -NoTypeInformation $path

Assuming A and C have headers Name and Address this will only allow rows where both of those values contain data. A non zero length string evaluates to True in PowerShell which is how the Where can be terse by taking advantage of that. You could also read that clause as ($_.name -ne '') -and ($_.address -ne '')

Capturing the results into a temporary variable and then writing the edited changes back to same file using the partner cmdlet Export-CSV.

ConanW · Accepted Answer · 2015-04-24 15:50:32Z

I don't think I'd fire up Excel for this task; I find the Office COM apps a bit cumbersome to say the least.

If you're starting with a .csv, why not use Import-Csv to create a PowerShell array of your data?

Consider .\test.csv:

ColumnA,ColumnB,ColumnC
1A,1B,1C
,2B,2C
3A,,3C
4A,4B,
5A,5B,5C

Which Import-Csv .\test.csv | ft -a will import like this:

ColumnA ColumnB ColumnC
------- ------- -------
1A      1B      1C
        2B      2C
3A              3C
4A      4B
5A      5B      5C

You can filter for the existence of both A and C columns on import (ensuring that neither are null):

Import-Csv .\test.csv | Where-Object {$_.ColumnA -and $_.ColumnC} | ft -a

ColumnA ColumnB ColumnC
------- ------- -------
1A      1B      1C
3A              3C
5A      5B      5C

Assign this to a variable and then write it out to a new file:

$NewVar = Import-Csv .\test.csv | Where-Object {$_.ColumnA -and $_.ColumnC}

$NewVar | Export-Csv -NoTypeInformation .\newtest.csv

Collectives™ on Stack Overflow

Editing CSV File - Deleting Entire row based on condition VIA Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related