CODEWITHSUNDEEP
csvreplace
big_smile
big_smile

Reputation: 1523

Delete all characters before and after quotation marks

I have a CSV file, which has two columns and 4500 rows. In one column, I have several phrases that are surrounded in quotation marks. I need to delete all the text that comes before and after the quotations marks.

For example: 

How would you say "Hello, my Friend" when speaking outside?
should become "Hello, my Friend"

I also have several rows that have the word NULL in the second column. I need these rows deleted in full.

What's the best way of doing something like this? I have been looking at regular expressions, but I'm not sure if they are flexible enough to do what I want to do, or how you would use them on a CSV file (I need the table structure to remain).

EDIT: 1) At the moment I am just using Apple Numbers, but I know that wont don't it, so I am happy to any suggestions. It must support Kanji characters.

2) I have removed all the NULL rows, so that is no longer needed (I simply added a column of numbers, sorted the table so all the NULLs were together, deleted them and the sorted back by the column of numbers).

Upvotes: 0

Views: 424

Answers (1)

shawnt00
shawnt00

Reputation: 17953

Find a text editor that supports regular expression search and replace.

Something like this would match ,NULL in the second column: ^.*,NULL.*$. Replace it with "DELETEMEDELETEME" to mark the line, or as an empty string or find a way to have it match on `\n' or '\r' to catch the line break and remove the entire line completely.

Stripping out parts of the quoted string might work like this:

^(.*,){n}(.*)(\".\")(.*)(,.*)$ replaced with \1\3\5 where n is the number of columns preceding the one you want to edit. Repeat (.*,) if that's not available. It will depend on the regex flavor of your tool.

Upvotes: 1

Related Questions