Home > Net >  Bash script: cut the next line in an excel and paste it in new column through command line
Bash script: cut the next line in an excel and paste it in new column through command line

Time:05-10

I have an excel, which I am using in my shell script where my data is like

ColumnName1
NameOfAnObject1
100
NameOfAnObject2
200

My requirement is to have the data as below

ColumnName1         ColumnName2
NameOfAnObject1     100
NameOfAnObject2     200

I have tried fetching the data as with below command awk '{getline nextLine; print nextLine,$1}' OFS="," file.csv

But the output that I get is

ColumnName1
NameOfAnObject1,100
NameOfAnObject2,200

CodePudding user response:

This may be what you're trying to do:

$ awk -v OFS='\t' 'NR==1{p=$1; sub(/1$/,2,$1)} NR%2{print p, $1} {p=$1}' file
ColumnName1     ColumnName2
NameOfAnObject1 100
NameOfAnObject2 200

If you're ever considering using getline then please read http://awk.freeshell.org/AllAboutGetline to understand why you probably shouldn't, as in this case.

The above script is just replacing 1 with 2 at the end of the first column header so it'll just reproduce the leading part of the column name rather than hard-coding what we think it should be.

CodePudding user response:

Using sed

$ sed '1s/\([^0-9]*\).*/&\t\12/;1!{/^[A-Z]/{N;s/\n/\t/g}}' input_file
ColumnName1     ColumnName2
NameOfAnObject1 100
NameOfAnObject2 200

CodePudding user response:

If your data is always formatted as described, you can do this:

(formatted with line breaks for clarity).

 awk '
   BEGIN {              # print headers and set separator
       OFS="\t"; 
       print "ColumnName1", "ColumnName2"
         } 
   NR>1{                # skip first line which is header
     getline N;         # read first col, this continues to next line
     print $0, N;       # print the line and data from line before
     N=""}              # ..do not keep the previous line anymore
   '  file.csv
ColumnName1             ColumnName2
NameOfAnObject1         100
NameOfAnObject2         200

The last setting N="" is to avoid having a remaining output of N at the end of the file (if your file ends with a linefeed.)

CodePudding user response:

If your input is, say, file.sample as below:

ColumnName1
NameOfAnObject1
100
NameOfAnObject2
200

then you can try this one-liner with sed:

sed -i '2i ColumnName2' file.sample && cat file.sample | sed 'N;s/\n/\t/g' > file.csv

If you cat the file.csv, you should get:

ColumnName1     ColumnName2
NameOfAnObject1 100
NameOfAnObject2 200

awk correctly interprets \t as field separator, if you do this:

awk -F"\t" '{ print $1 }' file.csv

Outputs:

ColumnName1
NameOfAnObject1
NameOfAnObject2
  • Related