Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Want to remove the last characters from each row of csv using shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Want to remove the last characters from each row of csv using shell script
# 1  
Old 12-02-2011
Want to remove the last characters from each row of csv using shell script
Hi,

I've a csv file seperated by '|' from which I'm trying to remove the excess '|' characters more than the existing fields. My CSV looks like as below.
Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041|||
10000002|ACTIVE|Testazar2|Testore2|20042|Street East||

I want the output csv as below, I just want remove the last two '|' characters only which present in the end of the line. My out put should be like this.

Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041|
10000002|ACTIVE|Testazar2|Testore2|20042|Street East

I used the following script but the output csv is weird which is not expected.
my shell script:
Code:
sed -e "s/\(.*\)||/\1/g" new6.csv >> new7.csv;

the output I'm getting is like this:
Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041
|
10000002|ACTIVE|Testazar2|Testore2|20042|Street East

the remaining pipe symbol(|) after deleting last two characters is coming down, which is supposed end in the same line. Can anyone help me out in getting me the proper output and place the last.

Thanks in Advance.
Last edited by Scott; 12-02-2011 at 07:02 AM.. Reason: Code tags
# 2  
Old 12-02-2011
Welcome to the forum.
well, its not the CSV file in that case.

Try,

Code:
sed 's/||*$/|/g'

# 3  
Old 12-02-2011
Thank you anchal.

Now I figured out that it is not the 'sed' script that is causing the issue. Actually what happens is that I want to remove some fields from my existing csv file using 'awk'. Let say I just to delete the ITDCLIENTUSERID from my csv file. My initial csv before deleting is like this:

Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041|
10000002|ACTIVE|Testazar2|Testore2|20042|Street East

I tried to delete the ITDCLIENTUSERID field using the following script:
Code:
awk 'BEGIN { FS="|"; OFS="|" } {$5="";$5=$6;$6=""}1' test.csv >> new6;

which gives the output like this:
Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1
||
10000002|ACTIVE|Testazar2|Testore2|Street East
|

Here the ending characters '|' comes in a new line but I want it should continue in the above line instead of coming into a new line.
My expected result should be like this:
Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1||
10000002|ACTIVE|Testazar2|Testore2|Street East|

can you please help me out in getting my proper expected result.

---------- Post updated at 05:46 AM ---------- Previous update was at 05:37 AM ----------

Small correction, My output looks like this after the 'awk' running.

Code:
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1
||
10000002|ACTIVE|Testazar2|Testore2|Street East
|

Last edited by Scott; 12-02-2011 at 07:04 AM.. Reason: Code tags
# 4  
Old 12-02-2011
Code:
# cat input
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041|||
10000002|ACTIVE|Testazar2|Testore2|20042|Street East||

Code:
# perl -nle 's/\|+$/\|/g; print $_' < input
HRLOAD|Service|AddChange|EN
PERSONID|STATUS|LASTNAME|FIRSTNAME|ITDCLIENTUSERID|ADDRESSLINE1
10000001|ACTIVE|Testazar1|Testore1|20041|
10000002|ACTIVE|Testazar2|Testore2|20042|Street East|

# 5  
Old 12-02-2011
What's your OS? It seems okay when I try it on RHEL (so gawk).
# 6  
Old 12-02-2011
Quote:
Here the ending characters '|' comes in a new line but I want it should continue in the above line instead of coming into a new line.
My expected result should be like this:

Are you ftping the file from windows?
I guess there are linefeed issue.
# 7  
Old 12-02-2011
My OS is AIX.
I'm not ftping the file from windows. It is with in the same server i.e. AIX server. Any help?

thanks in advance.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Outputting characters after a given string and reporting the characters in the row below --sed

I have this fastq file: @M04961:22:000000000-B5VGJ:1:1101:9280:7106 1:N:0:86 GGGGGGGGGGGGCATGAAAACATACAAACCGTCTTTCCAGAAATTGTTCCAAGTATCGGCAACAGCTTTATCAATACCATGAAAAATATCAACCACACCA +test-1 GGGGGGGGGGGGGGGGGCCGGGGGFF,EDFFGEDFG,@DGGCGGEGGG7DCGGGF68CGFFFGGGG@CGDGFFDFEFEFF:30CGAFFDFEFF8CAF;;8... (10 Replies)
Discussion started by: Xterra
10 Replies

2. Shell Programming and Scripting

Need a piece of shell scripting to remove column from a csv file

Hi, I need to remove first column from a csv file and i can do this by using below command. cut -f1 -d, --complement Mytest.csv I need to implement this in shell scripting, Whenever i am using the above command alone in command line it is working fine. I have 5 files in my directory and... (3 Replies)
Discussion started by: Samah
3 Replies

3. Shell Programming and Scripting

Shell script that should remove unnecessary commas between double quotes in CSV file

i have data as below 123,"paul phiri",paul@yahoo.com,"po.box 23, BT","Eco Bank,Blantyre,Malawi" i need an output to be 123,"paul phiri",paul@yahoo.com,"po.box 23 BT","Eco Bank Blantyre Malawi" (5 Replies)
Discussion started by: mathias23
5 Replies

4. Shell Programming and Scripting

Remove line breaks in csv file using shell script

Hi All, I've a csv file in which the record is getting break into 1 line or more than one line. I want to combine those splits into one line and remove the unwanted character existing in the record i.e. double quote symbol ("). The line gets break only when the record contains double... (4 Replies)
Discussion started by: rajak.net
4 Replies

5. Shell Programming and Scripting

Remove ^? characters in shell variables on using backspace

Friends, I observed a peculiar problem in shell. if I set a variable using standard input and backspace was used by the user, then the variable get ^? characters embedded in the variable. ### echo "Enter value for X=" read X echo $X expr $X + 1 ### If the variable is echoed, then there... (3 Replies)
Discussion started by: sachinverma
3 Replies

6. UNIX for Dummies Questions & Answers

Shell Script: Traverse Database Table Row by Row

Hello Everyone, My issue is that I want to traverse a database table row by row and do some action on the value retrieved in each row. I have gone through a lot of shell script questions/posts. I could find row by row traversal of a file but not a database table. Please help. Thanks &... (5 Replies)
Discussion started by: ahsan.asghar
5 Replies

7. Shell Programming and Scripting

shell script to remove extra commas from CSV outp file

Name,,,,,,,,,,,,,,,,,,,,Domain,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Contact,Phone,Email,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Location -----------------------,------------------------------------------------,-------,-----,---------------------------------,------------------------------------ ----... (1 Reply)
Discussion started by: sreenath1037
1 Replies

8. Shell Programming and Scripting

remove row if string is same as previous row

I have data like: Blue Apple 6 Red Apple 7 Yellow Apple 8 Green Banana 2 Purple Banana 8 Orange Pear 11 What I want to do is if $2 in a row is the same as $2 in the previous row remove that row. An identical $2 may exist more than one time. So the out file would look like: Blue... (4 Replies)
Discussion started by: dcfargo
4 Replies

9. Shell Programming and Scripting

Blank Space is not appending in each row of CSV File - Shell Script

I am calling SQL script in my UNIX Shell script and trying to create the CSV file and my last column value of each row is 23 blank spaces. In my SQL script,the last column is like below. RPAD(' ',23,' ') -- Padding 23 blank Spaces The CSV file is generated but the sapce(23 spaces) is... (2 Replies)
Discussion started by: praka
2 Replies

10. Shell Programming and Scripting

Script to find and remove characters

Hi. I have many files in a folder, and even more in the subfolders. I need a script that finds and removes certain characters (them being /n in this one) in the files in the folder and it's subfolders. So, could someone write me a script that works in Linux, does this: Searchs for "/n" in... (5 Replies)
Discussion started by: Zerby
5 Replies
Login or Register to Ask a Question