Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Copy columns from one file into another and get sum of column values and row count
Top Forums UNIX for Beginners Questions & Answers Copy columns from one file into another and get sum of column values and row count Post 303020270 by Tahir_M on Monday 16th of July 2018 05:30:16 AM
Old 07-16-2018
Copy columns from one file into another and get sum of column values and row count
I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv

abc.csv-
Code:
UTF-8,,,,,,,,,,,,,,,,,,,,,,,,,

Id,EventDate,EventStatus,EventType,Title,PONumber,Version,ProcurementUnit,OriginatingSystem,OriginatingSystemReferenceID,Supplier,SupplierLocation,PaymentTerms,ShipNoticeDate,ShipmentDate,DeliveryDate,ShippingCarrier,TrackingNumber,ASNNumber,ClosePOStatus,Comments,PurchaseOrder_cus_RequesterPIN,PurchaseOrder_cus_SupplierName,PurchaseOrder_TotalCost,PurchaseOrder_TotalCost_InBaseCurrency,PurchaseOrder_TotalCost_Currency

147366257,2/2/2018 7:11,Ordered,Ordered,Testing PO Closing at Line Level,PO5460,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,LV,3740,3740,USD

147369568,2/2/2018 7:26,Ordered,Ordered,Staples test 1,PO5461,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,Abc,30,30,USD

147372761,2/2/2018 7:40,Ordered,Ordered,Testing PO Closing at Line Level,PO5460,2,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,5610,5610,USD

147373497,2/2/2018 7:43,Ordered,Ordered,Staples test 2,PO5462,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,Arrow,100,100,USD

147374360,2/2/2018 7:47,Ordered,Ordered,Staples test 3,PO5463,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,LV,50,50,USD

147374487,2/2/2018 7:48,Ordered,Ordered,Copy of Testing PO Closing at Line Level,PO5464,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,5610,5610,USD

147375802,2/2/2018 7:54,Ordered,Ordered,Staples test 4,PO5465,1,,,,334741,334741,Net 30,,,,,,,Open,,334741,xvz,200,200,USD

147894306,2/6/2018 8:35,Ordered,Ordered,testing,PO5502,1,,,,474087,474087,Net 30,,,,,,,Open,,474087,Arrow,0,0,USD

147921985,2/6/2018 10:44,Ordered,Ordered,TYest with 101 Supplier - Consulting,PO5503,1,,,,GPB101,GPB101,Net 00,,,,,,,Open,,GPB101,LV,2250,2250,USD

148420762,2/8/2018 11:12,Ordered,Ordered,Testing manual invoice,PO5520,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,2750,2750,USD

148423383,2/8/2018 11:27,Ordered,Ordered,Copy of Copy of Copy of Testing,PO5521,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,2400,2400,USD

148424067,2/8/2018 11:31,Ordered,Ordered,Copy of Copy of Copy of Copy of Testing,PO5522,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,2400,2400,USD

148424763,2/8/2018 11:34,Ordered,Ordered,Copy of Testing,PO5523,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,LV,2400,2400,USD

149545031,2/15/2018 8:25,Ordered,Ordered,WD_Rejection Reason Code test PO,PO5584,1,,,,BIR05,BIR05,Net 00,,,,,,,Open,,BIR05,Abc,1200,1200,USD

149952795,2/19/2018 4:54,Ordered,Ordered,Staples Promo Cipher Test,PO5613,1,,,,303304,303304,Net 30,,,,,,,Open,,303304,xvz,5.25,5.25,USD

151200715,2/26/2018 9:25,Ordered,Ordered,Testing Invoice Variance,PO5678,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,2400,2400,USD

151206109,2/26/2018 9:53,Ordered,Ordered,Testing Invoice Variance - Separate Line,PO5679,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,2400,2400,USD

151764465,2/28/2018 11:14,Ordered,Ordered,Testing invoice approval,PO5692,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,ROBIN HOOD ENTERPRISES,500,500,USD

152184301,3/2/2018 7:05,Ordered,Ordered,Testing partial invoice,PO5713,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,2000,2000,USD

152758385,3/6/2018 10:34,Ordered,Ordered,Test Team Requisitioning,PO5742,1,,,,5002362,5002362,Net 01,,,,,,,Open,,5002362,LV,10,10,USD

153665200,3/12/2018 7:02,Ordered,Ordered,testing blanket,PO5791,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,Abc,12000,12000,USD

153713486,3/12/2018 10:44,Ordered,Ordered,Test AN direct,PO5792,1,,,,GPB101,GPB101,Net 00,,,,,,,Open,,GPB101,xvz,130,130,USD

153714281,3/12/2018 10:48,Ordered,Ordered,test,PO5793,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Arrow,5500,5500,USD

153909455,3/13/2018 8:12,Ordered,Ordered,Testing,PO5802,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

153968315,3/13/2018 13:03,Ordered,Ordered,Testing,PO5803,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

154210009,3/14/2018 15:22,Ordered,Ordered,Team Req Test 3,PO5808,1,,,,408107,408107,Net 45,,,,,,,Open,,408107,HUMANSCALE CORPORATION,20,20,USD

154329083,3/15/2018 5:54,Ordered,Ordered,Testing Tax Approver Flow,PO5817,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,220.8,220.8,USD

154704441,3/17/2018 23:39,Ordered,Ordered,my test order,PO5842,1,,,,318218,318218,Net 30,,,,,,,Open,,318218,LV,338.5,338.5,USD

155058345,3/20/2018 6:57,Ordered,Ordered,testing PO change,PO5863,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Abc,1200,1200,USD

155058845,3/20/2018 6:59,Ordered,Ordered,computer accesories,PO5864,1,,,,333366,333366,Net 30,,,,,,,Open,,333366,xvz,129,129,USD

155058911,3/20/2018 7:00,Ordered,Ordered,Test Team Requisitioning,PO5742,2,,,,5002362,5002362,Net 01,,,,,,,Open,,5002362,Arrow,40,40,USD

155060880,3/20/2018 7:07,Ordered,Ordered,testing,PO5869,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1200,1200,USD

155564616,3/22/2018 10:09,Ordered,Ordered,Testing Line Close,PO5886,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For All Actions,,BIR03,LV,9750,9750,USD

155569233,3/22/2018 10:29,Ordered,Ordered,Copy of Testing Line Close - fourth PO for test,PO5887,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,Abc,9750,9750,USD

155569271,3/22/2018 10:29,Ordered,Ordered,Testing Line Close - third PO for test,PO5888,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,xvz,9750,9750,USD

155569422,3/22/2018 10:30,Ordered,Ordered,Testing Line Close - second PO for test,PO5889,1,,,,BIR03,BIR03,Net 02,,,,,,,Order Closed For Various Actions,,BIR03,Arrow,9750,9750,USD

155572115,3/22/2018 10:43,Ordered,Ordered,Testing Line Close - fifth PO for test,PO5890,1,,,,BIR03,BIR03,Net 02,,,,,,,Open,,BIR03,ROBIN HOOD ENTERPRISES,1250,1250,USD

I've been writing something like this..

Code:
Code :

set -x # turn on debug

 

#!/bin/bash/sh

outputfile="Cloudfile.csv"

curr_date=`date '+%Y%m%d_%H%M%S'`

### Copy Column from a file and paste into another

cut -d, -f24 abc.csv | sed s/\"//g > file1.csv

### Remove first Row ## Remove Trailing Zero(s) and print only non null Values

awk 'NR>2' file1.csv | awk ' { if($1 ~ /\./) sub("\\.*0+$","");print}'| awk -F , '$1 > "0" { print }' > file2.csv

awk -F, ' {rows++; sum+=$1}

        END{print "Date=", $curr_date, "Row_count=", rows, "Sum Amounts=", sum} ' file2.csv > $outputfile

Output:
Code:
Date= 1250 Row_count= 36 Sum Amounts= 99483.6

but I want output to be like this:
expected output:
Code:
DATE                           Sum_Amount     RECORD_COUNT

06/15/2018                      29039.44         

06/20/2018                     314038.03         

07/14/2018                      41424.75         

07/15/2018                      90314.27         

07/22/2018                      66416.22         

07/23/2018                       2013.55         

07/24/2018                       1172.35         

Total:                         544418.61        51

Thanks in Advance!

---------- Post updated at 03:00 PM ---------- Previous update was at 02:46 PM ----------

** The date column has the current date values for all rows returned in sum_amounts.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies

2. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

3. Shell Programming and Scripting

Sum of values coming in a row

Hi, my requirement is to sum values in a row. eg: input is: sum,value1,value2,value3,.....,value N Required Output: sum,<summation of N values> Please help me... (5 Replies)
Discussion started by: MrGopal666
5 Replies

4. Shell Programming and Scripting

Copy values from columns matching in those in second file.

Hi All, I have two sets of files. Set 1: 100 text files with extension .txt with names like 1.txt, 2.txt, 3.txt until 100.txt Set 2: One big file with extension .dat The text files have some records in columns like this: 0.7316431 82628 0.7248189 82577 0.7248182 81369 0.7222999... (1 Reply)
Discussion started by: shoaibjameel123
1 Replies

5. UNIX for Dummies Questions & Answers

Count on grep for more than two values in a row of fixed length file

I want to get count on number of records in a few folders by running grep command for more than two columns in a row of fixed length file. suppose if i have a fixed length file has 5 columns and I want to see the record counts for country =can and province = bc and time stamp <= 12 feb 2013... (14 Replies)
Discussion started by: princetd001
14 Replies

6. Shell Programming and Scripting

Sum specified values (columns) per row

Hello out there, file.txt: comp51820_c1_seq1 42 N 0:0:0:0:0:0 1:0:0:0:0:0 0:0:0:0:0:0 3:0:0:0:0:0 0:0:0:0:0:0 comp51820_c1_seq1 43 N 0:0:0:0:0:0 0:1:0:0:0:0 0:0:0:0:0:0 0:3:0:0:0:0 0:0:0:0:0:0 comp51820_c1_seq1 44 N 0:0:4:0:3:1 0:0:1:9:0:0 10:0:0:0:0:0 0:3:3:2:2:6 2:2:2:5:60:3... (16 Replies)
Discussion started by: pathunkathunk
16 Replies

7. UNIX for Dummies Questions & Answers

Unique values in a row sum the next column in UNIX

Hi would like to ask you guys any advise regarding my problem I have this kind of data file.txt 111111111,20 111111111,50 222222222,70 333333333,40 444444444,10 444444444,20 I need to get this file1.txt 111111111,70 222222222,70 333333333,40 444444444,30 using this code I can... (6 Replies)
Discussion started by: reks
6 Replies

8. Shell Programming and Scripting

Add sum of columns and max as new row

Hi, I am a new bie i need some help with respect to shell onliner; I have data in following format Name FromDate UntilDate Active Changed Touched Test 28-03-2013 28-03-2013 1 0.6667 100 Test2 28-03-2013 03-04-2013 ... (1 Reply)
Discussion started by: gangaraju6
1 Replies

9. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines... (3 Replies)
Discussion started by: Bastami
3 Replies

10. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (12 Replies)
Discussion started by: as7951
12 Replies
All times are GMT -4. The time now is 04:30 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy