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 **************************** [email protected] ********* 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 > "[email protected]" 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 [email protected]. > To post to this group, send email to [email protected]. -- This is the BBEdit Talk public discussion group. If you have a feature request or would like to report a problem, please email "[email protected]" 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 [email protected]. To post to this group, send email to [email protected].
