0

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] }'
5
  • 2
    Do you really not want to change the delimiter for the header? That will make any subsequent processing very hard. Also, how can we know which , to keep and which , to replace with |? Can we safely assume that if the current line has >10 fields, then we need to consider the 5th , as non-delimiter? – terdon May 5 at 16:48
  • Also, can we just assume that any , that comes before a space should not be considered a delimiter? – terdon May 5 at 16:50
  • Header is mentioned here just for the explanation. The commas are only coming in the Address Column. The solution I am looking for is to change delimiter of first 4 columns and last 2 columns. This is the best approach in my mind as in this way commas in the address column will not get replaced. The solution I mentioned here needs a little bit of tweaking as that solution almost does the job but only removes the commas in the address column. – Mr. Underscore May 5 at 16:58
  • 1
    At least with sed, it's going to be easier to replace the one "rogue" delimiter rather than replace all the "good" ones - you can always swap them after ex. sed -e '1!s/,/|/5' -e 'y/,|/|,/' bad.csv – steeldriver May 5 at 17:02
  • 1
    If your file doesn't contain a header, please remove it. We cannot guess what your file looks like. You need to make sure that your example is actually an accurate representation of your data otherwise our solutions won't work. So far, we think you have files with exactly 2 lines, where the first line should not be changed but the second should. – terdon May 5 at 17:02
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
3
  • 1
    Note: dd-mm-yyyy is, IMO, a terrible date format. Use the standard yyyy-mm-dd which sorts the same chronologically and lexically. – glenn jackman May 5 at 18:47
  • Will the utility command join not clash with the user defined function join? – guest_7 May 6 at 15:09
  • Certainly. It would force you to write command join when you wanted the external tool. That function is something I'd add into a script that is not using the external tool. – glenn jackman May 6 at 15:47
2

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
6
  • the awk version prints " England" twice, once appended to $5 and again in $6. actually deleting a field in awk is notoriously a PITA. – cas May 6 at 4:16
  • @cas argh, of course, thanks. Perl to the rescue, again. – terdon May 6 at 9:15
  • 2,$ can be just 1! In sed.No? – guest_7 May 6 at 15:11
  • 1
    @cas deleting a field in awk isn't a PITA, it's usually as simple as $field=RS; sub(/\n./,"") where what goes in the regexp in general is a string that matches the RS (usually a single char which is usually \n) followed by a string or regexp that matches the OFS (also usually a single char). For example to delete field 2 of a b c would be awk '{$2=RS; sub(/\n./,"")} 1'. That wouldn't delete a field at the end of the line of course but in most awks you can do that with just NF-- or there's various other trivial ways to do that including using another simple RE with sub(/re$/,"") – Ed Morton May 8 at 12:02
  • @EdMorton you'll have to excuse me because I remain convinced that splice() is much easier. and a lot more versatile (you can use splice to insert or replace fields, as well as delete them). Also, it's a tool designed for the job, not a weird trick / funky hack. I've got nothing against neat tricks, but i prefer to avoid them except as a last resort. – cas May 8 at 12:19
2

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
0
2

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.

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

3
  • Is it possible for an awk equivalent of your perl code? – guest_7 May 6 at 15:10
  • @guest_7 Possible? Yes. Am I going to write it? No. Deleting columns in awk is a PITA - it's one of my (many) standard reasons for tossing out whatever awk code i'm writing and starting over in perl. – cas May 6 at 15:21
  • 1
    @guest_7 I don't know if it's exactly equivalent but see unix.stackexchange.com/a/648705/133219 for a simple awk approach to this specific problem. – Ed Morton May 8 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.