I need to create a SED command to modify an input file with some conditions explained below.

I have an input file something like below.

List item

1  ; 30000000004 ; 8417920 
2  ; 30000000004 ; 8170811 
10  ; 30000000004 ; 1581292
1001  ; 3000000000 ; 8285052

Below are few things I need to do with this file:

  1. Remove 1st line completely
  2. Retain only 2nd and 3rd number group separated by comma
  3. Append string ,DATABASE at the end of each line.

The output should look something like below.

List item

  • 1
    This is a job that is more suitable to be done by awk. Off top of my mind awk -F';' 'BEGIN{OFS=",";} NR>1{ print $2,$3,"DATABASE"}' filename – Valentin Bajrami Mar 20 '15 at 10:37
  • That works!! But I also want to remove the white spaces in my result. Currently it's printing something like ' 3000000004 , 8417,DATABASE' I need it to be 300000004,8417,DATABASE without any spaces. – Sandeep Dongapure Mar 20 '15 at 10:46
  • I posted the answer which removes leading whitespaces. – Valentin Bajrami Mar 20 '15 at 10:51

Assuming the output in the question is the desired output, you can try this.

awk -F';'  'BEGIN{OFS=",";} NR>1{gsub(/ /,""); print $2,$3,"DATABASES"}' filename | sed -e :a -e '$!N;s/\n/,/;ta' 

Probably this could be accomplished with awk only. Remove the sed part if you want lines in the same row.

Since SE seems to be having educative purposes, I'll do my best to explain what each flag on the above command does.

-F';' # Uses ; as --field-separator
BEGIN {OFS=",";} # This is a special pattern which runs prior any command that follows and it's output is not influenced by the rest of the program. Then we have ORS

ORS # Since the output should include a comma , we define the Output Record Separator to have comma as a value. The default value of ORS is a new line "\n"

NR>1 # Here we say to skip the first record. NR is also a special reserved variable that holds the total number of input records.

gsub() # Is a function which accepts a few parameters. If no field specified e.g $2 or $3 then gsub() function uses the entire record which is $0

Finally print $2,$3,"DATABASE" will print the specified fields and adds DATABASE at the end of the line.

print $2,$3 # Here we use comma to separate fields. The default FS (Field separator) delimiter is a single blank space ' '.

I hope this brief description clarifies things a bit.

| improve this answer | |

I’m assuming that you mean you want each line of input (after the header) to translate into one line of output (and not all run together on one line, as the first version of your question illustrated).  sed is a very good tool for that job; it can be done as

sed -e 1d -e "s/.* ; \(.*\) ; \(.*[^ ]\) */\1,\2,DATABASE/" filename


  • 1d means delete the first line, and
  •                 s/.* ; \(.*\) ; \(.*[^ ]\) */\1,\2,DATABASE/
    means break down every line (after the first) into
                    something1 ; something2 ; something3
    and then replace that with
    The .*[^ ] part is to exclude any trailing spaces from something3.
| improve this answer | |
  • Expressions can be separated by an ;, there's no need for multiple -e switches. -e '1d;s/.* ; (.*) ; (.*[^ ]) */\1,\2,DATABASE/' will work as well – bsd Mar 20 '15 at 11:22
  • Yes, I know. Functionality is not the only goal here at Stack Exchange; we also strive for education and explanation. I thought it would be clearer to make the two commands separate — especially since semicolons appear within one of the commands. And now let me teach you something: if you have only "expression" or "script", you can leave out the -e, so your suggestion is three characters longer than it needs to be. – Scott Mar 20 '15 at 11:28

As in comments awk solution:

awk '{ FS=";"; OFS=","; gsub(/^[ \t]+/, "", $2); gsub(/[ \t]+$/, "", $2); gsub(/^[ \t]+/, "", $3); gsub(/[ \t]+$/, "", $3); } NR > 1 { print $2, $3, "DATABASE" }' file
| improve this answer | |

I would probably do:

cut -d' ' -f3,5 <infile | 
sed -n 'y/ /,/;1!s/$/,DATABASE/p'

...as I think it is probably close the most efficient solution to this problem.

| improve this answer | |

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.