Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

csv file manipulation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting csv file manipulation
# 1  
Old 09-08-2010
csv file manipulation
Hi,

I generated a csv file.But i have to manipulate it to make it user friendly. This is the structure of the input csv file.
Code:
partyfailurereasonnumbercvfrejected12cvfapproved45cvfonhold23mvcunknown98mvconcall17pucapproved78puchold34actunknown19

The first line is the header.

The outpout should be:-
Code:
partyfailurereasonnumberrejected12cvfapproved45onhold23unknown98mvconcall17approved78puchold34actunknown19

Explanation:- In the first row, first column i.e A2, there should be cvf in it. In B2 there should be rejected, B3 should be approved and B4 should be onhold.C2 should be 12, C3 should be 45 and C4 should be 23.

Similarly it should be for others also..

-Amit

---------- Post updated at 02:53 AM ---------- Previous update was at 02:47 AM ----------

Sorry, the input and output data is looking weird. Once more i am posting it--
Input data:-
Code:
party	failurereason	number
cvf	rejected	12
cvf	approved	45
cvf	onhold	23
mvc	unknown	98
mvc	oncall	17
puc	approved	78
puc	hold	34
act	unknown	19

output
------
Code:
party	failurereason	number
	rejected	12
cvf	approved	45
	onhold	23
	unknown	98
mvc	oncall	17
	approved	78
puc	hold	34
act	unknown	19

Explanation:- In the first row, first column i.e A2, there should be cvf in it. In B2 there should be rejected, B3 should be approved and B4 should be onhold.C2 should be 12, C3 should be 45 and C4 should be 23.

Similarly it should be for others also..

-Amit

Moderator's Comments:
Mod Comment Use code tags please, you got a pm how to use them. Thanks.
# 2  
Old 09-08-2010
Try this,

Code:
awk -F"," '{if (NR>1 && a==$1) {print FS$2FS$3;a=$1}  else {print $0;a=$1}}' input.csv > output.csv

# 3  
Old 09-08-2010
Thanks Pravin. It worked.
But the output is getting generated as expected.
The present output that is getting generated now is:-
Code:
party|	failurereason|	number
-----|---------------|---------------
 cvf  |	rejected	    |       12
-----|---------------|--------------
      |	approved	    |        45
-----|---------------|---------------
       |	onhold	    |        23
-----|---------------|-------------------

This structure is also for mvc and puc also.
But the output file should be of this nature:-
Code:
party|	failurereason|	number
-----|---------------|---------------
       |	rejected	    |       12
       |---------------|--------------
cvf   |	approved	    |        45
       |---------------|---------------
       |	onhold	    |        23
-----|---------------|-------------------

Regards,
# 4  
Old 09-08-2010
Post your input CSV file and expected output CSV file
# 5  
Old 09-08-2010
Code:
awk '{print ((a==$1)?x:$1),$2((length($2)<8)?OFS:x),$3}{a=$1}' OFS="\t" file
party   failurereason   number
cvf     rejected        12
        approved        45
        onhold          23
mvc     unknown         98
        oncall          17
puc     approved        78
        hold            34
act     unknown         19

# 6  
Old 09-08-2010
Code:
 
Input csv
 
Party
failurereason
number
Cvf
Rejected
12
Cvf
Approved
45
Cvf
Onhold
23
Mvc
Unknown
98
Mvc 
Oncall
17
Puc
Approved
78
Puc 
Onhold
34
Act
Unknown
19

 
The solution that was provided gives this result:-  
Party
failurereason
number
Cvf
Rejected
12
 
Approved
45
 
Onhold
23
Mvc
Unknown
98
 
Oncall
17
Puc
Approved
78
 
Onhold
34
Act
Unknown
19

 
Expected output:-
 
Party
Failurereason
Number
Cvf
Rejected
12
Approved
45
Onhold
23
Mvc
Unknown
98
Oncall
17
Puc
Approved
78
Onhold
34
Act
Unknown
19

# 7  
Old 09-12-2010
Hi,

Code:
awk -F"," '{if (NR>1 && a==$1) {print FS$2FS$3;a=$1}  else {print $0;a=$1}}' input.csv > output.csv

This is working fine.
But can you please explain it.

And also the above code is generating unique items only in the second column.

Thanks
Last edited by Scott; 09-12-2010 at 03:46 PM.. Reason: Code tags, please...
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

2. Shell Programming and Scripting

Save output of updated csv file as csv file itself

Hi, all I want to sort a csv file based on timestamp from oldest to newest and save the output as csv file itself. Here is an example of my csv file. test.csv SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21 /home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28... (10 Replies)
Discussion started by: refrain
10 Replies

3. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

4. Shell Programming and Scripting

Pax1.csv file manipulation

Hallo Team and happy Friday, I have a .csv file attached called MHG_Only_Billing_only_great_201408_SummaryDetails.csv The file is as below: $ cat MHG_Only_Billing_only_great_201408_SummaryDetails.csv... (1 Reply)
Discussion started by: kekanap
1 Replies

5. Shell Programming and Scripting

.csv file manipulation

Hall0 Team, I need you help. I have a .csv file called Pax.csv. It has 13 fields The fields that i am interested in is field 7, field 10(CallDuration),field 12 and field 13(TelkomAsif) Field 12(Cost) is affected by what is on field 7(CallType) if field7= VoIS Community Sharecall then Cost... (14 Replies)
Discussion started by: kekanap
14 Replies

6. Shell Programming and Scripting

Data Manipulation on a .csv file

Hallo Friends, I need you help. My file has 5000 or so lines and currently looks like below(sample). Service Type,Origin,Destination,Rate Per Minute,Minimum Charge,Time Based Rate,Time Based From Day,Time Based To Day,Time Based From Time,Time Based To Time,Destination Prefix List,, VoIS... (3 Replies)
Discussion started by: kekanap
3 Replies

7. Shell Programming and Scripting

CSV data format manipulation

Hi There I need a script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. I am using an application that will only take data in a particular format and need something that will convert without manual intervention. The... (4 Replies)
Discussion started by: rbggbr16
4 Replies

8. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

9. Shell Programming and Scripting

csv manipulation

Hi, I want to delete the null values that are present in all the columns except the last column $ cat x.csv baseball,NULL,8798765,Most played,0,5,12367,NULL,NULL,98,67,Reason is not sufficient baseball,NULL,8928192,Most played,0,4,76893,NULL,RAW,54,78,NULL baseball,NULL,5678945,Most... (6 Replies)
Discussion started by: scripter12
6 Replies
Login or Register to Ask a Question