Question about redirecting awk script output to a csv file?

xpac · 05-28-2015, 11:44 AM

Hi,
I have an awk script that reads through our text logs, and I would like to redirect that output to a csv file for easy use.

The output returns 3 columns separated by spaces in Linux as such:

number date username

However, when I redirect this to a csv file it puts all three values in the same Excel column. Is there an easy way to have each value show up in its own column in the csv sheet?

jlinkels · 05-28-2015, 12:20 PM

Two options:
a. Tell Excel the fields are separated by spaces. IIRC when you open a CSV, Excel asks you about filed separators and formats.

b. (The better option): insert commas between your values: number, date, username. The format is named Comma Separated Values for a reason.

I don't know what your awk script looks like, but the simplest way to acieve this is:

Code:

print $1 "," $2 "," $3

Assuming $1 is number, $2 is username etc.

jlinkels

xpac · 05-28-2015, 02:06 PM

Here's the script:

Code:

awk '
{       split($3, d, "@")
        match($0, /"username":"[^"]*"/)
        user = substr($0, RSTART + 12, RLENGTH - 13)
        c[d[1] OFS user]++
}
END {   for(i in c)
                printf("%4d %s\n", c[i], i)
}' company.log | sort -k3,3 -k2,2

grail · 05-28-2015, 02:11 PM

So change OFS to be "," and put a comma in your printf after 4d.

xpac · 05-28-2015, 02:31 PM

Awesome, thanks guys!

grail · 05-28-2015, 04:02 PM

Remember to mark as SOLVED when you have a solution