Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: removing empty lines before loading with sqlloader

    guys,
    i have a file which looks like this

    Code:
    60146639946|13417074|-6|26/04/2007|2|Beyond|10/08/2006|21:42:43|0|
    60165448061|3264226|438|03/05/2007|1|Powerpack|10/05/2003|17:09:20|0|
    60165448121|3264282|1565|17/05/2007|1|Beyond|10/04/2003|12:26:00|0|
    60146640016|13417113|2109|08/04/2007|2|Beyond|25/07/2006|15:24:04|0|
    |||||||||
    60163278361|9647465|2693|17/02/2007|2|Beyond|13/09/2006|22:34:17|0|
    60163402706|9647783|272|26/04/2007|1|Beyond|07/10/2005|21:53:56|0|
    60163403016|9647806|-199|11/04/2007|2|Beyond|06/03/2006|19:22:12|0|
    60165447541|3192388|516|15/04/2007|1|DiGi Prepaid|30/04/2003|15:01:49|0|
    60165446976|3191845|768|15/04/2007|1|Beyond|03/04/2003|16:59:26|0|
    60165412106|2915938|7108|31/05/2007|1|DiGi Prepaid|22/04/2003|13:03:53|0|
    |||||||||
    60162930541|14151880|-6|12/04/2007|2|Beyond|12/12/2006|13:56:03|0|
    
    60166573826|8978481|31|18/04/2007|1|Beyond|11/07/2005|17:27:08|0|
    60165415796|2960628|536|31/03/2007|2|DiGi Prepaid|27/05/2003|17:35:35|0|
    what i want to do is to remove the empty rows before i load it into the db... any unix command or sql loader command can do this ?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    grep -v -e '^|||||||||$' -e '^$' file1 >file2

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can simplify this to the following (also helpful if you don't know the number of | and spaces):
    Code:
    grep -v -E '^[| \t]*$ file1 > file2
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I think you meant
    grep -v -E '^[| \t]+$' file1 > file2
    However that doesn't eliminate blank lines (containing only linefeed)
    Maybe use this instead
    grep -v -E '^[| \t]+$|^$' file1 > file2

  5. #5
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    What about:
    Code:
    sed '/^[| \t]*$/d' file1 > file2
    Does it all in one (like Knuts solution does, I don't see why one should use the + instead of the * in this case?)...

    Regards

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Using * is better than + because it removes the blank lines as well in one shot.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Must be a problem with my grep command as it doesn't work for me with an *
    sed works fine with the *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •