To remove double quotes from specific columns


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers To remove double quotes from specific columns
# 1  
To remove double quotes from specific columns

Hi,

I've a requirement like, in a csv file of 30+ fields where all the columns are having double quotes I need to remove the double quotes from certain fields and certain field should remain as it is.

Eg:
Code:
"event_log_id","event_id","event_name","event_time","insertion_time","event_args","src_mac","src_name","src_util_id","src_device_type","src_event_count","src_admin_state","src_ops_state","src_location_util_id","src_location_type","src_service_pt_util_id","src_service_pt_type","src_addr_line1","src_addr_line2"

I need to remove double quotes from 5th 6th 9th 10th & 14th columns. How this can be achieved.

I've used command
Code:
sed 's/\"//g'

. But its removing all the double quotes in the file.


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 01-31-2018 at 07:24 AM.. Reason: Added CODE tags.
# 2  
Hi,
maybe:
Code:
for i in 14 10 9 6 5; do echo 's/"\([^"]*\)"/\1/'$i ; done | sed -f - file.csv

But, all fields must having double quotes otherwise this line not work correctly.
# 3  
Would this come close:
Code:
sed -r 's/"([^"]*)"/\1/5' file

Unfortunately, you'd need to repeat that for every target field.
# 4  
Moderator's Comments:
Mod Comment This thread has been moved from the Post Here to Contact Site Administrators and Moderators forum to the UNIX for Beginners Questions & Answers forum.

Please do not post technical questions in non-technical forums.
# 5  
As long as there aren't any commas in your quoted fields, the following is a fairly simple way of getting rid of the quotes in specified fields in a text file named file using commas as field separators:
Code:
awk -v list=5,6,9,10,14 '
BEGIN {	OFS = FS = ","
	n = split(list, fields, FS)
}
{	for(i = n; i ; i--)
		gsub(/"/, "", $fields[i])
}
1' file

With your sample input data, this produces the output:
Code:
"event_log_id","event_id","event_name","event_time",insertion_time,event_args,"src_mac","src_name",src_util_id,src_device_type,"src_event_count","src_admin_state","src_ops_state",src_location_util_id,"src_location_type","src_service_pt_util_id","src_service_pt_type","src_addr_line1","src_addr_line2"

which, I assume, is what you wanted.

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
# 6  
Code:
a="event_log_id","event_id","event_name","event_time","insertion_time","event_args","src_mac","src_name","src_util_id","src_device_type","src_event_count","src_admin_state","src_ops_state","src_location_util_id","src_location_type","src_service_pt_util_id","src_service_pt_type","src_addr_line1","src_addr_line2"

i=0
for s in `echo $a |sed 's/,/ /g'`
 do
 let i+=1
 if [ $i -ge 2 ]; then echo -n ,; fi
 if [ $i = 5 -o $i = 6 -o $i = 9 -o $i = 10 -o $i =14 ]
 then
   echo -n $s |sed 's/\"//g'
 else
  echo -n $s
 fi 
done


Last edited by abdulbadii; 02-10-2018 at 02:31 AM..
# 7  
Quote:
Originally Posted by abdulbadii
Code:
a="event_log_id","event_id","event_name","event_time","insertion_time","event_args","src_mac","src_name","src_util_id","src_device_type","src_event_count","src_admin_state","src_ops_state","src_location_util_id","src_location_type","src_service_pt_util_id","src_service_pt_type","src_addr_line1","src_addr_line2"

i=0
for s in `echo $a |sed 's/,/ /g'`
 do
 let i+=1
 if [ $i -ge 2 ]; then echo -n ,; fi
 if [ $i = 5 -o $i = 6 -o $i = 9 -o $i = 10 -o $i =14 ]
 then
   echo -n $s |sed 's/\"//g'
 else
  echo -n $s
 fi 
done

I don't know why one would want to use the above code instead of other suggestions that have been provided by others in this thread. The request presented in this thread is to process a file in CSV format with one line from a sample file provided to show the desired behavior. Nowhere is it suggested that files to be processed will only contain one line.

Note that there is a syntax error (marked in red above). That portion of the conditional expression would need to be changed to $i = 14 (note the added <space> between the = and the 14) to get rid of the syntax error.

If the syntax error is corrected, the above code (instead of invoking awk once per file or sed once per file assuming that invocation of sed includes five substitution commands) invokes sed five times for each line in each file to be processed. With anything more than a few lines, invoking sed so often would use significantly more system resources and cause a script using this method to run slower than scripts using the other suggestions provided in this thread.

Last edited by Don Cragun; 02-10-2018 at 01:36 PM.. Reason: Update statement on how many times sed is invoked by RudiC's suggestion.
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #642
Difficulty: Easy
The first full release of NeXTSTEP 1.0 shipped on September 18, 1988.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace double quotes inside the string data for all the columns

Please use code tags Hi, I have input data is below format and n of column in the multiple flat files. the string data has any double quotes(") values replaced to double double quotes for all the columns{""). Also, my input flat file each column string data has carriage of new line too.... (14 Replies)
Discussion started by: SSrini
14 Replies

2. Shell Programming and Scripting

Extract multiple columns base on double quotes as delimiter

Hi All, I have my data like below "1","abc,db","hac,aron","4","5" Now I need to extract 1,2,4th columns Output should be like "1",abc,db","4" Am trying to use cut command but not able to get the results. Thanks in advance. (4 Replies)
Discussion started by: weknowd
4 Replies

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

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

5. Shell Programming and Scripting

Trying to remove double quotes

Hi, I am little new to forum and new on unix side. I have a small issue below: I am reading a file that has 5 columns something like below. col1,col2,col3,col4,col5 Some records are having double quoted values something like below: "value1","value2","value3","value4","value5" I need... (8 Replies)
Discussion started by: Saanvi1
8 Replies

6. UNIX for Dummies Questions & Answers

Remove two delimiters, space and double quotes

I would like to know how to replace a space delimiter with a ^_ (\037) delimiter and a double quote delimiter while maintaining the spaces inside the double quotes. The double quote delimiter is only used on text fields. I'd prefer a one-liner, but could handle a function or script that accepts... (4 Replies)
Discussion started by: SteveDWin
4 Replies

7. Shell Programming and Scripting

To append two columns without double quotes

Hi i have a file with follw data "20090427","0","","16371311","-100200","","","","16371311","JUL","09" In the 10th column i need to convert the month name into month number in this case JUL will be 7 and append the 10th and 11th column which shows me the output as 709. Can you suggest a shell... (11 Replies)
Discussion started by: vee789
11 Replies

8. Shell Programming and Scripting

replace value with double quotes of specific coulmn value in csv file

Hi, I am trying to replace a specific column values in a csv file with double quotes. Example: SNO,NAME,ZIPCODE,RANK 1,Robert,74538,12 2,Sam,07564,13 3,Kim, Ed,12345,14 Desired Output: SNO,NAME,ZIPCODE,RANK 1,Robert Ken,74538,12 2,Sam Mik,"07564",13 3,"Kim, Ed",12345,14 I... (3 Replies)
Discussion started by: techmoris
3 Replies

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

10. UNIX for Dummies Questions & Answers

Remove double quotes

A Triva question. What is the easy way to remove the double quotes in the file in the following format. "asdfa","fdgh","qwer" tr -d '\"' <filename >newfilename mv newfilename oldfilename This need to be handled in a script. Any better way to do this. Will sed be more effecient? One... (3 Replies)
Discussion started by: deepakwins
3 Replies

Featured Tech Videos