LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 11-03-2014, 03:08 AM   #1
vipinsqa
Member
 
Registered: Oct 2014
Posts: 40

Rep: Reputation: Disabled
Convert log to csv using shell script.


Hi All,

I am fetching some information from the webserver log and writing to a output file in shell script.
However, I need the information in the output file to be converted/present in a CSV format.
Can anyone please let me know how can I get this conversion done in Linux?

Kindly confirm.

Regards.
 
Old 11-03-2014, 03:31 AM   #2
sag47
Senior Member
 
Registered: Sep 2009
Location: Orange County, CA
Distribution: Kubuntu x64, Raspbian, CentOS
Posts: 1,831
Blog Entries: 36

Rep: Reputation: 451Reputation: 451Reputation: 451Reputation: 451Reputation: 451
You need to provide more information. Your script for fetching logs and a sample of some log entries would be a start. Also, post what you have tried to write so far. Generally the forum etiquette is to first produce what you have attempted and then seek feedback for improvement.

Last edited by sag47; 11-03-2014 at 03:34 AM.
 
Old 11-03-2014, 04:23 AM   #3
vipinsqa
Member
 
Registered: Oct 2014
Posts: 40

Original Poster
Rep: Reputation: Disabled
Hi,

Thanks for the reply. Below is what I have included in the script to fetch information from the log:-

cat /var/log/secure* | grep Accepted | awk '{print $1,$2,$3,$4,$6,$9,$10,$11,$12,$13}' > $HOME/MyLog/connected_host_$HOSTNAME.log

Now, the output log has rows captured like below which I need to convert to CSV:-

Jun 6 11:35:08 ibmhost Accepted biadmin from 192.168.0.86 port 56612
Jun 6 11:40:18 ibmhost Accepted root from 192.168.0.86 port 56649
Jun 6 16:17:26 ibmhost Accepted root from 192.168.0.86 port 57759

Please let me know in case any further information is required.

Regards.
 
Old 11-03-2014, 05:03 AM   #4
eklavya
Member
 
Registered: Mar 2013
Posts: 619

Rep: Reputation: 136Reputation: 136
It will depend on the field separator. What are the fields where you want separators.
Example : comma is the separator. if you want it like this
Code:
Jun 6 11:35:08, ibmhost, Accepted biadmin from, 192.168.0.86, port 56612
Run command
Code:
awk '/Accepted/{print $1,$2,$3",",$4",",$6,$9,$10",",$11",",$12,$13}' /var/log/secure* > "$HOME/MyLog/connected_host_$HOSTNAME.csv"
Just change the separator position and make your desired result.
 
1 members found this post helpful.
Old 11-03-2014, 05:23 AM   #5
sag47
Senior Member
 
Registered: Sep 2009
Location: Orange County, CA
Distribution: Kubuntu x64, Raspbian, CentOS
Posts: 1,831
Blog Entries: 36

Rep: Reputation: 451Reputation: 451Reputation: 451Reputation: 451Reputation: 451
Code:
cat /var/log/secure* | grep Accepted | awk 'BEGIN{OFS=","}; {print $1,$2,$3,$4,$6,$9,$10,$11,$12,$13}' | tee $HOME/MyLog/connected_host_$HOSTNAME.csv | tr ',' ' ' > $HOME/MyLog/connected_host_$HOSTNAME.log
There's an example using the OFS awk variable to change the output field separator to a comma which will generate CSV output. The tee command will write the CSV data to a file as well as stdout. The tr command will translate all commas to spaces which then gets written to a normal log file.
 
Old 11-03-2014, 05:33 AM   #6
vipinsqa
Member
 
Registered: Oct 2014
Posts: 40

Original Poster
Rep: Reputation: Disabled
That is awesome, thanks for the reply. This would certainly help.

Cheers!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
convert log file to CSV ablanco20 Linux - Newbie 3 07-23-2013 03:21 PM
aix shell script to convert 5 csv files to single excelsheet rajachan Programming 3 02-27-2013 09:39 PM
how to convert a simplte text file into csv using awk script certteam Linux - General 1 09-15-2010 01:23 AM
Filtering a CSV file from web log with shell script? Micro420 Programming 8 08-22-2007 04:13 AM
Script to convert csv 2 xls or odt xowl Linux - Software 1 01-16-2007 10:06 PM


All times are GMT -5. The time now is 11:04 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration