0

I have a destination that consumes CSV files, and the 6th field contains words but the max char length is 16. If the field length exceeds 16 characters, I would like to duplicate the line and break it up without breaking the words.

Current File

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK LMNOP Q RS TUV W XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

Desired Output

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
  • 1
    What have you tried and where are you stuck? – Centimane May 30 at 15:15
  • May the fields of your csv files have embedded commas? Are all the fields always enclosed in double quotes? – fra-san May 30 at 15:55
3

Using GNU Awk (gawk) to run fold through a Getline/Variable/Coprocess

gawk -F, '
  BEGIN{
    OFS=FS; 
    cmd="fold -sw 16";
  }

  # if total length (16 + 2 for quotes) is within limit, print as-is
  length($NF) <= 18 {print; next}

  # else
  {
    # trim the quotes, then fold
    print substr($NF,2,length($NF)-2) |& cmd; 
    close(cmd,"to"); 
    NF--; 
    while((cmd |& getline var) > 0){

      # (optional) trim trailing whitespace
      sub(/[ \t]+$/,"",var);

      print $0, "\"" var "\"" ;
    }
    close(cmd,"from");
  }
' file.csv

The sub removes trailing whitespace from the fold operation.

Note that to get the precise output shown, one would need to use fold -sw17 to break at 16 characters plus the (subsequently removed) trailing space. However, doing so creates the possibility of exceeding the 16 character limit in the last line of folded output.

  • Nice. Use the right tool for the job. – glenn jackman May 30 at 17:12
  • Amazing, fold was exactly the right move. – Steve May 30 at 17:54
  • @Steve thanks - I'm slightly disappointed that there doesn't seem to be a way to make it omit the space that it splits at (necessitating the extra sub operation) – steeldriver May 30 at 18:45
1

I have created a pretty lame awk script that preserves the double quotes. Here it comes:

{
    for ( i=0; i<= length($6); i+=16 )
    {
        if ( i+17 < length($6) )
        {
            if ( i == 0 )
                printf ("%s,%s,%s,%s,%s,%s\"\n", $1, $2, $3, $4, $5, substr($6,i,16))
            else
                printf ("%s,%s,%s,%s,%s,\"%s\"\n", $1, $2, $3, $4, $5, substr($6,i+1,16))
        }
        else
        {
            if ( i == 0 )
                printf ("%s,%s,%s,%s,%s,%s\n", $1, $2, $3, $4, $5, substr($6,i,16))
            else
                printf ("%s,%s,%s,%s,%s,\"%s\n", $1, $2, $3, $4, $5, substr($6,i+1,16))
        }
    }
}

The output is:

$ awk -F, -f awks csvfields
"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5"," LMNOP Q RS TUV "
"1","2","3","4","5","W XYZ 12 3456 78"
"1","2","3","4","5","90"
"9","8","7","6","5","LMN O PQ R"
$

The only issue is that if there is a space at the boundary, it is preserved unlike the example where it has been removed.

0

Tried with below code and it worked fine too

 k=16;for ((j=1;j<=50;j++)); do  awk -v j="$j" -v k="$k" -F "," '{if(length($NF) > 16){print $1,$2,$3,$4,$5,substr($NF,j,k)}else {print $0}}' filename; j=$(($j+16)); done|sort | uniq

output

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
0

A SHELL only approach (tested on Bash and Ksh93). However I do like the fold approach as it uses an extant tool.

# read from stdin, output to stdout
# Note no Shebang line at top so it made it easier for to try bash/ksh as interpreters

OIFS="$IFS"
IFS=,
while read f1 f2 f3 f4 f5 f6; do
    f6=${f6#\"}
    f6=${f6%\"}             # strip DQs
    if ((${#f6}<17)); then  # no action
            IFS="$OIFS"
            echo "$f1,$f2,$f3,$f4,$f5,\"$f6\""
            IFS=","
            continue
    else
            IFS="$OIFS"
            while ((${#f6}>17)); do
                    n6=${f6:0:16}
                    f6=${f6#$n6}
                    n6=${n6# }
                    n6=${n6% }
                    echo "$f1,$f2,$f3,$f4,$f5,\"$n6\""
            done
            echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""
    fi
    IFS=","
done
IFS="$OIFS"
exit

Results:

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"

To address the word break issue without using fold or similar, the following code should replace the commented out line shown below in the above. Also replace the second echo command line with:

                    c6="$f6"
                    n6=""
                    while (((${#n6}+${#nw})<=16)); do
                            n6=$n6${c6%% *}\
                            n6=${n6# }
                            eval c6=\${c6\#${c6%% *} }
                            nw=${c6%% *}
                    done
                    #n6=${f6:0:16} ### replace by above

and replace

            echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""

with

            ((${#f6}>0)) && echo "$f1,$f2,$f3,$f4,$f5,\"${f6# }\""

to avoid any null field 6 remainders occurring.

The following test file was used:

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK LMNOP Q RS TUV W XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
"1","2","3","4","5","A BB CCC DDD EEEE FFFFF GGGGGG HHHHHHH"

with results:

"5","4","3","2","1","XYZ ABCD E"
"1","2","3","4","5","AB CDE F GHI JK"
"1","2","3","4","5","LMNOP Q RS TUV W"
"1","2","3","4","5","XYZ 12 3456 7890"
"9","8","7","6","5","LMN O PQ R"
"1","2","3","4","5","A BB CCC DDD"
"1","2","3","4","5","EEEE FFFFF"
"1","2","3","4","5","GGGGGG HHHHHHH"

The use of the existing tools like fold however is much easier and follows the UNIX philosophy -- build upon existing simple tools. But if you like Shell programming then the above is one way to get a solution. If anyone needs explanations of the code please contact me.

  • BTW, I believe that @unxnut answer/solution does not handle keeping "words" together based on the results output. I read the request as wanting to maintain words (which should therefore be <16 chars or my solution would fail). – PJF May 30 at 21:11
  • And my above comment means of course that the use of fold is a MUCH better approach since you can easily make sure that words are not broken up. My answer does not (yet) do that but does correctly do the test cases. – PJF May 31 at 9:09

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.