1

I have a very big CSV log file that contains fields like this:

aaa=somedata1,bbb=somedata2,ccc=somedata3,eee=somedata5,hhh=somedata8
aaa=somedata1,ddd=somedata4,fff=somedata6,hhh=somedata8
aaa=somedata1,bbb=somedata2,hhh=somedata8,ggg=somedata9,jjj=somedata11

The problem with this file is that the generating device does not even include the "fieldname=" when there are no values, so, the CSV looks disordered thanks to the missing fields (so, every time a field is missing, the rest of the present fields gets dragged to the left of the CSV).

I had the idea to extract only certain columns that are relevant using AWK and also I need to output it into a new CSV.

For example, in the above example I'd like to extract all columns that include the fields "aaa" and "hhh" to make the new CSV look like this:

aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8

However, I have two problems:

  1. I don't know how to look for several conditions in AWK (I even tried writting down the names of the fields/keywords that I need into a TXT file and read it inside AWK, but I couldn't do it).
  2. Every time I try to print the resulting columns, the new CSV only prints a single giant column, and I can't seem to find a way to print separating for columns.

Appreciate any help!

---EDIT1---

Yup, I tried using a number of separate AWK commands kinda like this:

awk '{for (i=1;i<=NF;i++) if ($i ~ /aaa/) { print $i}}' > aaa.csv
awk '{for (i=1;i<=NF;i++) if ($i ~ /hhh/) { print $i}}' > hhh.csv

then tried using (of course I have a total of 10 different columns I'm interested in extracting, but for brevity reasons I only put 2 in the example):

paste -d "," aaa.csv hhh.csv > Allcolumns.csv

---EDIT2---

I have a total of ~10 relevant columns that I want to extract into a new file, since the original file is a log, I made sure of which columns appear on all lines and those are the ones I actually need. If by any chance they do not appear on the original file, I guess the best course of action would be to have the final file to reflect something like "aaa,hhh,,iii".

4
  • What should the output be for a row that has no aaa= or hhh= field? – Ed Morton Feb 26 at 2:16
  • Since you say "Every time I try to print the resulting columns", you seem to have tried AWK already. Can you add the AWK program to your question? – berndbausch Feb 26 at 3:57
  • If you are only interested in lines that contain both aaa= and hhh=, you could identify those lines with AWK pattern /aaa=/ && /hhh=/, then in the corresponding action extract the two columns using string matching functions. – berndbausch Feb 26 at 4:00
  • Hi, I edited the post for more info. – Bison-Ex1 Feb 26 at 5:15
2

Any time you have tag=value pairs in your data I find it best to first create an array to hold that mapping (tag2val[] below) and then you can reference all of the values by their tags (aka names or keys).

Using any awk in any shell on all Unix boxes:

$ cat tst.awk
BEGIN {
    FS = OFS = ","
    numTags = split("aaa,hhh",tags)
}
{
    delete tag2val
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        tag2val[tag] = $i
    }

    for (i=1; i<=numTags; i++) {
        tag = tags[i]
        printf "%s%s", tag2val[tag], (i<numTags ? OFS : ORS)
    }
}

$ awk -f tst.awk file
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8

If you want to print all possible fields on every line then that's a 2-pass approach where the first pass is just identifying all possible fields from every line:

$ cat tst.awk
BEGIN {
    FS = OFS = ","
}
NR==FNR {
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        if ( !seen[tag]++ ) {
            tags[++numTags] = tag
        }
    }
    next
}
{
    delete tag2val
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        tag2val[tag] = $i
    }

    for (i=1; i<=numTags; i++) {
        tag = tags[i]
        printf "%s%s", tag2val[tag], (i<numTags ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
aaa=somedata1,bbb=somedata2,ccc=somedata3,eee=somedata5,hhh=somedata8,,,,
aaa=somedata1,,,,hhh=somedata8,ddd=somedata4,fff=somedata6,,
aaa=somedata1,bbb=somedata2,,,hhh=somedata8,,,ggg=somedata9,jjj=somedata11

If you just want to print the fields that occur in all lines:

$ cat tst.awk
BEGIN {
    FS = OFS = ","
}
NR==FNR {
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        cnt[tag]++
    }
    next
}
FNR==1 {
    for (tag in cnt) {
        if ( cnt[tag] == (NR-1) ) {
            tags[++numTags] = tag
        }
    }
}
{
    delete tag2val
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        tag2val[tag] = $i
    }

    for (i=1; i<=numTags; i++) {
        tag = tags[i]
        printf "%s%s", tag2val[tag], (i<numTags ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
hhh=somedata8,aaa=somedata1
hhh=somedata8,aaa=somedata1
hhh=somedata8,aaa=somedata1

If the order of fields output matters that's an easy tweak too, e.g. to retain input order you'd just create an array in the first block to map an incrementing count to each new tag as it's seen:

$ cat tst.awk
BEGIN {
    FS = OFS = ","
}
NR==FNR {
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        if ( !cnt[tag]++ ) {
            order[++totTags] = tag
        }
    }
    next
}
FNR==1 {
    for (i=1; i<=totTags; i++) {
        tag = order[i]
        if ( cnt[tag] == (NR-1) ) {
            tags[++numTags] = tag
        }
    }
}
{
    delete tag2val
    for (i=1; i<=NF; i++) {
        tag = $i
        sub(/=.*/,"",tag)
        tag2val[tag] = $i
    }

    for (i=1; i<=numTags; i++) {
        tag = tags[i]
        printf "%s%s", tag2val[tag], (i<numTags ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
5
  • Thanks Ed, do you think the tags arguments can be 10 in total? – Bison-Ex1 Feb 26 at 5:22
  • Sure, it can be a million, it makes no difference as long as a line fits in memory. – Ed Morton Feb 26 at 7:57
  • 1
    nice solution. If the fields are always written in the correct order (but some fields are missing) by OP's program, you could even simpify it by replacing, in the first for loop, the last line tag2val[tag] = $i by if ( tag in numTags ) { nb++; printf "%s%s", $i, (nb<numTags ? OFS : ORS ) } (and add a : nb=0 before entering that first loop. And delete the second loop altogether. But it's arguably less readable and less modifyable – Olivier Dulac Feb 26 at 9:25
  • 1
    @OlivierDulac thanks. I find that any time the input has tag=value pairs starting by creating an array to contain those pairs makes for the clearest, most easy to maintain/enhance approach. To do what you suggested would require adding a loop in the BEGIN section as if (tag in numTags) wouldn't work, you'd need numTags = split("aaa,hhh",tmp); for (i in tmp) tags[tmp[i]]... if (tag in tags). You'd also have to handle the case where a tag was missing as then you wouldn't get an ORS sinc nb would never equal numTags on that lins. – Ed Morton Feb 26 at 14:10
  • 1
    you are right. I noticed the flaws of the missing RS if only 1 tag is viewed, but I incorrectly thought your solution also had that problem (I read too quickly during a work break!). Your way is clearer and surer. – Olivier Dulac Feb 26 at 16:55
1

If you have the option to use Miller, then your data is exactly in Miller's dkvp (key-value-pair) format and you could cut by field name directly:

$ mlr --dkvp cut -f aaa,hhh file.csv
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8

Alternatively, you could restore the missing fields using unsparsify:

$ mlr --dkvp unsparsify file.csv
aaa=somedata1,bbb=somedata2,ccc=somedata3,eee=somedata5,hhh=somedata8,ddd=,fff=,ggg=,jjj=
aaa=somedata1,bbb=,ccc=,eee=,hhh=somedata8,ddd=somedata4,fff=somedata6,ggg=,jjj=
aaa=somedata1,bbb=somedata2,ccc=,eee=,hhh=somedata8,ddd=,fff=,ggg=somedata9,jjj=somedata11
0
awk -F "," '{for(i=1;i<=NF;i++){if($i ~ /aaa|hhh/){print $i}}}' filename|sed "N;s/\n/,/g"

output

aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
aaa=somedata1,hhh=somedata8
1
  • Please note that this assumes that the pattern aaa and hhh cannot appear in the "value" part (after the =). – AdminBee Mar 1 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.