Eliminate comma on Field

Shell Programming and Scripting
1

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)

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

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

but it will show it as seperate field

Please advise,

Thanks,

2

If , is always between fields 2 and 3 and 4 fields per line, try:

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

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

try:

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

or

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

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:

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

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

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
1 Like
7

how about $3 and $4 has the same pattern in $2? how to code it using awk?

Thanks,