Solved

Convert date format and numbers in column on Linux

Posted on 2014-11-19
24
195 Views
Last Modified: 2014-11-26
Hi,
I have file on Linux with 50000+ lines like below:
Wed Nov 19 04:42:33 2014|1969929|877973|439.549|
Wed Nov 19 04:43:05 2014|1291231|969149|138.522|
Wed Nov 19 04:43:05 2014|969151|969149|5.884|

I need to do the following on Linux:
    First column convert to format "%Y%m%d%H%M%S"
    Second and third column divide by 1048576
    Forth column roundup

20141119044233|1.87|0.84|439


Thanks in advance
0
Comment
Question by:IKeystone
  • 13
  • 5
  • 4
  • +1
24 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 40453809
perl -aF'\W' -ne '$"="";printf "$F[6]${{Jan=>1,Feb=>2,Mar=>3,Apr=>4,May=>5,Jun=>6,Jul=>7,Aug=>8,Sep=>9,Oct=>10,Nov=>11,Dec=>12}}{$F[1]}@F[2..5]|%.2f|%.2f|%.d\n",(map$_/1048576,@F[7,8]),$F[9]' file
0
 
LVL 12

Expert Comment

by:tel2
ID: 40456346
Well said, ozo.

IKeystone, when you say "Forth column roundup", what do you mean in the context of your example, which has "439.549" as input and "439" as output?  Looks more like "truncate forth column to 0 decimal places", to me.
0
 
LVL 12

Expert Comment

by:tel2
ID: 40456397
Hi again IKeystone,

If you are wanting to round the forth column to the nearest integer, here are some slight modifications of ozo's code which seem to do it:

perl -aF'\W' -ne '$"="";printf "$F[6]${{Jan=>1,Feb=>2,Mar=>3,Apr=>4,May=>5,Jun=>6,Jul=>7,Aug=>8,Sep=>9,Oct=>10,Nov=>11,Dec=>12}}{$F[1]}@F[2..5]|%.2f|%.2f|%f\n",(map$_/1048576,@F[7,8]),$F[9]+$F[10]/1000' file

Here's the output:
20141119044233|1.88|0.84|440
20141119044305|1.23|0.92|139
20141119044305|0.92|0.92|6
If that's not what you want, please claify your requirements.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 84

Expert Comment

by:ozo
ID: 40456405
perl -aF'[^\w.]' -ne '$"="";printf "$F[6]${{Jan=>1,Feb=>2,Mar=>3,Apr=>4,May=>5,Jun=>6,Jul=>7,Aug=>8,Sep=>9,Oct=>10,Nov=>11,Dec=>12}}{$F[1]}@F[2..5]|%.2f|%.2f|%.f\n",(map$_/1048576,@F[7,8]),$F[9]'
0
 
LVL 12

Expert Comment

by:tel2
ID: 40456420
Well said again, ozo.

IKeystone, note that my '%f' was meant to read '%.f'.  But my code only works if the forth column has 3 decimal places (e.g. 5.5 would be considered the same as 5.005).  ozo's new solution doesn't have that limitation, though...and it's shorter...as usual.
0
 

Author Comment

by:IKeystone
ID: 40458710
Working fine. Can you change you script to have double digit month. Jan=01, etc ...
0
 
LVL 12

Expert Comment

by:tel2
ID: 40458722
Good to hear it, IKeystone.

I expect ozo will give you that fix shortly...if I don't beat him to it.

And would you want double digit days, too?  No extra charge if you do.  Just say the word.

Meanwhile...
What do you mean by "Forth column roundup", IKestone?  For example, do you want "5.499" to round down to "5" or up to "6"?  What about "5.001"?
0
 

Author Comment

by:IKeystone
ID: 40458743
Mean 4.589=5, 5.3467=5, 5.001=5, 116.5=117 (I think). Roundup to nearest integer
0
 
LVL 12

Expert Comment

by:tel2
ID: 40458747
Or more simply, "round to nearest integer", I guess.
0
 
LVL 12

Expert Comment

by:tel2
ID: 40458760
What was the answer to the question in my last post about "double digit days", IKeystone?

Why is it that I have to ask everything more than once?  I'm trying to prevent you from running into problems and having to come back and ask again.  Please make it easy for us to help you, especially when we are trying to go the extra mile for you.
0
 
LVL 12

Accepted Solution

by:
tel2 earned 250 total points
ID: 40458769
While I'm waiting for a response to my last post, try this:

perl -aF'[^\w.]' -ne '$"="";printf "%d%02d%02d%02d%02d%02d|%.2f|%.2f|%.f\n",$F[6], ${{Jan=>1,Feb=>2,Mar=>3,Apr=>4,May=>5,Jun=>6,Jul=>7,Aug=>8,Sep=>9,Oct=>10,Nov=>11,Dec=>12}}{$F[1]},@F[2..5],(map$_/1048576,@F[7,8]),$F[9]' file
0
 
LVL 84

Expert Comment

by:ozo
ID: 40459074
perl -aF'[^\w.]' -ne '$"="";printf "$F[6]${{Jan=>"01",Feb=>"02",Mar=>"03",Apr=>"04",May=>"05",Jun=>"06",Jul=>"07",Aug=>"08",Sep=>"09",Oct=>10,Nov=>11,Dec=>12}}{$F[1]}@F[2..5]|%.2f|%.2f|%.f\n",(map$_/1048576,@F[7,8]),$F[9]' file
0
 
LVL 19

Expert Comment

by:simon3270
ID: 40459992
Alternatively, just using standard commands ("date" to convert the date, "bc" to handle the division, and printf to do the rounding (bc just truncates)):
OIFS=$IFS
IFS='|'
while read dat; do
  set $dat
  d1=$(date -d "$1" '+%Y%m%d%H%M%S')
  d2=$(echo "scale=5;$2/1048576.0" | bc)
  d3=$(echo "scale=5;$3/1048576.0" | bc)
  printf "%s|%0.2f|%0.2f|%0.0f\n" "$d1" "$d2" "$d3" "$4"
done < file
IFS=$OIFS

Open in new window

(Note that printf rounds down when the fraction is exactly 0.5)
0
 
LVL 12

Expert Comment

by:tel2
ID: 40460919
Works for me, Simon.

> "(Note that printf rounds down when the fraction is exactly 0.5)"
Good point.  I never knew this.  Strange!  0.5 rounds down, and 1.5 etc rounds up!
$ printf "%1.0f\n" 0.5
0
$ printf "%1.0f\n" 1.5
2

What's that about?

Same thing happens via printf in Perl, so I maybe it uses the same code.

tel2
0
 
LVL 84

Expert Comment

by:ozo
ID: 40460955
The standard when rounding in a base that is a multiple of 2 but not a multiple of 4 (such as base 10 or base 2) is to round values that are half way between an even number and an odd number to the even number.
(see Knuth, Graham, et al.)
0
 
LVL 12

Expert Comment

by:tel2
ID: 40460963
Thanks ozo.  And that's what seems to be happening below.

$ printf "%1.0f\n" 2.5
2
$ printf "%1.0f\n" 3.5
4
$ printf "%1.0f\n" 4.5
4
$ printf "%1.0f\n" 5.5
6
0
 

Author Comment

by:IKeystone
ID: 40462713
Latest changes from ozo doesn't work.

syntax error at -e line 1, at EOF
Missing right curly or square bracket at -e line 1, within string
0
 
LVL 12

Expert Comment

by:tel2
ID: 40463120
Have you tried my amendments to ozo's code, IKeystone?  See post 40458769, above.  What is wrong with that code?

And I know I've already asked this twice, but I in the absense of an ansewer, could you please answer my question in post 40458760.

Thanks.
tel2
0
 

Author Comment

by:IKeystone
ID: 40463277
Sorry for misunderstanding. 40458769 working fine. And yes, I need double digit days and month
0
 
LVL 12

Expert Comment

by:tel2
ID: 40463346
Thanks for the clarification, IKeystone.  My version of ozo's code handles double digit months and days.

Does that cover your requirements, or have we missed anything?
0
 
LVL 84

Assisted Solution

by:ozo
ozo earned 250 total points
ID: 40464017
perl -aF'[^\w.]' -ne 'BEGIN{$"="";@m{qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec)}="01".."12"} printf "$F[6]$m{$F[1]}@F[2..5]|%.2f|%.2f|%.f\n",(map$_/1048576,@F[7,8]),$F[9]' file
0
 
LVL 12

Expert Comment

by:tel2
ID: 40467847
Thanks for the points, IKeystone.  Generous (to me) considering that ozo provided the basic code, but I guess some of my points are for nagging you to give us full spec's, which is all part of the work.  8)
Personally I think simon deserved some credit for providing a perfectly working solution too, although I expect the Perl solutions would be much faster (if speed is a consideration).

ozo, that's very concise/elegant, as usual.  Did you notice that it doesn't always output 2 digits for days though?  That is part of the final spec's.  For example, this input:
    Wed Jan 2 04:43:05 2014|969151|969149|5.4|
gives results in this output:
    2014012044305|0.92|0.92|5
but should result in this output:
    20140102044305|0.92|0.92|5

tel2
0
 
LVL 19

Expert Comment

by:simon3270
ID: 40467863
@tel2 - Maybe that's an argument for using the shell-based version. The  formatting is a little more visible, though I do think that  the Perl version is more concise! :-)
0
 
LVL 12

Expert Comment

by:tel2
ID: 40467878
True, Simon.  Concise, but cryptic.  Though it could be written more readably, starting by not writing it as a one-liner.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question