Insert empty columns inside a pipe delimited file

Shell Programming and Scripting
1

Hi All ,

I have pipe delimiter file with 11 columns . I need to insert 4 empty columns after column 10 . and After 11 column I need to insert a column which is having the same value for all the rows .

My file

1|2|3|4|5|6|7|8|9|10|11

New file

1|2|3|4|5|6|7|8|9|10|||||11|ABC

Please can anyone give me a hint on how to work on this

Thanks

2

Hello Hypesslearner,

Following may help you for provided input.

 awk -F"|" '{for(i=1;i<=NF;i++){if(i==10){ORS="|||||";print $i} else {ORS="|";print $i}}} END{ORS="\n";print "ABC"}'  Input_file

Output will be as follows.

1|2|3|4|5|6|7|8|9|10|||||11|ABC

EDIT: Just adding here if we take taken above output in a file named check14 and then try to get the field's values as follows we can see empty fields have been enterd as follows.

awk -F"|" '{for(i=1;i<=NF;i++){if($i==""){print "X"} else {print $i}}}' check14 #### This code is to just check above solution if it inserts the empty fields. ###

Output will be as follows.

1
2
3
4
5
6
7
8
9
10
X
X
X
X
11
ABC

Thanks,
R. Singh

3

You could also try this:

awk '
BEGIN {	FS = OFS = "|" }
{	$11 = "||||" $11 "|ABC" }
1' file

If you are using a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

1 Like
4

This might work too

sed 's/|/|||||/10; s/$/|ABC/' file
5

Without BEGIN Block

awk -F"|" '$11="||||"$11"|ABC"' OFS="|" filename
1 Like
6

Hi All ,

I am trying to insert a column at the begining of the file using the code below

awk 'BEGIN {FS=OFS=","} {print  "Align",$3,$10,$9,$7,$6,$4,$2,$5,$11,$10,$11}' temp1.txt>temp.txt

But somehow the first column is not coming with the word ALIGN

Please help

Thanks

7

Hello Hypesslearner,

Please use code tags for commands and codes used in your posts as per forum rules.
You can try following for same(Not tested though).

awk 'BEGIN {FS=OFS=","} {$1="Align"; print $1,$3,$10,$9,$7,$6,$4,$2,$5,$11,$10,$11}' temp1.txt>temp.txt

Thanks,
R. Singh

1 Like
8

When given the input:

1,2,3,4,5,6,7,8,9,10,11,12

I would expect the output:

Align,3,10,9,7,6,4,2,5,11,10,11

from the awk script:

awk 'BEGIN {FS=OFS=","} {print "Align",$3,$10,$9,$7,$6,$4,$2,$5,$11,$10,$11}'

rather than:

ALIGN,3,10,9,7,6,4,2,5,11,10,11

but it is doing what I would expect.

What are you getting?

What did you expect to get?

What OS are you using?

1 Like
9

Thanks Singh for the quick reply , I am trying to extend the same code now as below

awk 'BEGIN {FS=OFS=","} 
{$1="Align"
$COMMA=" , , , , ,"
$SIT="SIT"; 
print $1,$3,$10,$9,$7,$6,$4,$2,$COMMA,$5,$HPR,$11,$12,$13,$14,$15,$16,$17}' EXPDATA.txt>temp.txt

I need to insert few more columns like SIT and commas also

But it is prinitng only SIT and empty columns in the output file

Please help

---------- Post updated at 02:59 AM ---------- Previous update was at 02:51 AM ----------

Hi Cragun,

Just to be detail , My requirement is as below

Input file fields  - 1,2,3,4,5,6,7,8,9,10,11,12
Ouput file fields should be - Align,10,9,7,6,4,2,,,,,5,SIT,11

I am trying to insert new fields and spaces basically .

Thanks

10

In the code:

awk 'BEGIN {FS=OFS=","} 
{$1="Align"
$COMMA=" , , , , ,"
$SIT="SIT"; 
print $1,$3,$10,$9,$7,$6,$4,$2,$COMMA,$5,$HPR,$11,$12,$13,$14,$15,$16,$17}' EXPDATA.txt>temp.txt

You have $COMMA where you probably just want COMMA , $SIT where you probably just want SIT , and you're using $HPR even though HPR has never been defined. You also don't need to assign constant values to variables every time through the loop; just do it once in the BEGIN section of your script. Maybe you want something more like:

awk '
BEGIN { FS=OFS=","
        COMMA=" , , , , ,"
        SIT="SIT"} 
{       $1="Align"
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,SIT,$11,$12,$13,$14,$15,$16,$17
}' EXPDATA.txt>temp.txt

but if you don't show us any sample input or desired output, we're just guessing.

1 Like
11

Wow That worked

---------- Post updated at 10:31 PM ---------- Previous update was at 09:34 PM ----------

Hi all,

Here is my code

awk '
BEGIN { 
   FS=OFS="|"
   COMMA="|||||"
        HPR="HPR"
 
 } 
{ 
   if (NR==1) {next}
   $1="Align"
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
}' $EXPDATAFILE> FORECAST_`echo $CURRFINYEAR`_$(echo `date +"%Y%d%m"`).txt

I need to do the same process for different files .

Like for One file i need to Print Align, another file it is realign

So i am trying to call this as a function in my code

 
File_Modify  ALIGN 
File_Modify REALIGN

But how to call these parameters inside thefunction

 
File_Modify {
awk '
BEGIN { 
   FS=OFS="|"
   COMMA="|||||"
        HPR="HPR"
 
 } 
{ 
   if (NR==1) {next}
   $1=?????
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
}' $EXPDATAFILE> $1_`echo $CURRFINYEAR`_$(echo `date +"%Y%d%m"`).txt
12

Hypesslearner,
I repeat:

Try something like:

File_Modify {
        awk -v aligntype="$1" '
        BEGIN { FS=OFS="|"
                COMMA="|||||"
                HPR="HPR"
        }
        NR==1 { next
        }
        {       print aligntype,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
        }' $EXPDATAFILE > "$1_${CURRFINYEAR}_$(date +%Y%d%m).txt"
}

Note that if you use date +%Y%m%d (instead of date + %Y%d%m ), an alphabetical sort of your filenames (or the normal output of ls ) will list your files for the same value of $CURFINYEAR in increasing date order. (If you later want to look at the last x days' files, this change might make your life easier.)

1 Like