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

[jimmymj]

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

[cnamejj]

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.

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.

[Irithori]

My suggestion would be to not repair that broken export file,
but instead create a new query with the expected columns.

[jimmymj]

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

[jimmymj]

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?

[Irithori]

But if you checked the columns, then this means that this database has wrong year values.
This is probably serious enough for further investigation.

[jimmymj]

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

[jimmymj]

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

[cnamejj]

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.

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.

[jimmymj]

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.

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