Ubuntu – adding a new column as line number in a csv file

bashcommand linecsvfiles

I created a csv file but forgot to add line number as the first column. What is the fastest way to do this (for a large file)?

262145,umbrella,umbrella,carrot,carrot,carrot,carrot,person,person,person,person,person,person,person,person,person,person,person,person,umbrella,carrot,carrot,carrot,carrot,carrot,carrot,carrot,carrot,person,umbrella,carrot,person,carrot
262146,person,skis,backpack
524291,dog,dog,person,frisbee
393223,toothbrush,person
393224,tie,person,person,person,person,person
524297,person,person,person,bear
393227,person,skateboard
131084,motorcycle,person
393230,person,skis
524311,chair,person,remote
393241,frisbee,person,person,person,person,person,person,person,person,person,car,car,person,car,car,person,person,person
524314,car,person,person,teddy bear,car,handbag,cell phone,car,handbag,teddy bear
262171,boat,person,person,person,person,person,person,boat,person
131101,umbrella,person,person,backpack,handbag,person
524320,tie,person,person,suitcase,person

I want something like:

1,262145,umbrella,umbrella,carrot,carrot,carrot,carrot,person,person,person,person,person,person,person,person,person,person,person,person,umbrella,carrot,carrot,carrot,carrot,carrot,carrot,carrot,carrot,person,umbrella,carrot,person,carrot
2,262146,person,skis,backpack

Best Answer

create new file with line numbers and just get rid of old one

awk '{printf "%s,%s\n", NR,$0}' input.txt > new.txt

With only `awk` command:

awk -F, '{getline f1 <"file2" ;print f1,$3,$4}' OFS=, file1

Get a line from file1 and store it into local variable f1, then print the line that stored in f1 and finally print the third($3) and forth($3) fields from file1 which delimited with comma , altogether, and change the OFS(output field separator [space by default]) to comma(,).

The short command would be like this:

paste -d, file2 <(cut -d, -f3- file1)

 A,B,C,D  
 A,B,C,D  
 A,B,C,D  
 A,B,C,D

paste the file2, then cut and paste the third column to the next(-f3-) from file1.

With `awk` and `paste` (option A)

Below command also copies the last two columns (C,D) from file1 at the end of each line in file2:

paste -d',' file2  <(awk -F',' '{print $(NF-1)","$NF}' file1)

Above command paste the file2 content then print a comma delimiter(-d',') then paste the two last field(NF is the index of last field and $NF is the string which its index is NF. So $(NF-1) is the second field before last field ) from file1 when those index redefines or splits with comma spectator(-F',').

With `awk` and `paste` (option B)

This command also is the same as above($3 and $4 points to third and forth field of each line from file1 ):

paste -d',' file2  <(awk -F',' '{print $3","$4}' file1)

Or another solution with `cut` command:

paste -d, <(cut -d, -f1 file1) <(cut -d, -f2 file2) <(cut -d, -f3- file1)

cut command in above command first cut the first field(-f1 which indexed with comma delimiter(-d.)) from file1(cut -d, -f1 file1), then cut and paste the second field of file2(cut -d, -f2 file2) and finally cut and paste the third column(-f3) to the nexts(-) from file1(cut -d, -f3- file1) again.

This command also returns the same result:

paste -d, <(awk -F',' '{print $1}' file1) <(awk -F',' '{print $2}' file2) <(awk -F',' '{print $3","$4}' file1)

paste the second field from file1(awk -F',' '{print $1}' file1) then print a comma(-d,), then paste the second column from file2(awk -F',' '{print $2}' file2), finally paste the second and last column of file1(awk -F',' '{print $3","$4}' file1) again.

Ubuntu – column: line too long error

According to this bug report

In the utility source code: column.c It defines MAXLINELEN as 2048 + 1

If your input contains a line which its length is > 2048 you'll get this error.

In order to solve it, you should make sure that the length of the lines in your input is < 2048, or replace the 'column' utility with another one.

Best Answer

Related Solutions

Ubuntu – Add column from one .csv to another .csv file

With only awk command:

The short command would be like this:

With awk and paste (option A)

With awk and paste (option B)

Or another solution with cut command: