How to delete the commas in a .CSV file that are enclosed in a string with double quotes? Post: 302887936

Sponsored Content

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 302887936 by Chubler_XL on Tuesday 11th of February 2014 06:30:37 PM

02-11-2014

Moderator

You could also use the csv parse/create functions I posted for you here: Remove the double quotes from a .CSV file except for values enclosed in double quotes | Unix Linux Forums | Shell Programming and Scripting

Calling them like this:

Code:

{
  num_fields = csv_parse($0, csv, ",", "\"", "\"", "\n", 1)
  for(i=1; i<=num_fields; i++) gsub(/,/," ",csv[i])
  print csv_create(csv, num_fields)
}

Note it also removes the quotes, as they aren't needed as the fields don't contain commas any more:

Code:

$ awk -f remove_commas.awk infile3
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,

Chubler_XL

View Public Profile for Chubler_XL

Find all posts by Chubler_XL

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

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

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

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

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

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

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"

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

9. Shell Programming and Scripting

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

10. Shell Programming and Scripting

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

LEARN ABOUT DEBIAN

ifpps

ifpps(8)							netsniff-ng-toolkit							  ifpps(8)

NAME

       ifpps - fetch and format kernel network statistics

SYNOPSIS

       ifpps	 -d|--dev <netdev> [-t|--interval <sec>][-p|--promisc][-c|--term]
	    [-C|--csv][-H|--csv-tablehead][-l|--loop][-v|--version][-h|--help]

DESCRIPTION

       A tiny tool to provide top-like reliable networking statistics.	ifpps reads out the 'real' kernel statistics, so it does not give
       erroneous statistics on high I/O load.

OPTIONS

       ifpps --dev eth0
	   Fetch eth0 interface statistics.

       ifpps --dev eth0 --interval 60 --csv
	   Output eth0 interface statistics every minute in CSV format.

OPTIONS

       -h|--help
	   Print help text and lists all options.

       -v|--version
	   Print version.

       -d|--dev <netdev>
	   Device to fetch statistics for i.e., eth0.

       -p|--promisc
	   Put the device in promiscuous mode

       -t|--interval <time>
	   Refresh time in sec (default 1 sec)

       -c|--term
	   Output to terminal

       -C|--csv
	   Output in CSV format.  E.g. post-processing with Gnuplot et al.

       -H|--csv-tablehead
	   Print CSV table head.

       -l|--loop
	   Loop terminal output.

AUTHOR

       Written by Daniel Borkmann <daniel@netsniff-ng.org>

DOCUMENTATION

       Documentation by Emmanuel Roullit <emmanuel@netsniff-ng.org>

BUGS

       Please report bugs to <bugs@netsniff-ng.org>

								    2012-06-29								  ifpps(8)