LinuxQuestions.org - Convert into CSV file

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Convert into CSV file (https://www.linuxquestions.org/questions/programming-9/convert-into-csv-file-656058/)

Convert into CSV file

Hi All,

I have one file which has account names (Bold), successful (green colour)and failed (red colur)logins. Below is some extract of this file.

$cat logins
lbamcmbs
Successful Logins.
Internal: 12/07/2008 00:01:22 (10.56.73.107)
External: n/a (0)
Failed Logins.
Internal: n/a (0)
External: n/a (0)
ntrs4
Successful Logins.
Internal: n/a (0)
External: 15/07/2008 09:21:55 (192.77.161.22)
Failed Logins.
Internal: n/a (0)
External: n/a (0)

I want this file to be converted into CSV format which should have format of

AccountName,SuccessfulLogins,FailedLogins
lbamcmbs,Internal: 12/07/2008 00:01:22 External: n/a (0),Internal: n/a (0)External: n/a (0)
ntrs4,Internal: n/a (0) External: 15/07/2008 09:21:55 (192.77.161.22),Internal: n/a (0)External: n/a (0)

There are many accounts in this file. Can anyone please suggest me any script to get this work done. My manger wants it to be done by EOD and there are many accounts which will cost my whole day :(

Any help will be much appreciated.

Thanks
Ravi

Something like this may work:

Code:

awk 'BEGIN { 

  FS = ","

  print "AccountName,SuccessfulLogins,FailedLogins"

  }

!/Logins/ &&

    ORS = (/External/ && !(++c % 2)) ?

      RS : (!/Internal/ ? FS : OFS)

' logins

Quote:

Originally Posted by say_hi_ravi (Post 3216060)

My manger wants it to be done by EOD and there are many accounts which will cost my whole day :(

while radoulov has done it for you, however you should get your hands dirty since you are being paid to do the job. See my sig for shell scripting manual.

ghostdog74,

Completely agreed. I tried to write a code to fulfill my need. But I was not successful in that :(

Was thinking of taking account_name,successful logins, failed logins in different files and then merge them in one CSV file. I was able to parse output to three files but was getting stuck how to combine them. But at last your comment made me itching to complete my work. and i am glad i was successful. Thanks... you made my day.

My work around:

$cat logins|sed 's/,//g'|grep -v "Successful Logins."|grep -v "Failed Logins."|sed -n '1,${p,n;n;n;n;}' > account_name
$cat logins|sed 's/,//g'|grep -v "Successful Logins."|grep -v "Failed Logins."|sed -n '2,${p,n;n;n;n;}' > success_internal
$cat logins|sed 's/,//g'|grep -v "Successful Logins."|grep -v "Failed Logins."|sed -n '3,${p,n;n;n;n;}' > success_external
$cat logins|sed 's/,//g'|grep -v "Successful Logins."|grep -v "Failed Logins."|sed -n '4,${p,n;n;n;n;}' > fail_internal
$cat logins|sed 's/,//g'|grep -v "Successful Logins."|grep -v "Failed Logins."|sed -n '5,${p,n;n;n;n;}' > fail_external

it created 5 files

and then

$paste -d, account_name success_internal success_external fail_internal fail_external > final_result

It gave although not a desired result, but the result which i could produce, manager is happy with that :)

Thanks everyone. Thanks to radoulov. I have not tested this code but will soon be testing it. Curious to get understand each and every line of this code.

-Ravi

Note that the code I posted works with GNU Awk only. It will work with New Awk (nawk) and POSIX Awk (/usr/xpg4/bin/awk on Solaris) if the ternary operators do not span over multiple lines:

Code:

awk 'BEGIN { 

  FS = ","

  print "AccountName,SuccessfulLogins,FailedLogins"

  }

!/Logins/ && \

    ORS = (/External/ && !(++c % 2)) ? \

      RS : (!/Internal/ ? FS : OFS)

' logins

Regards
radoulov