Insert empty columns inside a pipe delimited file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Insert empty columns inside a pipe delimited file
# 1  
Old 12-08-2014
Insert empty columns inside a pipe delimited file

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

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

New file

Code:
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

Last edited by Don Cragun; 12-08-2014 at 01:26 AM.. Reason: Add CODE tags.
# 2  
Old 12-08-2014
Quote:
Originally Posted by Hypesslearner
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
Hello Hypesslearner,

Following may help you for provided input.

Code:
 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.
Code:
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.

Code:
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.
Code:
1
2
3
4
5
6
7
8
9
10
X
X
X
X
11
ABC

Thanks,
R. Singh

Last edited by RavinderSingh13; 12-08-2014 at 12:53 AM.. Reason: Added a solution check to check either empty fields are entered or not
# 3  
Old 12-08-2014
You could also try this:
Code:
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.
This User Gave Thanks to Don Cragun For This Post:
# 4  
Old 12-08-2014
This might work too
Code:
sed 's/|/|||||/10; s/$/|ABC/' file

# 5  
Old 12-08-2014
Without BEGIN Block
Code:
awk -F"|" '$11="||||"$11"|ABC"' OFS="|" filename


Last edited by Don Cragun; 12-08-2014 at 06:50 AM.. Reason: Fix CODE tags.
This User Gave Thanks to pravin27 For This Post:
# 6  
Old 12-10-2014
Hi All ,

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

Code:
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

Last edited by Don Cragun; 12-10-2014 at 03:43 AM.. Reason: Fix CODE tags.
# 7  
Old 12-10-2014
Quote:
Originally Posted by Hypesslearner
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
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).

Code:
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

Last edited by RavinderSingh13; 12-10-2014 at 03:41 AM.. Reason: Added code tags comment for user
This User Gave Thanks to RavinderSingh13 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

2. Shell Programming and Scripting

Shuffle Columns in Pipe delimited data

My sample data contains escape characters followed by delimiter. I'm stuck in writing awk comand to swap the columns. please help me out. Sample Data: 12345678|ABN\|XYZ MED CHEM PTY. LTD.|C||100.00|22|AB"C\|Corp|"XYZ|CDEF"| Expected Output Data: 12345678|C|ABN\|XYZ MED CHEM PTY.... (10 Replies)
Discussion started by: BrahmaNaiduA
10 Replies

3. Shell Programming and Scripting

Delete and insert columns in a tab delimited file

Hi all , I have a file having 12 columns tab delimited . I need to read this file and remove the column 3 and column 4 and insert a word in column 3 as "AVIALABLE " Is there a way to do this . I am trying like below Thanks DJ cat $FILENAME|awk -F"\t" '{ print $1 "\t... (3 Replies)
Discussion started by: Hypesslearner
3 Replies

4. Shell Programming and Scripting

Insert a value in a pipe delimited line (unsig sed,awk)

Hi, I want to insert a value (x) in the 3rd position of each line in a file like below a|b|c|d|1 a|b|c|d a|b|c|d|e|1 a|b|cso that output file looks like a|b|x|c|d|1 a|b|x|c|d a|b|x|c|d|e|1 a|b|x|cI can do that using perl as below #!/usr/bin/perl -w use strict; #inserting x at... (5 Replies)
Discussion started by: sam05121988
5 Replies

5. Shell Programming and Scripting

Oracle table extract: all columns are not converting into pipe delimited in flat file

Hi All, I am writing a shell script to extract oracle table into a pipe dilemited flat file. Below is my code and I have attached two files that I have abled to generate so far. 1. Table.txt ==> database extract file 2. flat.txt ==> pipe delimited after some manipulation of the original db... (5 Replies)
Discussion started by: express14
5 Replies

6. Shell Programming and Scripting

Remove few columns from pipe delimited file

I have file as below column1|column2|column3|column4|column5| fill1|fill2|fill3|fill4|fill5| abc1|abc2|abc3|abc4|abc5| . . . . i need to remove column2,3, from that file column1|column4|column5| fill1|fill4|fill5| abc1|abc4|abc5| . . . (3 Replies)
Discussion started by: greenworld123
3 Replies

7. Shell Programming and Scripting

Insert empty columns in a flat file

Hi, I have a tab delimited flat file, for example shown below Name Desg Loc a b c d e fI want to insert an empty column inbetween the column Desc and Loc, the result should be like shown below: Name LName Desg Loc a b c d e ... (6 Replies)
Discussion started by: sampoorna
6 Replies

8. Shell Programming and Scripting

how to Insert values in multiple lines(records) within a pipe delimited text file in specific cols

this is Korn shell unix. The scenario is I have a pipe delimited text file which needs to be customized. say for example,I have a pipe delimited text file with 15 columns(| delimited) and 200 rows. currently the 11th and 12th column has null values for all the records(there are other null columns... (4 Replies)
Discussion started by: vasan2815
4 Replies

9. Shell Programming and Scripting

How to insert a sequence number column inside a pipe delimited csv file using shell scripting?

Hi All, I need a shell script which could insert a sequence number column inside a dat file(pipe delimited). I have the dat file similar to the one as shown below.. |A|B|C||D|E |F|G|H||I|J |K|L|M||N|O |P|Q|R||S|T As shown above, the column 4 is currently blank and i need to insert sequence... (5 Replies)
Discussion started by: nithins007
5 Replies

10. UNIX for Dummies Questions & Answers

Adding EMPTY columns to Tab-delimited txt file

Hi I have a txt file with 4 columns where I need to add 4 empty columns in the middle meaning that I need what is currently column 4 to be column 8 in a new file. The idea is that I have to use the file as input in a program that reads the data in column 1 and 8, so the content of the other... (8 Replies)
Discussion started by: Banni
8 Replies
Login or Register to Ask a Question