Add an incrementing identity column to a file

UNIX for Advanced & Expert Users
1

Two questions:
1) Is there a way to create a new column (i.e. ID) to an existing file containing data and the new column ID populate as an auto incrementing number?
for example:
(Current file has headers and is PIPE delimited.)

data looks like this

"COL1"|"COL2"|"COL3"|"COL4"|"COL5"
"A1"|"B1"|"C1"|"D1"|"E1"
"A2"|"B2"|"C2"|"D2"|"E2"
"A3"|"B3"|"C3"|"D3"|"E3"

would like for it to be like

"ID"|"COL1"|"COL2"|"COL3"|"COL4"|"COL5"
"1"|"A1"|"B1"|"C1"|"D1"|"E1"
"2"|"A2"|"B2"|"C2"|"D2"|"E2"
"3"|"A3"|"B3"|"C3"|"D3"|"E3"

2) Is there a way to replace an existing column with a New column for an existing file containing data. The new column would be "ID" and populate as an auto incrementing number?
for example:
(Current file has headers and is PIPE delimited.)
data looks like this

"COL1"|"COL2"|"COL3"|"COL4"|"COL5"
"A1"|"B1"|"C1"|"D1"|"E1"
"A2"|"B2"|"C2"|"D2"|"E2"
"A3"|"B3"|"C3"|"D3"|"E3"

would like for it to be like

"COL1"|"COL2"|"ID"|"COL4"|"COL5" 
"A1"|"B1"|"1"|"D1"|"E1"
"A2"|"B2"|"2"|"D2"|"E2"
"A3"|"B3"|"3"|"D3"|"E3"

Hoping someone can help provide a solution for both scenarios. I have tried so many methods (i.e. awk , sed , ect ) with no luck.

2

The awk utility is perfectly capable of doing things like this. Please show us the awk code that you used to try to do this (in CODE tags). Maybe we can help you fix it so it does what you want.

The sed utility doesn't know how to perform arithmetic operations; so it is not usually a good candidate for projects like this.

I am not familiar with the ect utility. Please show us what you tried with that utility too (in CODE tags). Maybe someone else reading this thread will be familiar with that utility and will be able to help you fix that code.

3

Here is an awk code that works with your sample file:

awk 'BEGIN {FS=OFS="|"; Q="\""} {print Q (NR==1 ? "ID" : ++n) Q, $0}' file

And this one modifies column #3:

awk 'BEGIN {FS=OFS="|"; Q="\""} {$3=Q (NR==1 ? "ID" : ++n) Q; print}' file
4

Please help me out - what is the "i" for?

awk 'BEGIN {FS=OFS="|" i ; Q="\""} {$3=Q (NR==1 ? "ID" : ++n) Q; print}' file
5

Thanks for the note. A typo. I have corrected my post.