CODEWITHSUNDEEP
linuxshellcsvsedawk
DoiT International
DoiT International

Reputation: 2435

Converting multi-line log file to CSV

I have file which looks like following:

----------------------------------------
#Timestamp: 4/11/2013 12:00:48 AM
#Title: MDS error
#Category: Errors

----------------------------------------
#Timestamp: 4/11/2013 12:03:27 AM
#Title: MDS error
#Category: Errors

----------------------------------------
#Timestamp: 4/11/2013 12:05:39 AM
#Title: MDS error
#Category: Errors

----------------------------------------

I need to convert it to CSV file which looks like this:

4/11/2013 12:00:48 AM,MDS error,Errors
4/11/2013 12:03:27 AM,MDS error,Errors
4/11/2013 12:05:39 AM,MDS error,Errors

Need something being done as a command line (awk/sed)? since I have a series of files like this one which need to be converted to CSV.

Upvotes: 2

Views: 4657

Answers (7)

user2285887
user2285887

Reputation: 1

awk -F ": " '!(i%3)&&i{print s;s=i=""}/#/{s=s!=""?s","$2:$2;i++}'

Upvotes: 0

Thor
Thor

Reputation: 47089

Assuming each record only contains three rows, you can get away with cleaning the input and "pasting" it together:

<infile sed '/^---/d; /^ *$/d; s/[^:]*: *//' | paste -d, - - -

Output:

4/11/2013 12:00:48 AM,MDS error,Errors
4/11/2013 12:03:27 AM,MDS error,Errors
4/11/2013 12:05:39 AM,MDS error,Errors

If you have a variable number of rows, you could do it like this with GNU awk (perhaps mawk as well):

<infile awk 'NF>0 {gsub("\n\n+", "\n"); gsub("\n[^:]+: *", ","); sub(",",""); print}' RS='-{40}' ORS=''

The first substitution removes empty lines, the second replaces headers with comma, and the third removes an extraneous comma.

Upvotes: 0

Vijay
Vijay

Reputation: 67211

awk -F: '/^#Timestamp/{line=$2","}/^#Title/{line=line""$2}/^#Category/{print line","$2;}' your_file

Tested:

> cat temp
----------------------------------------
#Timestamp: 4/11/2013 12:00:48 AM
#Title: MDS error
#Category: Errors

----------------------------------------
#Timestamp: 4/11/2013 12:03:27 AM
#Title: MDS error
#Category: Errors

----------------------------------------
#Timestamp: 4/11/2013 12:05:39 AM
#Title: MDS error
#Category: Errors

----------------------------------------
> awk -F: '/^#Timestamp/{line=$2","}/^#Title/{line=line""$2}/^#Category/{print line","$2;}' temp
 4/11/2013 12, MDS error, Errors
 4/11/2013 12, MDS error, Errors
 4/11/2013 12, MDS error, Errors

A shorter solution if its ok for the OP:

awk -F: '/^#/{line=line","$2}/^-/{print substr(line,3);line="";}' your_file

Upvotes: 5

potong
potong

Reputation: 58351

This might work for you (GNU sed):

sed '/^#Timestamp:/{N;N;y/\n/,/;s/#[^ ]* //gp};d' file

Upvotes: 1

William
William

Reputation: 4925

Here's mine:

sed -ne '/----/{N;N;N;s/\n/,/g;s/[^:]*: \([^,]*,\)[^:]*: \([^,]*,\)[^:]*: \(.*\)/\1\2\3/;p;}' file

That does assume there are three lines of interest following the dashed line. If it's variable, some looping would have to happen.

Upvotes: 0

Chris Seymour
Chris Seymour

Reputation: 85765

$ awk -F": " '/^#T/{printf "%s,",$2}/^#C/{printf "%s\n",$2}' file
4/11/2013 12:00:48 AM,MDS error,Errors
4/11/2013 12:03:27 AM,MDS error,Errors
4/11/2013 12:05:39 AM,MDS error,Errors

Upvotes: 0

John Kugelman
John Kugelman

Reputation: 361565

#!/bin/bash

while true; do
    read             || break
    read _ timestamp || break
    read _ title     || break
    read _ category  || break
    read             || break

    printf '%s,%s,%s\n' "$timestamp" "$title" "$category"
done < logfile.txt

Upvotes: 1

Related Questions