Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

/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.

/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!

share|improve this question
Which operating system are you on? Which tools to you normally use? What have you tried? – slhck Jun 4 '12 at 5:37
Windows 7 using TextPad's regex search and replace function but I can just as easily switch over to a linux command line. I'm new to regex so not really understanding it a whole lot. – StefWill Jun 4 '12 at 5:39
It might be a lot easier to just load this into a spreadsheet and merge the fields there – Journeyman Geek Jun 4 '12 at 5:42
I would but excel can't deal with 2 million rows and I'd rather not have to split the files. – StefWill Jun 4 '12 at 5:47
up vote 1 down vote accepted

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.
share|improve this answer
Can you just let me know if you downvoted my answer or not? Trying to retrace a serial downvote incident. – slhck Jun 4 '12 at 20:28
@slhck: No. I didn't. As you can see from my profile page I've only cast two downvotes in my almost 3 yrs of activity. I know you're only asking for the purpose of process of elimination. – Dennis Williamson Jun 4 '12 at 23:42
That works perfectly. Now I just need to de-construct what you've done and how it works. But you have pulled my bacon out of the fire on this one ;) – StefWill Jun 4 '12 at 23:44
@StefWill: Feel free to ask for clarification if my explanation is lacking. – Dennis Williamson Jun 4 '12 at 23:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .