Replacing Comma delimiter coming inside the data.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replacing Comma delimiter coming inside the data.
# 1  
Old 05-11-2011
Replacing Comma delimiter coming inside the data.

Hello,

I am having flat file (Comma Delimiter) and the data in the file is as given below.

EMPNO, ENAME, DESIGNATION, SALARY
10979, Arun Kumar, Cosultant, 35000
13555, Bidhu Shekar, Senior Consultant, 45000
15000, Kiran, Kumar, Senior, Consultant, 40000

If you notice the 3rd record, there are Comma's presents in the column (Column 2: Kiran, Kumar Column 3: Senior, Consultant). Please help me in fixing this problem?
Thanks in Advance!!
# 2  
Old 05-11-2011
what exactly needs to be done
# 3  
Old 05-11-2011
Replacing Comma delimiter coming inside the data.

You can see that it's a comma delimiter. It means each and every record is identified by a comma.
But if you notice the last record, The 2nd column is separated by 2 commas, and also 3rd column is separated by 2 commas which means it contains 6 columns. But actually we are having only 4 columns.
How can we supress the commas that are present inside the data of a column?
# 4  
Old 05-11-2011
if you just want to suppress comma for this particular text, here's the command that works for me:

cat filename | sed 's/Kiran,/Kiran/g' | sed 's/Senior,/Senior/g'
This User Gave Thanks to aman23 For This Post:
# 5  
Old 05-11-2011
Try:
Code:
perl -F, -ape 's/(\d, \w+),/\1/ && s/, (\w+, \d+$)/ \1/ if $#F==5' file

This User Gave Thanks to bartus11 For This Post:
# 6  
Old 05-11-2011
Thanks for the reply bartus11
Can we do the samething with SED or AWK command? If yes can you please give me the answer?
# 7  
Old 05-11-2011
Code:
awk -F, 'NF==6{$0=gensub ("([0-9]+, [[:alpha:]]+),","\\1",1);$0=gensub (", ([[:alpha:]]+, [0-9]+$)"," \\1",1)}1' file

This User Gave Thanks to bartus11 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How can awk ignore the field delimiter like comma inside a field?

We have a csv file as mentioned below and the requirement is to change the date format in file as mentioned below. Current file (file.csv) ---------------------- empname,date_of_join,dept,date_of_resignation ram,08/09/2015,sales,21/06/2016 "akash,sahu",08/10/2015,IT,21/07/2016 ... (6 Replies)
Discussion started by: gopal.biswal
6 Replies

2. Shell Programming and Scripting

awk facing delimiter inside data

Inpu file is as below: CMEOPT1_dump.1:1002 ZN:VTJ3J3C131 CMEOPT1_dump.1:1002 ZN:VTM4M4P123%5 CMEOPT1_dump.1:1002 ZN:VTM3M3P132%5 CMEOPT1_dump.2:1002 OZNG4 CMEOPT2_dump.3:1002 ZB:VTH4H4C132 CMEOPT2_dump.4:1002 ZN:VTK4K4P123 CMEOPT2_dump.5:1002 ZN:BOZ2Z2Z2P131%5 CMEOPT2_dump.5:1002 OZNG4 ... (10 Replies)
Discussion started by: zaq1xsw2
10 Replies

3. Shell Programming and Scripting

Replace comma delimiter by newline

The input file is as below AR,age,marks,roll,section,evin,25,80,456,A,atch,23,56,789,B,eena,24 ,78H245,C,Ps,ot,ecessary,hat,ame comes first then age and rest AR AZ,kevin,25,80,456,A,Satch,23,56,789,Satch,23,56,789,B,Meena,24,78,H245,C,AZ ................ ................ I am writting... (8 Replies)
Discussion started by: millan
8 Replies

4. Shell Programming and Scripting

Change the delimiter from Comma to Pipeline

Hello All, I need to convert a csv file to pipeline delimiter file in UNIX. The data in file itself contains comma with double qouted qualifier apart from the comma separator. Let me know how to do it. Appreciate any help if awk can be used to do it. Mentioned below is the sample record of... (14 Replies)
Discussion started by: Arun Mishra
14 Replies

5. Shell Programming and Scripting

Need Help - comma inside double quote in comma separated csv,

Hello there, I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process. Input in the csv file is , 1,234,"abc,12,gh","GH234TY",34 I need output like below,... (8 Replies)
Discussion started by: Uttam Maji
8 Replies

6. Shell Programming and Scripting

nawk won't accept comma as delimiter

I have a text file delimited by commas, with three fields (no " marks). I want to use awk to create a fourth field which is equal to the line number + field 1 + .txt I've searched this forum and found the following nawk -v OFS=';' '{print $0, FNR}' myFile Which I've amended to change the... (2 Replies)
Discussion started by: b.hamilton
2 Replies

7. UNIX for Dummies Questions & Answers

Making a Tab delimiter file to Comma

How can i make a tab delimiter file to a comma delimiter??? (13 Replies)
Discussion started by: saggiboy10
13 Replies

8. Shell Programming and Scripting

Exporting data into Comma Delimiter.

Hi, Requirement: Exporting data from Oracle to UNIX into "Comma" delimiter. Help Needed: I was able to connect to Oracle and import the data. But please let me know while importing the data I need to make it into Comma delimiter flat file. For Example: Source Data - 100 ABC TLead... (6 Replies)
Discussion started by: arunvasu2
6 Replies

9. Shell Programming and Scripting

Pull Data After Comma if 2 word before comma

Hi, I am trying to truncate word after comma in a file ONLY if there are already 2 words BEFORE comma. If there is one word or 3 or more words BEFORE comma, then I have to leave the data AS IS. See below for example. Input File : John Smith, Manager Smith, John Frank J F K... (2 Replies)
Discussion started by: msalam65
2 Replies

10. Shell Programming and Scripting

comma delimiter and space

I have a csv file and there is a problem which I need to resolve. Column1,Column2,Colum3,Column4 ,x,y,z ,d,c,v t,l,m,n ,h,s,k ,k,,y z,j, ,p Now if you see column1 for row 1 and row 4 though they are null there is a space but in case of row2 and row 5 there is no space. I want row... (3 Replies)
Discussion started by: RubinPat
3 Replies
Login or Register to Ask a Question