Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
3 years ago
Solved

How to remove blank rows from an input flat file?

Hi Team,

How can I remove all the blank rows from an input flat file? I cannot use a condition restricted on a single column as the other records are needed. All I want to do is filter out ALL ROWS that doesn’t have any data (like Row 4, 5, 7, 8 shown in the screenshot below)

Desired Output:

Thus, final output should be:

Thanking in advance.

Regards,
DT

expression_language
pipeline
transform
  • ddellsperger's avatar
    ddellsperger
    3 years ago

    This is maybe a little bit difficult, but you can put the following filter after your parser
    The expression is: $.entries().filter(v => v[1] != "").length > 0 which will find any instance where all of the values for the given input document are all empty and filter them out.

18 Replies

  • Aleksandar_A's avatar
    Aleksandar_A
    Contributor III
    3 years ago

    Hi @darshthakkar,

    After parsing the file try using a filter with the following expression $ != {}.

    Let me know if this helps,

    BR,
    Aleksandar.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      Sure @AleksandarAngelevski , I will give it a try,
      I tried with isEmpty() both on filter and router but it didn’t work, was planning to put a condition on all the columns as the last resort (but that is not a good design to be honest).

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      This is not fetching the desired results.

  • ddellsperger's avatar
    ddellsperger
    Admin
    3 years ago

    This is maybe a little bit difficult, but you can put the following filter after your parser
    The expression is: $.entries().filter(v => v[1] != "").length > 0 which will find any instance where all of the values for the given input document are all empty and filter them out.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      @ddellsperger: Voila, it worked! Thank you for your assistance. I will now remove my conventional filter which has the condition of ! = null on each column in the file (it was never a good design but my only resort until we could find a best solution).

      Thanks again.

      BR,
      Darsh

      • darshthakkar's avatar
        darshthakkar
        Valued Contributor
        3 years ago

        @ddellsperger: Is it a safe assumption that the below solution provided by you is similar to the conventional filter I used (which was putting a NOT NULL condition on each column?) Q-1

        I believe my conventional filter would be a lot of overhead on the system + this time, I only had to deal with 10 columns (so 20 conditions, one as !=null and another as !=“”) however if I had to work on 350+ columns (which is my ideal day scenario), conventional filter would have busted.

        I want to take this opportunity to learn from an expert like you that what would be the overhead (time & space complexity) while using $.entries().filter(v => v[1] != “”).length > 0? Q-2

        Is the above a recommended approach and the ONLY approach to filter out blank rows from a flat file? Q-3

        Looking forward to hearing from you.

        BR,
        Darsh

      • darshthakkar's avatar
        darshthakkar
        Valued Contributor
        3 years ago

        Thank you @pmancevski for your suggestion and assistance on this one. I tested with your solution and unfortunately it didn’t work.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      Thank you @ddellsperger, I will definitely give this a try.
      Before posting this to community, I tried with $.filter((v,k)=> v != null) but didn’t get any success.

      Will keep you posted with my findings. Thanks again.

      Regards,
      Darsh

  • smudassir's avatar
    smudassir
    Employee
    3 years ago

    Will JSON.stringify($) != “{}” works? I feel this is simpler.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      @smudassir: I haven’t tried this but can surely give a try and keep you posted. Should the input stream be in any specific format?

      BR,
      Darsh