11

I have a csv file with the following format:

example csv:

bear,brown,mean,large
ant,black,strong,tiny
cat,yellow,moody,small

How may I apply double quotes around every value? How may I accomplish this using regex?

I am using TextMate (text editor) to do the find/replace w/ regular expression.

HappyCoding
  • 641
  • 16
  • 36
Brad
  • 12,054
  • 44
  • 118
  • 187

3 Answers3

36

Here are the important portions of the regex. Hopefully I got it right when I converted to textmate format:

Search - ([^,]*)(,|$)

Replace - "$1"$2

Search explanation: Find every character that is not a comma, up until we reach a comma, or the end of the line. Capture the match for string to be quoted in one variable, and capture the comma/end-of-line match in another variable.

Replace explanation: The original string, quoted, and the comma or end-of-line that follows it.

Merlyn Morgan-Graham
  • 58,163
  • 16
  • 128
  • 183
  • 2
    This worked mostly in notepad++, but left off the first quote and last quote on each line. This stack overflow post fixed that right up: http://stackoverflow.com/questions/11003761/notepad-add-to-every-line – DrCord Nov 19 '13 at 22:35
  • @DrCord: Regex syntax is slightly different for nearly every implementation I've seen, particularly the escaping style. Looking over it, I don't see anything about my regex that shouldn't work with proper escaping, assuming your find/replace supports captures, negative character classes, and "global" matches (i.e. multiple matches on the same line in a single find/replace). For example, if you're using VIM, the find/replace command becomes: `:%s/\([^,]*\)\(,\|$\)/"\1"\2/g` - tested it just now, and it works with the OP's example :) Maybe you escaped your quotes or capture refs incorrectly? – Merlyn Morgan-Graham Nov 21 '13 at 01:52
  • Other deal with my regex is it won't handle already quoted things, or escaped commas. With many simple CSVs this will be enough. – Merlyn Morgan-Graham May 29 '14 at 07:02
  • Just as a note (to this old and well-handled question), you'll want to use ([^, ]*)(,|$) if the CSV has a leading space after the comma that you do not want to include in the quotations (there is a space after ^,) – Mr.Budris Nov 16 '17 at 16:27
2

You could start with:

find: ,
replace: ","

then add a " at the start and at the end?

nicomen
  • 1,183
  • 7
  • 16
  • If I'm not mistaken, I believe there is also a way to replace linebreaks in the same manner (thereby avoiding having to manually add the final quotes at start and end of each line). Although such characters may differ between text editors... In any case, just adding quotes the the start and end of document is not sufficient as that would transform the entire multi-row CSV document into a single-row CSV document. – gablin Aug 24 '10 at 19:20
  • You are correct, textmate regexps seems to support stuff like ^ and $, maybe that can be used to fix that: ^(.*)$ => "$1" – nicomen Aug 24 '10 at 20:15
0

This worked great for me. Did the find/replace for rows first Image of Regex Find/Replace

Then did the simple find / replace for , => ","

Had to add the first quote, and adding the "Markup" made it easy.

Thanks and good luck.

Rob S
  • 658
  • 1
  • 5
  • 7