Update delimiter of specific columns

Question

I have a comma-separated file with 7 fields. One of these fields, however, contains , in the value. I want to change the delimiter of the file to | without changing the data of the field with the ,.

File I have:

Name,Age,Country,ID,Address,Category,DOB
John Doe,19,England,3653,Manchester, England,Main Worker,20-05-1995

Required Output:

Name,Age,Country,ID,Address,Category,DOB
John Doe|19|England|3653|Manchester, England|Part Time Worker|20-05-1995

I have tried multiple solutions but have not been able to achieve what I am looking for. I tried the following command which only updates the delimiter of the first 4 columns:

sed 's/,/|/;s/,/|/;s/,/|/;s/,/|/' file

The approach I'm looking for is to update the delimiter of the first 4 columns and last 2 columns. In this way I can have the file with updated delimiter without changing the Address column.

I have come up with the following piece of code but this removes the , between Manchester and England.

awk '{ORS="";N=split($0,a,",");\
            print a[1]"|"a[2]"|"a[3]"|"a[4]"|"; \
            for(i=5;i<N-1;i++) print a[i]; 
            print "|"a[N-1]"|"a[N] }'

Answer 1

With awk you can try:

awk -F, -v OFS='|' 'NR==1{print $0} NR>1{$5 = $5 FS $6; print $1,$2,$3,$4,$5,$7,$8}' file
Name,Age,Country,ID,Address,Category,DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Answer 2

With bash, one can do this, which handles an arbitrary number of commas in the address field:

# function to join strings with a separator
join() { 
    local IFS=$1
    shift
    printf '%s\n' "$*"
}

# process the file
{ 
    IFS=, read -ra header
    join '|' "${header[@]}"
    f=${#header[@]}                     # expected num of fields

    while IFS=, read -ra row; do
        n=${#row[@]}                    # actual num

        # with a placeholder for the address
        real_row=("${row[@]:0:4}" __ "${row[@]:n-2}")

        # set the actual address
        real_row[4]=$(join ',' "${row[@]:4:n-f+1}")

        join '|' "${real_row[@]}"
    done
} < file

outputs

Name|Age|Country|ID|Address|Category|DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Answer 3

You haven't really explained how we can know which commas to keep and which to change. Based on the single example line you have given us, it might be enough to just replace all , that don't come after a space with a |:

$ sed -E 's/,(\S)/\|\1/g' file
Name|Age|Country|ID|Address|Category|DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Or, if you really don't want to change the header:

$ sed -E '2,${s/,(\S)/\|\1/g}' file
Name,Age,Country,ID,Address,Category,DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Alternatively, if we cannot rely on the space and instead need replace all commas except the 5th on lines that to something like this: "if this line has more than 7 fields, then consider the 5th , in the line as part of the 4th field and not as a delimiter". If so, this should work for you:

$ $ perl -F, -lane 'if($#F>6){$F[4].=",$F[5]"; splice(@F,5,1)} print join("|",@F)' file
Name|Age|Country|ID|Address|Category|DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Or, again, if you really don't want to change the header, use:

$ perl -F, -lane 'if($#F>6){$F[4].=",$F[5]"; splice(@F,5,1)} $.==1 ? print : print join("|",@F)' file
Name,Age,Country,ID,Address,Category,DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

Answer 4

Using any awk in any shell on every Unix box:

$ awk -F, -v OFS='|' 'NF>7{$5=$5",\n"; sub(/\n./,"")} 1' file
Name,Age,Country,ID,Address,Category,DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

$5=$5",\n" adds a , and a newline (which can't otherwise be present in the current record since records are separated by newlines) to the end of field 5 which, by the act of modifying a field, causes awk to rebuild $0 replacing all ,s between fields (i.e. all commas in the record except the comma we just made part of field 5) so we get ...|field4|field5,<newline>|field6|field7|....

sub(/\n./,"") then removes the only <newline>| that can exist in the record, i.e. the one between field5, and field6, so we're left with ...|field4|field5,field6|field7|... which is the desired output.

Answer 5

$ perl -F, -lne 'if ($#F == 7) {$F[4] .= ",$F[5]"; splice @F,5,1};
                 print join("|",@F);' input.csv
Name|Age|Country|ID|Address|Category|DOB
John Doe|19|England|3653|Manchester, England|Main Worker|20-05-1995

This perl one-liner checks to see how many comma-separated fields are in each input line. If there are 8 fields, it appends a comma and the contents of field 5 to field 4, then deletes field 5 using splice().

It does all this using the auto-split array @F, which is the perl equivalent to awk's $1, $2, $3, etc when perl is called with either -F or -a.

Then it prints the @F array joined by pipe characters | regardless of whether it changed fields 4 & 5 or not.

Note: perl arrays start from zero, not one. $#F returns the last index number of array @F, which is why the test is == 7 rather than == 8. In a record with 8 fields, @F will have indices 0..7. This also means that the 5th field (Address) is referred to as $F[4], not $F[5].