Hi all! I have a strange search/replace situation & sometimes looking at GREP makes me cross-eyed!

[Aethon]

I have a big file that I had exported from the office as a CSV (forgetting that I had done the first part a while back as tab-separated).

So now I have a list where some of the text columns contains commas and this throws the whole structure/order off.

Here is a sample of the data.

home,ATC-Sea-Serpents-by-Gustav-Klimt,ArtToCanvas,54W x 27H inches : Sea Serpents by Gustav Klimt,ArtToCanvas,prints,319,,,1,https://arttocanvas.com

home,ATC-Gold-Swirls-by-Lisa-Kowalski,ArtToCanvas,30W x 40H inches : Gold Swirls by Lisa Kowalski,ArtToCanvas,prints,269,,,1,https://arttocanvas.com

home,ATC-Blue-Green-and-Brown-by-Mark-Rothko,ArtToCanvas,26W x 32H inches : Blue, Green, and Brown by Mark Rothko,ArtToCanvas,prints,219,,,1,https://arttocanvas.com

home,ATC-La-seperazione-dell-atomo-by-Salvador-Dali,ArtToCanvas,50W x 26H inches : La seperazione dellatomo by Salvador Dali,ArtToCanvas,prints,299,,,1,https://arttocanvas.com

Now, what I want is to replace the commas from the centre column (in bold).

I had use a grep search like the following :

ArtToCanvas,(.+),ArtToCanvas,prints 

to select the correct information—but I don't know to do a further replacement of the commas when I output the \1.

Is this even possible using  Bbedit Grep?

I have version 10.5.3

Thanks,

George

[ThePorgie]

In your sample data would the patten be inconsistent regarding the number of commas that need to be replaced? I'm thinking that might be the case looking at your sample...Also if you want to replace the commas in the bolded copy what is it you want to replace them with?

Not knowing the number of commas I might choose to use Quotes to ID the copy with a grep string like

find:

(?<=ArtToCanvas,)(.+)(?=,ArtToCanvas,prints,)

replace:

"\1"

Parts explained **Look ahead and look behind are not found and therefor are not captured.

(?<=ArtToCanvas,) Positive look behind

(.+) Captured copy

(?=,ArtToCanvas,prints,) Positive look ahead

This will put Quotes around your copy at which point you would be able to replace just the commas between the quotes or import into Excel or most other apps as the structure should be correct for dealing with the commas in the string.

Hope that helps

-George

[Aethon]

That is simple and clever.  Thanks man!

-George

[John Springer]

Here's another way to deal with it.  You had 11 columns all together.  The first 3 are comma-delimited.  So are the last 7.  Column four is the one that may have additional commas in it.  You can restore the columns like this:

Find:

^(.*?),(.*?),(.*?),(.*),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?)$

3 fields separated by commas, another field that can be any length, followed by 7 fields separated by commas.  The fourth column, commas and all, will end up in the 4th match field.

Replace:

Replace each field.  I used tabs, and also surrounded col 4 with pipes to so I could see that commas were captured.  

\1\t\2\t\3\t|\4|\t\5\t\6t\7t\8t\9t\10t\11

[Kerri Hicks]

You've gotten some good answers about how to do precisely what you say you want to do, and that's great.

I'm curious, though, if you might want something different. When you're working with a CSV, enclosing a field in double-quotation marks preserves/respects the commas within that field. If you remove the commas completely, or replace the commas with something else, as you've requested, it will degrade the quality of your data. (That might not matter to you in this case.)

So instead of finding everything between "ArtToCanvas" and "ArtToCanvas,prints" and removing the commas from it, I'd find the first 

ArtToCanvas, 

and replace it with 

ArtToCanvas,"

and then find the second

,ArtToCanvas

and replace it with 

",ArtToCanvas

Hope this helps.

--Kerri

--
This is the BBEdit Talk public discussion group. If you have a
feature request or need technical support, please email
"sup...@barebones.com" rather than posting to the group.
Follow @bbedit on Twitter: <https://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+un...@googlegroups.com.
To post to this group, send email to bbe...@googlegroups.com.
Visit this group at https://groups.google.com/group/bbedit.
To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/e548236e-c87a-4c5c-9544-7c46b8588ada%40googlegroups.com.