Adding columns in csv


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Adding columns in csv
# 1  
Old 06-27-2008
Adding columns in csv

I have the following data in FILE1.CSV:

code: Amount1: Amount2:
xxxxx ,, 200 ,,400
yyxxa ,,200
bbcgu ,,2500 ,,300

i want to be able to produce the following FILE2.CSV:

code: Amount
xxxxx ,, 600
yyxxa ,,200
bbcgu ,,2800

Just want to now how to add the columns in unix and if there is no amount2 then do nothing

thanks for you assistance
# 2  
Old 06-27-2008
Code:
awk -F",," '
   $0 !~ /Amount/ {print $1,",,"($2+$3)}
   /Amount/ {print "code: Amount"}
' infile

or written on one line:
Code:
awk -F",," '$0 !~ /Amount/ {print $1,",,"($2+$3)}; /Amount/ {print "code: Amount"}' infile

# 3  
Old 06-27-2008
OK that does the trick
could you just help explain the code for me as to what each section is doing so that i may learn from this process rather thangettingthe answer.

Also one problem i am finding is that if the original file had a figure of
,,00003864096.93 it is being displayed as
,,3.8641e+06.

It is imperative that the ammount remans the same and no rounding is done, could you assist.
# 4  
Old 06-27-2008
I didn't check what it does with such long values.

For the code:

Setting awk's input field separator to something useful. Those double commas suited well:
Code:
-F",,"

Code:
   $0 !~ /Amount/ {print $1,",,"($2+$3)}

$0 -> the whole line
!~ -> is not like
/Amount/ -> is just a pattern, a string in that $0
The curly brackets start the action when "the line has nothing like "Amount" in it
print $1 -> prints the 1st field, which is defined by our separator we have set in the beginning
The comma separating $1 and ",," is putting in the OFS, output field separator which is a blank per default
",," -> simply printing two commas
($2+$3) -> adding the values of field 2 and field 3 (don't forget, the ",," counts as field separator and is left out
Thats for that action so far, so we check the current line with the next pattern/action pair

Code:
/Amount/ {print "code: Amount"}

/Amount/ {print "code: Amount"} -> if pattern "Amount" is somewhere in the line, just print out the string "code: Amount"

' infile -> hands over awk the file parse, so no "cat infile| awk ..." is needed, which is useless use of cat.
# 5  
Old 06-27-2008
Thanks again, is there anyomne outhere who can assist with the rounding issue?
# 6  
Old 06-27-2008
Just checked, you can write it like following:

Code:
....int($2+$3)....

or any more suitable type.

Or check this:

The GNU Awk User's Guide
# 7  
Old 06-27-2008
thanks you are a star
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Adding columns from 2 files with variable number of columns

I have two files, file1 and file2 who have identical number of rows and columns. However, the script is supposed to be used for for different files and I cannot know the format in advance. Also, the number of columns changes within the file, some rows have more and some less columns (they are... (13 Replies)
Discussion started by: maya3
13 Replies

2. UNIX for Advanced & Expert Users

Replace columns in .csv using other .csv columns

Hi, I have 2 csv files with 15 000 lines, which looks like this: Daily.csv "CODE","BRAND","DESIGNER","SIZE","TYPE","GENDER","SET","DESCRIPTION","IMAGE","COST","WEIGHT","MSRP","UPC" "M-1001","212","Caroline Her","1.7 oz","EDT... (4 Replies)
Discussion started by: olivieraz
4 Replies

3. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

4. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

5. Shell Programming and Scripting

Adding columns with values dependent on existing columns

Hello I have a file as below chr1 start ref alt code1 code2 chr1 18884 C CAAAA 2 0 chr1 135419 TATACA T 2 0 chr1 332045 T TTG 0 2 chr1 453838 T TAC 2 0 chr1 567652 T TG 1 0 chr1 602541 ... (2 Replies)
Discussion started by: plumb_r
2 Replies

6. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

8. UNIX for Advanced & Expert Users

Unix Bash: substitute columns in .csv using other .csv columns

Hi All, I have two .csv's input.csv having values as (7 columns) ABC,A19907103,ABC DEV YUNG,2.17,1000,2157,07/07/2006 XYZ,H00213850,MM TRUP HILL,38.38,580,23308,31/08/2010 output.csv having (25 columns) A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y... (4 Replies)
Discussion started by: abhivyas
4 Replies

9. Shell Programming and Scripting

Help needed: Adding columns in csv file in loop

Hi Everyone: My shell script creates multiple csv files (~30) in for loop. I want to compile (or merge) 3rd column from each (all) of these files to another file (in loop). Please help. Thanks. (3 Replies)
Discussion started by: smap007
3 Replies

10. Shell Programming and Scripting

Perl: adding columns in CSV file with information in each

Hi Wise UNIX Crew, I want to add 3 different columns to the file in which: 1. The first new column pulls in today's date and time 2. Second column one has a '0' 3. Third column has the word 'ANY' going down the column If my file content is as follows: "7","a","abc",123"... (1 Reply)
Discussion started by: dolo21taf
1 Replies
Login or Register to Ask a Question

Featured Tech Videos