Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to delete the commas in a .CSV file that are enclosed in a string with double quotes?
Top Forums Shell Programming and Scripting How to delete the commas in a .CSV file that are enclosed in a string with double quotes? Post 302887691 by dhruuv369 on Monday 10th of February 2014 10:03:12 AM
Old 02-10-2014
How to delete the commas in a .CSV file that are enclosed in a string with double quotes?
Okay, I would like to delete all the commas in a .CSV file (TEST.CSV) or at least substitute them with empty space, that are enclosed in double quote.

Please see the sample file as below:
Code:
column 1,column 2,column 3,column 4,column 5,column 6,column 7,column 8,column 9,column 10
12310,42324564756,"a simple string with a , comma","string with or, without commas",string 1,USD,12,70%,08/01/2013,
23455,12312255564,"string, with, multiple, commas","string with or, without commas",string 2,USD,433,70%,07/15/2013,
23525,74535243123,"string , with commas, and - hypens and: semicolans","string with or, without commas",string 1,CAND,744,70%,05/06/2013,
46476,15467534544,"lengthy string, with commas, multiple: colans","string with or, without commas",string 2,CAND,388,70%,09/21/2013,

I was trying the below code but, it is deleting all the commas in the file:
Code:
awk -F'"' '{gsub (/,/,"\001",$0)}1' OFS='"' TEST.CSV

Using either SED or AWK I am expecting the output as below:
Code:
column 1,column 2,column 3,column 4,column 5,column 6,column 7,column 8,column 9,column 10
12310,42324564756,"a simple string with a  comma","string with or without commas",string 1,USD,12,70%,08/01/2013,
23455,12312255564,"string with multiple commas","string with or without commas",string 2,USD,433,70%,07/15/2013,
23525,74535243123,"string with commas and - hypens and: semicolans","string with or without commas",string 1,CAND,744,70%,05/06/2013,
46476,15467534544,"lengthy string with commas multiple: colans","string with or without commas",string 2,CAND,388,70%,09/21/2013,

 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

How to remove a character which is enclosed in Double quotes

I want to remove the comma which is present within the double quoted string. All other commas which is present outside double quotes should be present. Input : a,b,"cc,dd,ee",f,ii,"jj,kk",mmm output : a,b,"ccddee",f,ii,"jjkk",mmm (3 Replies)
Discussion started by: mohan_tuty
3 Replies

2. Shell Programming and Scripting

how to find the count of commas in a string excluding the ones in double quotes

Hi, my requirement is to find the count of commas in a string excluding the ones in double quotes. For example: If the input string is abc,xyz.com,lmhgdf,"abc, 401 street","tty,stt",45,23,45 The output should be 7 (7 Replies)
Discussion started by: amitshete
7 Replies

3. Shell Programming and Scripting

HELP with AWK or SED. Need to replace the commas between double quotes in CSV file

Hello experts, I need to validate a csv file which contains data like this: Sample.csv "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 I just need to check if all the records contain exactly the number of... (5 Replies)
Discussion started by: shell_boy23
5 Replies

4. Shell Programming and Scripting

How to delete a column/columns of a CSV file which has cell values with a string enclosed in " , "?

How can I delete a column from a CSV file which has comma separated value with a string enclosed in double quotes and a comma in between? I have a file 44.csv with 4 lines including the header like the below format: column1, column2, column3, column 4, column5, column6 12,455,"string with... (6 Replies)
Discussion started by: dhruuv369
6 Replies

5. Shell Programming and Scripting

How to delete a columns of a CSV file which has cell values with a string enclosed in " , "?

Hi How can I delete a columns from a CSV file which has comma separated value with a string enclosed in double quotes or square bracket and a comma in between? I have a csv file with below format. Template,Target Server,Target Component,Rule Group,Rule,Rule Reference Number,Rule... (7 Replies)
Discussion started by: Litu19
7 Replies

6. Shell Programming and Scripting

How to match fields surrounded by double quotes with commas?

Hello to all, I'm trying to match only fields surrounded by double quotes that have one or more commas inside. The text is like this "one, t2o",334,"tst,982-0",881,"kmk 9-l","kkd, 115-001, jj-3",5 The matches should be "one, t2o" "tst,982-0" "kkd, 115-001, jj-3" I'm trying with... (11 Replies)
Discussion started by: Ophiuchus
11 Replies

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

8. Shell Programming and Scripting

Replace Double quotes within double quotes in a column with space while loading a CSV file

Hi All, I'm unable to load the data using sql loader where there are double quotes within the double quotes As these are optionally enclosed by double quotes. Sample Data : "221100",138.00,"D","0019/1477","44012075","49938","49938/15043000","Television - 22" Refurbished - Airwave","Supply... (6 Replies)
Discussion started by: mlavanya
6 Replies

9. Shell Programming and Scripting

Remove pipe(|) symbol ina file, except the ones which are enclosed in double quotes

I have file with are delimited by pipe(|) symbol, I wanted those to be removed except the ones which are enclosed in double quotes. If your quote file is: |Life is |Beautiful"|"Indeed life |is beautiful too|"|"But unix is fun| is not"|" It should return: Life is Beautiful"|"Indeed life is... (1 Reply)
Discussion started by: Sathyapts
1 Replies

10. Shell Programming and Scripting

Remove pipe(|) symbol in except the ones which are enclosed in double quotes

I have file with are delimited by pipe(|) symbol, I wanted those to be removed except the ones which are enclosed in double quotes. If your quote file is: |Life is |Beautiful"|"Indeed life |is beautiful too|"|"But unix is fun| is not"|" It should return: Life is Beautiful"|"Indeed life is... (9 Replies)
Discussion started by: Sathyapts
9 Replies

LEARN ABOUT MOJAVE

colrm

COLRM(1)						    BSD General Commands Manual 						  COLRM(1)

NAME

     colrm -- remove columns from a file

SYNOPSIS

     colrm [start [stop]]

DESCRIPTION

     The colrm utility removes selected columns from the lines of a file.  A column is defined as a single character in a line.  Input is read
     from the standard input.  Output is written to the standard output.

     If only the start column is specified, columns numbered less than the start column will be written.  If both start and stop columns are spec-
     ified, columns numbered less than the start column or greater than the stop column will be written.  Column numbering starts with one, not
     zero.

     Tab characters increment the column count to the next multiple of eight.  Backspace characters decrement the column count by one.

ENVIRONMENT

     The LANG, LC_ALL and LC_CTYPE environment variables affect the execution of colrm as described in environ(7).

EXIT STATUS

     The colrm utility exits 0 on success, and >0 if an error occurs.

SEE ALSO

     awk(1), column(1), cut(1), paste(1)

HISTORY

     The colrm command appeared in 3.0BSD.

BSD
								  August 4, 2004							       BSD
All times are GMT -4. The time now is 08:23 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy