CODEWITHSUNDEEP
regexperlunixawksed
longhorndkr
longhorndkr

Reputation: 21

awk/sed help - how do I find columns that contain a comma and then double-quote them?

I've searched the archives but cannot find this particular question and am having trouble piecing together awk parts to come up with an answer. I have a Unix file with about a dozen columns delimited by pipes. I would like to take this delimited file, convert it to .csv and automate mailing it to staff to open in Excel. My problem is that some of the columns have commas inside them (Ex: |RYAN, NOLAN| which makes conversion to .csv problematic.

Does anyone know a way in awk/sed/perl that will find the columns in which a comma exists (there could be multiple instances within a single row) and enclose each of those columns with double-quotes, which will allow Excel to properly open just a single column?

Ex dataset:

MSH|^~\&|IMT7||EXSCH|384^SCH|20150202082830||MFN^M02|RYAN, NOLAN|P|2.2|Last|First|DOE, JOHN|...

I would like the above to become:

MSH|^~\&|IMT7||EXSCH|384^SCH|20150202082830||MFN^M02|"RYAN, NOLAN"|P|2.2|Last|First|"DOE, JOHN"|...

Thank you gurus in advance! Dave

Upvotes: 2

Views: 267

Answers (3)

repzero
repzero

Reputation: 8412

Using awk 

awk -F '|' -v OFS='|' '{for(i=1;i<=NF;++i){if($i~/,/){$i="\""$i"\""}};print}' file

example

echo "MSH|^~\&|IMT7||EXSCH|384^SCH|20150202082830||MFN^M02|RYAN, NOLAN|P|2.2|Last|First|DOE, JOHN|..."|awk -F '|' -v OFS='|' '{for(i=1;i<=NF;++i){if($i~/,/){$i="\""$i"\""}};print}'

results

MSH|^~\&|IMT7||EXSCH|384^SCH|20150202082830||MFN^M02|"RYAN, NOLAN"|P|2.2|Last|First|"DOE, JOHN"|...

using a sed approach

sed  's/|\([^|]\+,[^|]\+\)|/|"\1"|/g' file

Upvotes: 3

Arjun Mathew Dan
Arjun Mathew Dan

Reputation: 5298

With sed:

sed 's/\([^,|]*,[^,|]*\)/"&"/g' File

or

sed -r 's/([^,|]*,[^,|]*)/"&"/g' File

Surround every pattern matching the criteria (sequence of non-, & non-| characters followed by , followed by sequence of non-, & non-| characters) with ".

Upvotes: 0

TobyLL
TobyLL

Reputation: 2296

The following Perl script will do it, just save the script, and then pipe the file into it:

#! /usr/bin/perl
use strict;
while (my $line = <STDIN>) {
    chomp $line;
    my @out;
    foreach (split /\|/, $line) {
        $_ = qq("$_") if (/,/);
        push @out, $_;
    }
    print join "|", @out, "\n";
}

You could probably make this into a single line of Perl, but it's much easier to read this way.

Upvotes: 2

Related Questions