sunhux
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
(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
ASKER
Bear with me, I've never used an Excel Macro before.
How do I set a macro into action in Excel?
How do I set a macro into action in Excel?
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
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
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
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
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
. . .
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
. . .
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:
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
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
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
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
previous script give above be used now to extract
out & format the dates/timings?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
-Glenn
ASKER
excellent
ASKER
I was so blind as not to see the year in the output