CODEWITHSUNDEEP
regexunixsed
user3611574
user3611574

Reputation: 13

Changing Delimiter in a csv file using sed command

I have a file with the below format.

1,"John, Williams",23,USA

2,"Abraham, Lincoln",24,NC

i need the output format as

1~"John, Williams"~23~USA

2~"Abraham, Lincoln"~24~NC

I have wrote something using sed command but it is not working..

 sed "s/\",\"/\"~\"/g"

but it is not giving correct format..

Upvotes: 0

Views: 1349

Answers (3)

NeronLeVelu
NeronLeVelu

Reputation: 10039

sed ':a
s/,\(\([^"]*"[^"]*"\)*[^"]*\)$/~\1/g
t a' YourFile

replace any , that are followed by any peer of double quote

Upvotes: 0

Jotne
Jotne

Reputation: 41456

This awk should do:

awk -F, '{for (i=1;i<NF;i++) {if ($i~/"/ && gsub(/"/,"\"",$i)==1) f=!f;printf "%s"(f?",":"~"),$i}print $NF}'
1~"John, Williams"~23~USA

2~"Abraham, Lincoln"~24~NC
a~"foo"~b~c

It does test if , is inside ".." or not, then select ~ or ,

Also handle a,"foo",b,c

Upvotes: 0

BMW
BMW

Reputation: 45233

If your awk version is Gnu version 4 above, and support new feature of FPAT, here is a simply way:

awk 'BEGIN{FPAT = "([^,]+)|(\"[^\"]+\")"}$1=$1' OFS="~" file

Detail about usage of FPAT is here:4.7 Defining Fields By Content

4.7 Defining Fields By Content

NOTE: This section discusses an advanced feature of gawk. If you are a novice awk user, you might want to skip it on the first reading.

Normally, when using FS, gawk defines the fields as the parts of the record that occur in between each field separator. In other words, FS defines what a field is not, instead of what a field is. However, there are times when you really want to define the fields by what they are, and not by what they are not.

The most notorious such case is so-called comma separated value (CSV) data. Many spreadsheet programs, for example, can export their data into text files, where each record is terminated with a newline, and fields are separated by commas. If only commas separated the data, there wouldn’t be an issue. The problem comes when one of the fields contains an embedded comma. While there is no formal standard specification for CSV data24, in such cases, most programs embed the field in double quotes. So we might have data like this:

Robbins,Arnold,"1234 A Pretty Street, NE",MyTown,MyState,12345-6789,USA
The FPAT variable offers a solution for cases like this. The value of FPAT should be a string that provides a regular expression. This regular expression describes the contents of each field.

In the case of CSV data as presented above, each field is either “anything that is not a comma,” or “a double quote, anything that is not a double quote, and a closing double quote.” If written as a regular expression constant (see Regexp), we would have /([^,]+)|("[^"]+")/. Writing this as a string requires us to escape the double quotes, leading to:

FPAT = "([^,]+)|(\"[^\"]+\")"
Putting this to use, here is a simple program to parse the data:

BEGIN {
    FPAT = "([^,]+)|(\"[^\"]+\")"
}

{
    print "NF = ", NF
    for (i = 1; i <= NF; i++) {
        printf("$%d = <%s>\n", i, $i)
    }
}

Upvotes: 1

Related Questions