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 Mar 20, 2015 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. Mar 20, 2015 at 10:46
  • I posted the answer which removes leading whitespaces. Mar 20, 2015 at 10:51

4 Answers 4


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.


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.
  • 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, 2015 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, 2015 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

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.

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.