Ronald and others, Thanks for the solutions. I have folks uploading CSV files to a web server that I then have to parse the columns and rows on the server and populate a database. When I asked for help here I also went back to the client and they agreed to do a find/replace to remove the commas from the spreadsheet before they upload the csv file. I should be good to go with out having to do any munging.
Thanks, Ted *********************** Ted Burger **************************** t...@tobsupport.com ********* www.tobsupport.com On Feb 17, 2014, at 11:23 PM, Ronald J Kimball wrote: > On Mon, Feb 17, 2014 at 07:44:29PM -0500, Ted Burger wrote: >> Folks, >> >> Thanks in advance, but I am struggling with putting together a grep search. >> >> I have Excel created .csv files that I have to parse. >> Excel has a "feature" where it incodes commas in a field by enclosing that >> field with quotes. >> This means that you might have rows that look like: >> aaaaa,bbbbb,"cccc,ccccc",ddddd,eeeee >> "aaaa,aaaa",bbbb,cccc,dddd,"eeee,eeee" >> So what I want to do is replace any comma that is enclosed within quotes >> with a space. >> I could survive with just deleting the comma if that makes it easier. >> >> I think this requires some look-ahead or look-behind, which is over my head. >> I will have to have a grep solution, no perl no shell, no applescript. > > I'm curious, what is your ultimate goal? I'm wondering if there may be > ways to achieve it without having to munge the data first. > > By the way, if you have access to Excel, it would be trivial to do a > search/replace within Excel to replace commas with spaces. > > > Anyway, I think you would want something like this: > > Find > > (\"[^\",\r]*),([^\"\r]*\")(?=(?:,(?:[^\",\r]+|\"(?:[^\"\r]|\"\")*\"))*$) > > Replace > > \1 \2 > > The positive look-ahead matches any remaining fields to the end of the > line, to make sure the comma being replaced is within a field rather than > between fields. It should properly handle quoted empty fields and fields > containing quotation marks (which are escaped by doubling, e.g. "abc""def"). > > This will only find the first comma in each field, so you'll need to run it > multiple times if a field can contain multple commas. > > Ronald > > -- > This is the BBEdit Talk public discussion group. If you have a > feature request or would like to report a problem, please email > "supp...@barebones.com" rather than posting to the group. > Follow @bbedit on Twitter: <http://www.twitter.com/bbedit> > > --- > You received this message because you are subscribed to the Google Groups > "BBEdit Talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to bbedit+unsubscr...@googlegroups.com. > To post to this group, send email to bbedit@googlegroups.com. -- This is the BBEdit Talk public discussion group. If you have a feature request or would like to report a problem, please email "supp...@barebones.com" rather than posting to the group. Follow @bbedit on Twitter: <http://www.twitter.com/bbedit> --- You received this message because you are subscribed to the Google Groups "BBEdit Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to bbedit+unsubscr...@googlegroups.com. To post to this group, send email to bbedit@googlegroups.com.
