Skipping empty CSV objects

December 4, 2012

12 comments

Hi, it’s been a while since my last post, I’ve been busy mostly at work and also by investing most of my time running the PowerShell Magazine website together with my friends. I hope to post more in the future. For the time being, here’s my latest post, cross-posted on the PowerShell Magazine.

Working with CSV files in PowerShell is a common practice. You import the file, loop on its records and you’re good to go. Sometimes however you may find yourself in a situation where you get a file that has blank lines in it, and those lines can break your script. Consider the following CSV content:

## sample.csv ##
column1,column2,column3
Value1,Value2,Value3
Value1,Value2,Value3


 
## file ends here

 

On the surface, nothing looks suspicious when you import the file:

PS> Import-Csv sample.csv
 
column1  column2  column3
-------  -------  -------
Value1   Value2   Value3
Value1   Value2   Value3
 


PS>

 

But if you pipe it to Format-List you can clearly see what’s going on. You get empty objects for each empty line in the file.

PS> Import-Csv sample.csv | Format-List
 
column1 : Value1
column2 : Value2
column3 : Value3
 
column1 : Value1
column2 : Value2
column3 : Value3
 
column1 :
column2 :
column3 :
 
column1 :
column2 :
column3 :
 
column1 :
column2 :
column3 :

 

To filter out empty objects you need to test that all properties are not equal to an empty string and throw them away.

You might be attempted to do that with:

Import-Csv sample.csv |
Where-Object {$_.column1 -ne '' -and $_.column1 -ne '' -and $_.column1 -ne ''}

 

But what if each record has 20 properties, or even more? This is where the PSObject property comes to rescue. In a nutshell, PSObject allows us to work with any object in the same way without really knowing its structure. PowerShell wraps the base object in a PSObject and provide us a simplified and consistent view of the object, its methods, properties, and so on. One of the properties of PSObject is Properties, and it gives us a list of properties of the base object.

On a related note, PSObject and other members are not visible when you pipe an object to the Get-Member cmdlet. To reveal those members add the -Force switch to Get-Member.

For our purpose, we can process the properties list and filter out those who have a Value of null.

Import-Csv sample.csv |
Where-Object { ($_.PSObject.Properties | ForEach-Object {$_.Value}) -ne $null} |
Format-List
 
column1 : Value1
column2 : Value2
column3 : Value3
 
column1 : Value1
column2 : Value2
column3 : Value3

 

In PowerShell 3.0 and the new Member Enumeration feature we can get the same result in less characters:


Import-Csv sample.csv |
Where-Object { $_.PSObject.Properties.Value -ne $null}

I logged an Import-Csv feature enhancement, and you can add your vote if you’d like to have a built-in option to ignore empty lines.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

12 comments

  1. プーマAugust 16, 2013 ב 12:01

    Worse is their Facebook page. Though they have to just start responding to peoples posts on the page, the department just seems indifferent.
    [url=http://www.kakushoesjp.com/]プーマ[/url]
    プーマ

    Reply
  2. Lululemon OutletAugust 16, 2013 ב 15:45

    Here’s Tip #1 from our 10-tip post on July 25th – Use them to hang just about anything!? (Numbered guide to the photo is below.)
    Lululemon Outlet http://www.calululemonoutlet4s.ca

    Reply
  3. ナイキ SB SERIESAugust 16, 2013 ב 16:04

    When stopped by officers, Gifford ignored officer commands that included “lower your hands,” put ’em down” and “put it down.”
    ナイキ SB SERIES http://www.japanoutdoorstore.com/ナイキ%20SB%20SERIES-451/

    Reply
  4. Lululemon Outlet CanadaAugust 16, 2013 ב 16:08

    Born in Bartholomew Close, London, William Hogarth was the son of an impoverished teacher of Latin who spent time in the Fleet Prison for debt, and founded a coffee house at St John’s Gate where only Latin was spoken. It was a commercial failure. A Freemason, William married Jane Thornhill in 1729, the daughter of Sir James Thornhill, also an artist. The childless couple fostered foundling children, and took a keen interest in the affairs of Captain Coram’s Foundling Hospital.Had it been created in an earlier century, it would scarcely have been regarded as a legitimate pictorial enterprise at all because at first glance it seems to have almost no theme. It consists of little other than this extraordinarily tall screen of forest, so dense that we have no hope of seeing through it to a less oppressive scene. We want the scene to rise and fall – that would give us pleasure – but it does not do so. It comes on at us, relentlessly foreboding; in fact, it almost presses against our eyeballs. No, nothing is stirring here.
    Lululemon Outlet Canada http://www.calululemonoutletz.ca

    Reply
  5. トリーバーチ 財布August 16, 2013 ב 16:23

    Alan Gross, 61, who worked for a Washington-area company contracted under a US Agency for International Development programme to promote democracy in Cuba, was arrested at the Havana airport on December 3 and has been held on suspicion of espionage and subversion.
    トリーバーチ 財布 http://www.barnesburch.tk/

    Reply
  6. レイバン メガネAugust 16, 2013 ב 16:24

    It was Goyals dedication that paid off. “I studied for long hours. My parents would often pressurise me to stop studying,” she said. Of the six subjects, Goyal has scored 99 per cent marks in Science and Sanskrit. For Goyal, the success was unexpected. “I was expecting to score around 94 per cent but so this is a surprise,” she said. Goyal plans to take up Science. She has now set her sights on getting into the Indian Institute of Technology.With Diwali round the corner, parents embroiled in divorce petitions or those already divorced are moving the family court seeking permission to visit their children. However, those working for child rights say many parents may not get visitation rights, as the court will be on vacation from Friday.
    レイバン メガネ http://www.banwinstone.tk

    Reply
  7. Lululemon CanadaAugust 16, 2013 ב 17:12

    “It’s more important for a manager to know that we are in a jobwhere any day could be your last day.”
    Lululemon Canada http://www.lululemon.com

    Reply
  8. Toms OutletAugust 16, 2013 ב 17:49

    And Rick Chavis? His jury didn’t like him — but they didn’t think there was enough evidence that he swung the bat, so Chavis was acquitted. The courts weren’t through with Chavis. A few months later he was charged with lewd and lascivious battery involving Alex. Again, he was acquitted.
    Toms Outlet http://jetsouth-bhm.com

    Reply
  9. ELECOM ヘッドホンAugust 21, 2013 ב 07:36

    Suspension:(Front) MacPherson Strut, (Rear) Torsion beam
    ELECOM ヘッドホン http://www.japanbrandoutlet.com/ELECOM-2457/

    Reply
  10. 夏のセール MANHATTAN PORTAGE /マンハッタンポーテージAugust 21, 2013 ב 18:06

    Ryan, a Republican, was convicted in 2006 of racketeering, conspiracy, fraud and other offenses involving favoritism and kickbacks for state contracts and property leases. He went to prison in November of 2007.

    Reply
  11. サングラスAugust 22, 2013 ב 07:12

    Like Romney, Obama has been tough on China during the campaign, launching a World Trade Organisation challenge against Beijing’s subsidies on autos and car parts.
    サングラス http://www.raysbarreto.tk

    Reply
  12. ディズニー(disney)正規店 送料無料 安値August 28, 2013 ב 18:33

    4.Michael Jackson’s family, not the Kennedys, constitutes “American Royalty.”

    Reply