Regex to join fields in a CSV

Question

I have a CSV with over 2 million records in it with the following format.

path;name;extension;size;date;user    
/foo/;difacs;cgi;3,795;18-07-2011;Unix User\pads
/foo/;difacs.cgi;bak;2,622;03-12-2009;Unix User\pads
/foo/test/kzt/netcdfSample/testing/;zzz;;401;27-07-2006;Unix User\kzt
/foo/test/kzt/netcdfSample/vic_netcdf_popup/;a;txt;1,832;17-02-2006;Unix User\kzt

I need to join the path, name and extension into one correctly formatted field.

path;size;date;user    
/foo/difacs.cgi;3,795;18-07-2011;Unix User\pads
/foo/difacs.cgi;bak;2,622;03-12-2009;Unix User\pads
/foo/test/kzt/netcdfSample/testing/zzz/;401;27-07-2006;Unix User\kzt
/foo/test/kzt/netcdfSample/vic_netcdf_popup/a.txt;1,832;17-02-2006;Unix User\kzt

Thanks in advance!

Answer 1

This is a variation on slhck's answer which deals properly with an empty extension field (and avoids falsely replacing a dot that might have existed in the second or third field intentionally):

sed 's/^\([^;]*\);\([^;]*\)/\1\2/;ta;:a;s/^[^;]\+;;/&/;t;s/;/./' inputfile

It's not necessary to use a third capture group. That answer works without it. It's not necessary to escape the dot on the right hand side of the substitute command.

Here's an explanation of my script:

• capture the first two fields, excluding the semicolons that delimit them.
• ta;:a - if a successful replacement was made, then branch to label :a which immediately follows - this effectively clears the "success" flag
• s/^[^;]\+;;/&/ - replace a sequence of non-semicolons followed by two semicolons (the concatenated first and second fields followed by an empty third field) with itself - it's a no-op, but it sets the "success" flag.
• t - if the last replacement was successful (the third field is empty), skip to the end of processing of the current line (since no label was specified)
• s/;/./ - if we've gotten to this point (the third field was not empty), replace the semicolon with a dot.