Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to insert a sequence number column inside a pipe delimited csv file using shell scripting?
# 1  
Old 09-24-2011
Java 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 numbers into it, so that the new data file becomes as shown below.
|A|B|C|1|D|E
|F|G|H|2|I|J
|K|L|M|3|N|O
|P|Q|R|4|S|T

If the data file has 'n' rows the last row must have n as the sequence number.
Please help!!
# 2  
Old 09-24-2011
Try:
Code:
awk -F\| -vOFS=\| '{$5=NR}1' file

This User Gave Thanks to bartus11 For This Post:
# 3  
Old 09-24-2011
Code:
awk -F\| '$5=NR' OFS=\| file

This User Gave Thanks to danmero For This Post:
# 4  
Old 09-24-2011
Question Thaaanks...

Thanks bartus11 Smilie ..
Now i have one more complication in the above question...Smilie

I have the dat file similar to the one as shown below..(column 5 will have only 7 or 8 and all the 7's and 8's are grouped)
|A|B|C||7|E
|F|G|H||7|J
|K|L|M||8|O
|P|Q|R||8|T

As shown above, the column 4 is currently blank and i need to insert sequence numbers into it, also now the sequence number depends upon the column 5, ie if the column 5 = 7 then column 4 will have one set of sequence numbers, for column 5 = 8 then again the sequence should start from 1 so that the new data file becomes as shown below.

|A|B|C|1|7|E
|F|G|H|2|7|J
|K|L|M|1|8|O
|P|Q|R|2|8|T
# 5  
Old 09-24-2011
Code:
awk 'BEGIN{FS=OFS="|"}{y=$6!=x?0:y;x=$6}$5=++y' file

This User Gave Thanks to danmero For This Post:
# 6  
Old 09-25-2011
Thank you Danmero Smilie

Previous Thread | Next Thread
Test Your Knowledge in Computers #959
Difficulty: Medium
The first virus attack against a Linux machine was by a virus called Stooge,
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace delimiter for a particular column in a pipe delimited file

I have an input file as below Emp1|FirstName|MiddleName|LastName|Address|Pincode|PhoneNumber 1234|FirstName1|MiddleName2|LastName3| Add1 || ADD2|123|000000000 Output : 1234|FirstName1|MiddleName2|LastName3| Add1 ,, ADD2|123|000000000 OR 1234,FirstName1,MiddleName2,LastName3, Add1 ||... (2 Replies)
Discussion started by: styris
2 Replies

2. Shell Programming and Scripting

Need a piece of shell scripting to remove column from a csv file

Hi, I need to remove first column from a csv file and i can do this by using below command. cut -f1 -d, --complement Mytest.csv I need to implement this in shell scripting, Whenever i am using the above command alone in command line it is working fine. I have 5 files in my directory and... (3 Replies)
Discussion started by: Samah
3 Replies

3. Shell Programming and Scripting

Insert a new column with sequence number (Delimiter as comma)

Hi All, I have a file which has data like a,b c,d e,f g,h And I need to insert a new column at the begining with sequence no( 1 to n) 1,a,b 2,c,d 3,e,f 4,g,h Please let me know how to acheive this in unix (3 Replies)
Discussion started by: weknowd
3 Replies

4. Shell Programming and Scripting

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 1|2|3|4|5|6|7|8|9|10|11 New file ... (11 Replies)
Discussion started by: Hypesslearner
11 Replies

5. Shell Programming and Scripting

Replacing a column in a pipe delimited file

Hi, I have a pipe delimited file as below and I need to replace the 2nd column of each line with null values. 1|10/15/2011|fname1|lname1 2|10/15/2012|fname2|lname2 3|10/15/2013|fname3|lname3 Output file: 1||fname1|lname1 2||fname2|lname2 3||fname3|lname3 I tried this ... (2 Replies)
Discussion started by: member2014
2 Replies

6. Shell Programming and Scripting

Pivoting csv field from pipe delimited file

Hello All, Thanks for taking time to read through the thread and for providing any possible solution. I am trying to pivot a comma separated field in a pipe delimited file. Data looks something like this: Field1|Field2 123|345,567,789 234|563,560 345|975,098,985,397,984 456|736 Desired... (8 Replies)
Discussion started by: svks1985
8 Replies

7. Homework & Coursework Questions

how to show particular column from pipe delimited file

hi, I have pipe delimited flat file as below 1|ab|4.5|9| 2|ac|3|12| 3|ac|4.5|8| i want to show (display) only 3rd field between pipes. please help (1 Reply)
Discussion started by: vai15517
1 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

Convert CSV file (with double quoted strings) to pipe delimited file

Hi, could some help me convert CSV file (with double quoted strings) to pipe delimited file: here you go with the same data: 1,Friends,"$3.99 per 1,000 listings",8158here " 1,000 listings " should be a single field. Thanks, Ram (8 Replies)
Discussion started by: Ram.Math
8 Replies

10. UNIX for Dummies Questions & Answers

Need help with shell script for chekking a column in txt file - pipe delimited

Hi: I have a text file date(pipe delimited) which is loaded in to the DB using sql loader(&CTL files) after some initial validation by the shell script. Now i have a situation where the shell script needs to check a column in the text file and if it is NULL then it needs send this record/row... (12 Replies)
Discussion started by: ravi0435
12 Replies

Featured Tech Videos