Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace double quotes inside the string data for all the columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace double quotes inside the string data for all the columns
# 1  
Old 05-15-2018
Replace double quotes inside the string data for all the columns
Moderator's Comments:
Mod Comment 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. this new lines also should replace double double quotes if data has presence single double quotes.

Please advise me how to handle this using awk or sed comand.

Code:
Input :
emp|^^|ename|^^|sal|^^|refname
"PM"|^^|"R"A"M"|^^|100|^^|"X""YZ"
"RM"|^^|"PR
RED"
ORANG"
SRI"|^^|500|^^|"ABCD"
"AM"|^^|""MAR""|^^|200|^^|"ABC""

Code:
Output:
emp|^^|ename|^^|sal|^^|refname
"PM"|^^|"R""A""M"|^^|100|^^|"X"""YZ"
"RM"|^^|"PR
RED""
ORANG""
SRI"|^^|500|^^|"ABCD"
"AM"|^^|"""MAR"""|^^|200|^^|"ABC"""

Thanks,

Regard,
Srini
Last edited by jim mcnamara; 05-16-2018 at 12:52 AM..
# 2  
Old 05-16-2018
What code have you tried? Please let us know what OS and shell you are using - it helps us to give you the best possible answer.
# 3  
Old 05-16-2018
Hi jim mcnamara,

Thanks for the reply,
we are using redhat 7 version and tried using sed command.
The flatfile data have carriage of new line for some of the columns, in that scenarios sed replacement is not working.

Sample Code :

Code:
### change Double Quote value (") change to double double quote (""). 
sed  -i 's/"/""/g;s/\\b^""""|^|\b\/""""/"""/g; s/\\b|^|""""\b\/""""/"""/g' $Inpufile
#### Add envlose character double quote value for each column
sed  -i 's/|^^|""/|^^|"/g; s/""|^^|/"|^^|/g' $Inpufile
##remove " value in begin and end of each row###########
sed -i  's/^""/"/;s/""$/"/' $Inpufile

please advise easiest way to replace double quote values inside the string for each column.

Thanks,

Moderator's Comments:
Mod Comment Seriously: Please use CODE tags as required by forum rules!
Last edited by RudiC; 05-16-2018 at 04:18 AM.. Reason: Added CODE tags.
# 4  
Old 05-16-2018
Quote:
Originally Posted by SSrini
Code:
"PM"|^^|"R"A"M"|^^|100|^^|"X""YZ"

Code:
Output:
"PM"|^^|"R""A""M"|^^|100|^^|"X"""YZ"

In the example you gave, the input line has 10 double quotes, while the output line has 12 double quotes. If every double quote is supposed to be doubled, there should be 20 double quotes in the output.
# 5  
Old 05-16-2018
I have to second rovf in that neither your description nor the samples are consistent. Even with the assumption that double quotes WITHIN the fields should be doubled, excluding the ones enclosing the field, I can't find a logical explanation why "X""YZ" should become "X"""YZ".
# 6  
Old 05-16-2018
How about
Code:
awk -F\| '
        {while (NF < 7) {getline X
                         $0 = $0 "\001" X
                        }
         for (i=1; i<=NF; i++)  {gsub (/"/, "&&", $i)
                                 sub (/^"/, "", $i)
                                 sub (/"$/, "", $i)
                                }
         gsub (/\001/, "\n")
        }
1
' OFS=\| file
emp|^^|ename|^^|sal|^^|refname
"PM"|^^|"R""A""M"|^^|100|^^|"X""""YZ"
"RM"|^^|"PR
RED""
ORANG""
SRI"|^^|500|^^|"ABCD"
"AM"|^^|"""MAR"""|^^|200|^^|"ABC"""

# 7  
Old 05-16-2018
Hi Rudic,

Thanks a lot for your email.
I have tested with sample data, it's working as expected. I will test multiple flat files and let you know in case of any other issues.

Could you please explain me how above awk command replacing double double quotes where ever the double quotes inside the string value for each columns.

Thanks again.

Regards,
Srini
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Programming

Replace comma which is not inside brackets,quotes or paranthesis

Hi All, I want to replace the commas which are not inside parenthesis,quotes if input is abc,,lm,(no,pq,rs),{tu,vw,xy},zs,"as,as,fr",'ab,cd,ef' output should be abc lm (no,pq,rs) {tu,vw,xy} zs "as,as,fr" 'ab,cd,ef' I tried this str.replaceAll("\\(.*?\\)|(,)", " "); say my string... (3 Replies)
Discussion started by: preethy
3 Replies

3. Shell Programming and Scripting

sed command to replace string that contain blackslash,double quotes

Hi All, I have been trying to replace a string using the sed command string value contain blackslash and double quotes. I am not a expert writer of unix script but do try not to ask question. I have almost given up. Hope you all can give me some suggestion I want to replace a place string... (6 Replies)
Discussion started by: thanush9sep
6 Replies

4. Shell Programming and Scripting

Replace double quotes with a single quote within a double quoted string

Hi Froum. I have tried in vain to find a solution for this problem - I'm trying to replace any double quotes within a quoted string with a single quote, leaving everything else as is. I have the following data: Before: ... (32 Replies)
Discussion started by: pchang
32 Replies

5. Shell Programming and Scripting

Preserve commas inside double quotes (perl)

Hi, I have an input file like this $ cat infile hi,i,"am , sam", y hello ,good, morning abcd, " ef, gh " ,ij no, "good,morning", yes, "good , afternoon" from this file I have to split the fields on basis of comma"," however, I the data present inside double qoutes should be treated as... (3 Replies)
Discussion started by: sam05121988
3 Replies

6. Shell Programming and Scripting

Replace double double quotes using AWK/SED

Hi, I have data as "01/22/97-"aaaaaaaaaaaaaaaaa""aaa""aabbbbbbbbcccccc""zbcd""dddddddddeeeeeeeeefffffff" I want to remove only the Consequitive double quotes and not the one which occurs single. My O/P must be ... (2 Replies)
Discussion started by: Bhuvaneswari
2 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

To Replace comma with Pipe inside double quotes

Hi, I have a requirement to replace the comma's inside the double quotes. The comma's inside the double quotes will get changed dynamically. Input Record: "Washington, DC,Prabhu,aju",New York Output Record: "Washington| DC|Prabhu|aju",New York I tried with the below command but it... (3 Replies)
Discussion started by: prabhutkl
3 Replies

9. Shell Programming and Scripting

sed removing comma inside double quotes

I have a csv file with lines like the followings 123456,"ABC CO., LTD","XXX" 789012,"DEF LIMITED", "XXX" before I bcp this file to database, the comma in "CO.," need to be removed first. My script is cat <filename> | sed 's/"CO.,"/"CO."/g' but it doesn't work. Can anyone here able to... (2 Replies)
Discussion started by: joanneho
2 Replies

10. Shell Programming and Scripting

escaping double-quotes inside the script?

I'm having a strange problem with escaping double-quotes. I have a script that looks like this: #!/bin/bash for HOST in `cat $INFILE | grep -v ^#` do for VFILER in `some_command` do echo " " echo -e '\E The problem with ssh command... (3 Replies)
Discussion started by: GKnight
3 Replies
Login or Register to Ask a Question