16

I have a input text file with 10 columns, while processing this file, in one of the middle column, I am getting this type of data.I required column value to be as below:

Input Column value: "This is my new program:"Hello World""

Required Column value: "This is my new program: Hello World".

Please help me in any Unix shell scripts or any commands. Really appreciate your time and thanks in advance.

6
  • 4
    can you provide a full input, exactly as is? it's hard to derive a solution based on partial data. Otherwise, a simple sed 's/"//g' would suffice.
    – Dani_l
    Commented Oct 14, 2015 at 17:17
  • Do you have any control about how the input file is generated? Sounds like it's generating invalid CSV. You should have "This is my new program:""Hello World""" Commented Oct 14, 2015 at 17:51
  • No, I don't have any control over source file. I just need to have only first and last double quotes..All the extra double quotes in between the column value need to be removed.
    – Karthik
    Commented Oct 14, 2015 at 18:03
  • What is the column separator? Commented Oct 14, 2015 at 18:09
  • Pipe (|) delimiter
    – Karthik
    Commented Oct 14, 2015 at 18:18

2 Answers 2

30

A very simple option is to use sed as @Dani proposes if you want to remove all double-quotes.

$ echo "This is my program \"Hello World\"" | sed 's/"//g'

This is my program Hello World

Nevertheless, if you want to remove only internal quotes, I would suggest removing all quotes and adding one at the beginning and one at the end as follows.

Let's say we have a file sample.txt with these contents:

$ cat sample.txt

"This is the "First" Line"
"This is the "Second" Line"
"This is the "Third" Line"

Then, if you want to remove only internal quotes, I would suggest the following:

$ cat sample.txt | sed 's/"//g' | sed 's/^/"/' |sed 's/$/"/'

"This is the First Line"
"This is the Second Line"
"This is the Third Line"

Explanation:

sed 's/"//g' removes all double quotes on each line

sed 's/^/"/' adds a double-quote at the beginning of each line

sed 's/$/"/' adds a double-quote at the end of each line

sed 's/|/"|"/g' adds a quote before and after each pipe.

Hope this helps.

EDIT: As per the pipe separator comment, we have to slightly change the command

Let sample.txt be:

$ cat sample.txt

"This is the "First" column"|"This is the "Second" column"|"This is the "Third" column"

Then, adding a replacer command for the pipe give us the final solution.

$ cat sample.txt | sed 's/"//g' | sed 's/^/"/' |sed 's/$/"/' | sed 's/|/"|"/g'

"This is the First column"|"This is the Second column"|"This is the Third column"

The script option

Using this sample.txt file

$ cat sample.txt
"This is the "first" column"|12345|"This is the "second" column"|67890|"This is the "third" column"

And this script

#!/bin/ksh

counter=1
column="initialized"
result=""
while [[ "$column" != "" ]]
do
    eval "column=$(cat sample.txt | cut -d"|" -f$counter)"
    eval "text=$(cat sample.txt | cut -d"|" -f$counter | grep '"')"
    if [[ "$column" = "$text" && -n "$column" ]]
    then
        if [[ "$result" = "" ]]
        then
            result="_2quotehere_${column}_2quotehere_"
        else
            result="${result}|_2quotehere_${column}_2quotehere_"
        fi
    else
        if [[ -n "$column" ]]
        then
            if [[ "$result" = "" ]]
            then
                result="${column}"
            else
                result="${result}|${column}"
            fi
        fi
    fi
    echo $result | sed 's/_2quotehere_/"/g' > output.txt
    (( counter+=1 ))
done
cat output.txt
exit 0

You will get this:

$ ./process.sh
"This is the first column"|12345|"This is the second column"|67890|"This is the third column"

$ cat output.txt
"This is the first column"|12345|"This is the second column"|67890|"This is the third column"

I hope this is the processing you need.

Let me know!

FINAL EDIT

This script processes the input line you provided, several times included. Only restriction is that all 20 columns MUST BE on the same line.

#!/bin/ksh

rm output.txt > /dev/null 2>&1
column="initialized"
result=""
lineCounter=1
while read line
do
    print "LINE $lineCounter: $line"
    counter=1
    while [[ ${counter} -le 20 ]]
    do
        eval 'column=$(print ${line} | cut -d"|" -f$counter)'
        eval 'text=$(print ${line} | cut -d"|" -f$counter | grep \")'
        print "LINE ${lineCounter} COLUMN ${counter}: $column"
        if [[ "$column" = "$text" && -n ${column} ]]
        then
            if [[ "$result" = "" ]]
            then
                result="_2quotehere_$(echo ${column} | sed 's/\"//g')_2quotehere_"
            else
                result="${result}|_2quotehere_$( echo ${column} | sed 's/\"//g')_2quotehere_"
            fi
        else
            if [[ "$result" = "" ]]
            then
                result=${column}
            else
                result="${result}|${column}"
            fi
        fi
        (( counter+=1 ))
    done
    (( lineCounter+=1 ))
    echo -e $result | sed 's/_2quotehere_/"/g' >> output.txt
    result=""
done < input.txt
print "OUTPUT CONTENTS:"
cat output.txt

exit 0

From here, you must be able to get it working for your particular case.

23
  • sed 's/\([^"]\)"/\1/g'
    – Costas
    Commented Oct 14, 2015 at 19:14
  • But here is the problem.. In my row i have numeric columns also..If i go with this, sed 's/"//g' | sed 's/^/"/' |sed 's/$/"/' | sed 's/|/"|"/g' . This will print all column values with double quotes but I just want only string columns to have double quotes. How to write this cat sample.txt | sed 's/"//g' | sed 's/^/"/' |sed 's/$/"/' | sed 's/|/"|"/g' into a text file?
    – Karthik
    Commented Oct 14, 2015 at 19:14
  • To dump it into a text file, just redirect the output cat sample.txt | sed 's/"//g' | sed 's/^/"/' |sed 's/$/"/' | sed 's/|/"|"/g' > output.txt Let me look for a workaround for the numbers. Is there any other piece of information we need to solve this? Commented Oct 14, 2015 at 19:18
  • By the complexity of the problem, I think is better to go for an structured script that makes it happen. Do you agree? Commented Oct 14, 2015 at 19:22
  • 1
    @Karthik: Please stop having this conversation in comments.  You should edit your question to show examples of your data, what commands you've tried, what results they give, and why they aren't what you want. Commented Oct 22, 2015 at 0:45
0

The simplest criteria to edit a field is "if it has letters".
Fields with only numbers (and some symbols .,- etc.) should be left alone.
This simple awk script does the job:

#!/bin/bash

awk -v FS='|' -v OFS='|' '{
for ( i=1; i<=NF; i++) {
    if ( $i ~ /[a-zA-Z]/ ) {
        gsub(/["]/,"",$i); $i="\"" $i "\""    # Remove dquotes, add them back.
    }
} }1' input.txt >output.txt

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .