Find the answer to your Linux question:
Results 1 to 10 of 10
After fastexport, my flat data set looks wired. e.g (I did TRIM (BOTH FROM CAST (month AS CHAR(6)))) I expected as follows(sample); month employee Fname 201201 1122 james 201201 2233 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24

    how to correct bad data (sed , tr, awk or other command) if statement?


    After fastexport, my flat data set looks wired.
    e.g (I did TRIM (BOTH FROM CAST (month AS CHAR(6))))

    I expected as follows(sample);
    month employee Fname
    201201 1122 james
    201201 2233 jim
    201201 3322 peter
    201201 5566 kim
    201201 7777 will

    but output as follows;

    month employee Fname
    5201201 1122 james
    7201201 2233 jim
    201201 3322 peter
    201201 5566 kim
    8201201 7777 will

    I want to use sed or tr or .... put some other condition like lf statement to have the expected output.

    can i use sed or tr or ...to do as below;
    for example, if month =201201 then whole line > filename.dat
    else cut -c1-50 and line > filename.dat


    Thanks

  2. #2
    Linux Newbie
    Join Date
    Jun 2012
    Location
    SF Bay area
    Posts
    196
    I'm not completely sure I understand what you want to do, but from the example logic is sounds like you want to only save the lines where the first column is "201201". If so, then this awk command will work.

    Code:
    awk '$1 == "201201"' > filename.dat
    It works since the default action to take when a record matches the '$1 == "201201"' criteria is to print the line.

  3. #3
    Trusted Penguin Irithori's Avatar
    Join Date
    May 2009
    Location
    Munich
    Posts
    3,412
    My suggestion would be to not repair that broken export file,
    but instead create a new query with the expected columns.
    You must always face the curtain with a bow.

  4. $spacer_open
    $spacer_close
  5. #4
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    what about other records, also I want to print the line as well
    month |employee| Fname
    5201201 |1122 |james -- want to print out as well(after remove 5)
    7201201 |2233 |jim -- want to print out as well(after remove 7)
    201201 |3322 |peter -- want to print out as well
    201201 |5566| kim -- want to print out as well
    8201201 | 7777 | will -- want to print out as well (after remove

    please advise me

  6. #5
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by Irithori View Post
    My suggestion would be to not repair that broken export file,
    but instead create a new query with the expected columns.
    I checked the expected columns(50 columns). exact the same columns what I want. checked data type size and trim both records as well. how can I prevent the broken export file?

  7. #6
    Trusted Penguin Irithori's Avatar
    Join Date
    May 2009
    Location
    Munich
    Posts
    3,412
    But if you checked the columns, then this means that this database has wrong year values.
    This is probably serious enough for further investigation.
    You must always face the curtain with a bow.

  8. #7
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by Irithori View Post
    But if you checked the columns, then this means that this database has wrong year values.
    This is probably serious enough for further investigation.
    year values in database are correct. thanks

  9. #8
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    I tried as follows;
    awk '{if($1 != '201208');$1=='201208' print $0}' >test.dat //if not 201208 then fix correct on 201208 then print whole line

    but syntax error. can you help me

  10. #9
    Linux Newbie
    Join Date
    Jun 2012
    Location
    SF Bay area
    Posts
    196
    First of all, I strongly agree with Irithori and would personally not be comfortable fixing the data you're currently getting from the database. I'd have to know why the extra information is getting appended to the "month" field. I just wouldn't trust the data without figuring that problem out.

    Having said that, if you do need to clean up the data you have, I think this awk script will do what you want.

    Code:
    awk '{ if( length( $1) > 6) $1 = substr( $1, length( $1) - 5); for( np=1; np <= NF; np++) printf "%s ", $np; printf "\n"; }' > filename.dat
    That script assume any "month" field with more then 6 characters should have all but the last 6 characters lopped off the front.

    But to repeat, I really think you should find out how to get clean data from the database and not live with this mystery. I don't think accepting those sorts of mysteries is a good idea.
    Last edited by cnamejj; 11-06-2012 at 05:44 PM. Reason: fix broken [/CODE] tag

  11. #10
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by cnamejj View Post
    First of all, I strongly agree with Irithori and would personally not be comfortable fixing the data you're currently getting from the database. I'd have to know why the extra information is getting appended to the "month" field. I just wouldn't trust the data without figuring that problem out.

    Having said that, if you do need to clean up the data you have, I think this awk script will do what you want.

    Code:
    awk '{ if( length( $1) > 6) $1 = substr( $1, length( $1) - 5); for( np=1; np <= NF; np++) printf "%s ", $np; printf "\n"; }' > filename.dat
    That script assume any "month" field with more then 6 characters should have all but the last 6 characters lopped off the front.

    But to repeat, I really think you should find out how to get clean data from the database and not live with this mystery. I don't think accepting those sorts of mysteries is a good idea.
    Thanks. I will figure out how to get the clean data

Posting Permissions

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