asked on

Excel / script to convert date from MMM dd hh:mm:ss to dd/mm/yyyy hh:mm:ss

I have the following output from my Linux boxes & need to
use Excel to convert the dates to dd/mm/yyyy hh:mm:ss
(using Excel formula or Windows batch script)

Note that the Year is missing from Linux output below so
we'll assume the year is 2014 unless the date is later
than 20th Jun, then set the year to 2013:

lastlog |grep pts; lastlog |grep tty
cddadmin pts/2 172.22.1.55 Thu May 2 17:43:46 +0800 2013
SysAdm1 pts/1 172.22.112.95 Sat Feb 1 07:52:49 +0800 2014
dduser pts/0 172.22.155.21 Tue Mar 12 12:10:25 +0800 2013
admin pts/0 172.22.122.91 Fri Jun 20 11:27:22 +0800 2014

SOLUTION

Regnillobian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunhux

ASKER

Oh, sorry, use the year as shown in the outputs.

I was so blind as not to see the year in the output

sunhux

ASKER

So, pls ignore the paragraph (& use the years given in the outputs):

"Note that the Year is missing from Linux output below so
we'll assume the year is 2014 unless the date is later
than 20th Jun, then set the year to 2013"

SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunhux

ASKER

On checking further, there are 3 types of output format:
(outputs 1 & 3 has no IP address column)

Output 1:
=======
Server1      0      Success      cddadmin pts/2 vvloodb02 Thu Nov 22 12:41:02 +0800 2012

Output 2:
========
SysAdmin1 pts/2 172.20.128.98 Fri Jan 10 18:29:50 +0800 2014
oouser pts/2 172.20.2.41 Mon Apr 14 19:50:40 +0800 2014

Output 3:
=======
root tty1 Sat Jan 25 10:11:37 +0800 2014

sunhux

ASKER

Bear with me, I've never used an Excel Macro before.
How do I set a macro into action in Excel?

Glenn Ray

Don't worry about the macro yet. Based on what you just typed about the differing data outputs, my example would not work.

Please attach a more extensive example of the Linux output - something that better represents typical data. If you have a flat file, that would be optimal.

Regards,
-Glenn

sunhux

ASKER

Attached the actual Linux text output which
I need to extract & format into an Excel with
the following columns:

1. Server1 Userid Date (in dd/mm/yyyy hh:mm:ss)
2. Server2 Userid Date (in dd/mm/yyyy hh:mm:ss)

The timing is in 24hour format.

Referring to the attached output, some examples of
the servers are VVLSMTP02, VVLSMS01, VVLSASA02
MLnxid3.txt

sunhux

ASKER

Just to take the first 2 servers as example, the Excel output will be:

1 VVLSMTP02 cddadmin 01/02/2014 07:52:49
2 VVLSMTP02 AustraSysAdmin1 10/07/2013 10:02:45
3 VVLSMTP02 oouser 12/03/2014 12:10:25
4 VVLSMTP02 admin 22/05/2014 18:03:03
5 VVLSMTP01 cddadmin 22/11/2014 12:41:02
6 VVLSMTP01 AustraSysAdmin1 10/01/2014 18:29:50
. . .

Glenn Ray

This will take a slightly different approach since you have a header row for each server.

Is there any chance that the Linux output can be modified so that the first userid is not on the header row? Right now, it follows the "Success" string after a tab (ASCII char 9) character. Something like this would be preferable:

VVLSMTP02	0	Success	
cddadmin         pts/2    172.23.2.55      Thu May  2 17:43:46 +0800 2013	
AustraSysAdmin1  pts/1    172.23.128.98    Sat Feb  1 07:52:49 +0800 2014				
oouser           pts/0    172.23.195.22    Tue Mar 12 12:10:25 +0800 2013				
admin            pts/0    172.23.2.53      Thu May 22 18:03:03 +0800 2014				
				
VVLSMTP01	0	Success	
cddadmin         pts/2    VVLoedb02        Thu Nov 22 12:41:02 +0800 2012	
AustraSysAdmin1  pts/2    172.23.128.98    Fri Jan 10 18:29:50 +0800 2014				
oouser           pts/2    172.23.2.41      Mon Apr 14 19:50:40 +0800 2014				
admin            pts/1    172.23.2.53      Wed May 21 23:33:05 +0800 2014				
				
VVLSMS02	0	Success	
root	pts/0    Thu Sep 19 11:54:42 +0800 2013
admin	pts/0    Wed May 14 10:33:34 +0800 2014			
AustraSysAdmin1	pts/1    Fri Jan 10 18:04:50 +0800 2014

Open in new window

If so, that would simplify the coding needed to parse it out properly. If not, it can still be done; just needs to be processed differently.

-Glenn

Regards

sunhux

ASKER

It's difficult to remove the server name from the header row
as we used an automation tool to login to the Linux servers
(which number about 80-90 VMs) & this automation tool
generated the server name in the header row.

sunhux

ASKER

on 2nd thought, I can always manually edit that 80+ entries
just by hitting ENTER after the server name to move the rest
of the header row to next line. Shouldn't take much time.

To manually key in the date+time is the really time-consuming
part which your script/formula would be most helpful

SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunhux

ASKER

Excellent. So with this modified input text file, can the
previous script give above be used now to extract
out & format the dates/timings?

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

Hi, have you had a chance to review my latest solution? I think you'll find it meets all your needs and is set up to run right away. Let me know if you have any questions.

-Glenn

sunhux

ASKER

excellent