Need specific columns in a log file as excel.

Shell Programming and Scripting
1

Hi All...
I am in need of few columns from a log file.. in .xls file... below is what i have tried.

my log file has 16 colums with " ; " as delimiter, but i need randomn columns 1 2 3 4 5 6 10 11 16 in an excel.

I tried to awk the columns with delimiter ; and it worked, below is the log after awk.

awk -F'; ' '{ print $1,$2,$3,$4,$5,$6,$10,$11,$13,$16 }' Report.log 

  

Sun Aug 10 00:02:06 2014  status  ACK  Administrator admin  0  Sat Aug 09 23:17:41 2014  loret   ctn1029  MAJOR
Sun Aug 10 00:02:17 2014  ticket_number  Administrator admin  0  Sat Aug 09 23:17:41 2014  loret   ctn1029  MAJOR
Sun Aug 10 00:04:21 2014  status  ACK  Administrator admin  0  Sat Aug 09 23:53:20 2014  loret  ctn68  WARNING
Sun Aug 10 00:04:21 2014  status  ACK  ldapuser1 admin  0  Sat Aug 09 23:53:20 2014  loret  ctn1342  WARNING

Now i need this in an excel... I tried tr command but did not work, i am unable to understand the delimiter ... tried with /t but dint help.. please suggest...

Here data in columns are

column 1: Sun Aug 10 00:02:05 2014
Column 2:  status
Column3: ACK
Column4 Administrator
Column5: admin
Count6: 0 
Column7: Sat Aug 09 23:53:20 2014  
Column8: loret  
Column9: ctn1342  
Column10: WARNING

OR..

can AWK give us output with delimiter... ? this is also fine with me as i can use tr convert to excel...

Thanks in Advance !!!

2

The default value of the field separator FS is a string " " containing a single space, awk splits an input record into fields is controlled by the field separator. In your case if you want to open in excel try to create file using double quotes for example as you shown column 1 should be field1 to field5 would be print "\"" $1, $2, $3, $4, $5 "\"" "," where column separator is comma

1 Like
3

If that doesn't work, you could try one of these:-

  • save the file as a .txt and when you open it with Excel you will be prompted for information including the delimiters. You can set a space there, but it will compromise your date/time field
    .
  • Assuming that there are no other ; in the input, just a simple cut -f 1-6,10,11,16 -d";" input-file | tr ";" "," > output_file.csv

Do either of these options help?

Robin

1 Like
4

Both worked out.. Robin & Akshay... Thanks much...

Another doubt... I am trying to send this csv to email.. i used send mail and mailx but dint work... once i give this i dont get the prompt it gets struck there....

I am using Solaris.. Please suggest

uuencode <filename.txt>  | mail -s "subject" mailid
uuencode <filename.txt>  | mailx -s "subject" mail id
uuencode <filename.txt>  | sendmail -f <mailid> -v -t

Please suggest

5

Try this script

#!/bin/bash

from="sender@domain.org"
to="receiver@domain.org"
subject="test subject"
body="test message"

# string more detail read this https://tools.ietf.org/html/rfc2046#section-5.1.1
boundary="ZZ_/afg6432dfgkl.94531q"


# add attachements...here...
attachments=( "adb.log" "mail.sh")



# function......
get_mimetype(){
  file --mime-type "$1" | sed 's/.*: //' 
}

# Build headers
{

printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"

--${boundary}
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

$body
"
 
# now loop over the attachments
for file in "${attachments[@]}"; do

  [ ! -f "$file" ] && echo "Warning: attachment $file not found, skipping" >&2 && continue

  mimetype=$(get_mimetype "$file") 
 
  printf '%s\n' "--${boundary}
Content-Type: $mimetype
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\"$file\"
"
 
  base64 "$file"
  echo
done
 
# print last boundary with closing --
printf '%s\n' "--${boundary}--"
 
} | sendmail -t -o
1 Like
6

I usually use comma delimited output for Excel you can do this by setting OFS or adding commas to your print line.

Mike

1 Like