Eliminate comma on Field


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Eliminate comma on Field
# 1  
Old 03-16-2016
Eliminate comma on Field

Hi All,

Seeking for your assistance on my problem below:

What i did was extract the records from database and change the extension name to .csv. My problem is there is a records contains comma between records if i opened it in excel it will generate the comma as new field(please see below example)

Code:
file1.csv
a,JunJun,Binay,b,c - where in JunJun,Binay is consider only as 1 field. because of the comma delimeter it will display it as 2 fields which is wrong.

wrong output:
a,JunJun,Binay,b,c

Expected Output in csv file
HeaderA HeaderB HeaderC HeaderD
a,JunJun,Binay,b,c
where in
Field 1 = a
Field 2 = JunJun,Binay
Field 3 = b
Field 4 = c

I used
Code:
 awk -F "," '{print $1,$2,$3,$4}' file1.csv

but it will show it as seperate field

Please advise,

Thanks,
# 2  
Old 03-17-2016
If , is always between fields 2 and 3 and 4 fields per line, try:
Code:
awk -F, '{$2=$2 "," $3; $3=$4; print $1,$2,$3}' OFS=, file1.csv


Last edited by rdrtx1; 03-17-2016 at 12:25 AM..
# 3  
Old 03-17-2016
Thanks Sir rdrtx1 will check on it and let you know the result.

Br,

---------- Post updated at 11:27 AM ---------- Previous update was at 11:20 AM ----------

Hi Sir,

Upon checking on JunJun,Binay it is considered as 2 field in excel. i want it to consider as 1 field only.

Please advise,
# 4  
Old 03-17-2016
try:
Code:
awk -F, '{$2=$2 "," $3; $3=$4; print $1,"\"" $2 "\"", $3}' OFS=, file1.csv

or
Code:
awk -F, '{$2="\"" $2 "," $3 "\""; $3=$4; print $1, $2, $3}' OFS=, file1.csv

This User Gave Thanks to rdrtx1 For This Post:
# 5  
Old 03-17-2016
okay will try. will let you know the result. thx

---------- Post updated at 11:52 AM ---------- Previous update was at 11:45 AM ----------

upon testing whenever i tried to print another field it will copy the last field. please see below:

Code:
awk -F, '{$2="\"" $2 "," $3 "\""; $3=$4; print $1, $2, $3, $4}' OFS=, file.csv

output:
a,"JunJun,Binay",b,b

# 6  
Old 03-17-2016
Hi Znesotomayor,
you can also save your excel as pipe delimited.Keeping JunJun,Binay as it is.
follow the below steps.
  • press window tab
  • type regional and language
  • go to additional settings
  • in the list separator put |
  • apply and ok
This User Gave Thanks to looney For This Post:
# 7  
Old 03-17-2016
how about $3 and $4 has the same pattern in $2? how to code it using awk?

Thanks,
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to parse comma separated field and removing comma in between number and double quotes

Hi Experts, Please support I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6. ... (3 Replies)
Discussion started by: as7951
3 Replies

2. 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

3. Shell Programming and Scripting

How can i comma-delimited last field in line?

Awk gurus, Greatly appreciate for any kind of assistance from the expert community Input line: abc,11.22.33.44,xyz,7-8-9-10 pqr,111.222.333.444,wxy,1-2-3 def,22.33.44.55,stu,7-8 used the gsub function below but it changes all of the "-" delimiter: awk 'gsub("-",",")' Desired... (4 Replies)
Discussion started by: ux4me
4 Replies

4. Shell Programming and Scripting

Comma separated to rows based on field

Hi to all, I have a file like: chr1 a1 a2 a3 a4 a5 a6,a7,a8,a9 chr1 b1 b2 b3 b4 b5 b6,b7 chr2 c1 c2 c3 c4 c5 c6,c7,c8,c9,c10 ... I would like an output like this: chr1 a6 chr1 a7 chr1 a8 chr1 a9 chr1 b6 chr1 b7 chr2 c6 chr2 c7 chr2 c8 (6 Replies)
Discussion started by: aec
6 Replies

5. Shell Programming and Scripting

Use sed to add comma to end of first field

Example data Gi1/10 Gi1/12 xl32lytscb07 3/11 to nyc 3/12 41764 ecomm 3/13 hxcsxsa 2/1 3/14 ziim570-rsvd 3/15 xl3NDSADM Po1 VPC trunk to xl3-i Po2 ***DO NOT ENABLE** Po13 *** VPC link to Po101 Po102 xl3-2lyg1accsgh-fe... (7 Replies)
Discussion started by: sumguy
7 Replies

6. Shell Programming and Scripting

Extract a nth field from a comma delimited file

Hi, In my file (which is "," delimited and text qualifier is "), I have to extract a particualr field. file1: 1,"aa,b",4 expected is the 2nd field: aa,b I tried the basic cut -d "," -f 2 file 1, this gave me aa alone instead aa,b. A small hint ot help on this will be very... (5 Replies)
Discussion started by: machomaddy
5 Replies

7. Shell Programming and Scripting

awk, comma as field separator and text inside double quotes as a field.

Hi, all I need to get fields in a line that are separated by commas, some of the fields are enclosed with double quotes, and they are supposed to be treated as a single field even if there are commas inside the quotes. sample input: for this line, 5 fields are supposed to be extracted, they... (8 Replies)
Discussion started by: kevintse
8 Replies

8. Shell Programming and Scripting

How to format file into comma separated field

Guys, Need you help, i have a a file content that look like this. Nokia 3330 <spaces><spaces><more spaces>+76451883874 Nokia 3610 +87467361615 so on and so forth, - there are so many spaces in between. - e.g.... (5 Replies)
Discussion started by: shtobias
5 Replies

9. Shell Programming and Scripting

Replace comma by space for specified field in record

Hi, i want to replace comma by space for specified field in record, i mean i want to replace the commas in the 4th field by space. and rest all is same throught the record. the record is 16458,99,001,"RIMOUSKI, QC",418,"N",7,EST,EDT,902 16458,99,002,"CHANDLER,... (5 Replies)
Discussion started by: raghavendra.cse
5 Replies

10. Shell Programming and Scripting

Its PERL + Comma separated seventh field

Hi Friends, I'm working on a perl script, which seems to be simpler. But I'm very new to PERL scripting. I have a comma separated data file, from which I need to extract only the seventh field data out of available twenty fields to an array using perl. Any help would be much appreciated. ... (17 Replies)
Discussion started by: ganapati
17 Replies
Login or Register to Ask a Question