1

I have a CSV file in which data in a row are present in following way:

ID, Name, State, Score, Rank

In the Name Column, data are in "some Name" form. But there are many entries in which a comma is present in the name like: "some name, Again" which makes that row inconsistent with other rows.

I have many CSV files in this format. So, how can I delete the comma character that are present in between two " from every line?

Sample Data:

ABC, "Ujjwal Gupta", XYZ, 35, 1
DEF, "Abhishek Kumar, Gupta", RST, 23, 5

As you can see 2nd line can create inconsistencies.

  • 1
    It would be helpful to include some sample data (obviously not your exact data) to show the format more visually. Please use the edit button to include this. – D. Ben Knoble Jan 22 at 20:55
  • I have edited the question. – Ujjwal Gupta Jan 22 at 21:01
  • Instead of messing with the commas inside the fields, consider switching the commas outside the fields to a character you won't see in the fields themselves, such as a TAB character. Turning this file from a CSV to a tab-separated file would be helpful to work on it inside Vim. – filbranden Jan 23 at 17:35
  • Also consider whether Vim (or a text editor) is really the right tool to manipulate a CSV file like this. Even though the structure in a CSV is fairly simple and shallow and using a text editor is a choice, it's not always the most appropriate choice... Use the right tools for the task. (Or: "If all you have is a hammer, everything starts to look like a nail.") – filbranden Jan 23 at 17:37
2

This situation is frustrating. Technically speaking, the CSV file you describe is within MicroSoft's CSV spec - the double-quotes escape the comma, so it's not considered a separator. However, there's a lot of programs out there that don't honor any method of saying the comma isn't a value separator, or don't honor that one.

If you only have a single double-quoted term, it's easier to deal with, because you can just deal with any separator between those quotes.

:%s/\("[^",]*\),\(.*"\)/\1\2/

This just looks for a double quote, followed by anything that's not a comma or a double quote, which it puts into group 1, and then a comma, followed by whatever ending with a double quote, which goes in group 2. The comma wasn't included in either group, so we just replace with the groups and the comma is gone. I'm not using the g modifier there because that case was defined to be only one double-quoted term, and since it's matching both of the double-quotes around that term, it's not going to work more than once per line. If you can have multiple double-quoted sections, it can be much harder. Unfortunately, to make sure we're not thwacking commas that really are separators, we're going to have to start each match at the beginning of the line, and this will only work for CSVs which do not have quoted line endings (another thing that's allowed and annoying in MicroSoft's spec. At least, annoying from the perspective of someone trying to parse it with simple tools.)

:%s/^\(\([^,"]*,\|"[^,"]*",\)*"[^,"]*\),\([^"]*"\)/\1\3/

Like the first one, we simply put everything we want, up to the close double quote after our unwanted character, into two groups, and concatenate them, without including what we don't want. But this time, the first group is preceded with an anchored expression that matches any number of elements that either aren't enclosed in double quotes or are enclosed in double quotes but don't contain any commas, so we're "fixing" the first value with the issue, most specifically the first comma from that value. The 'any number of values without the pattern' is handled using another group block, so instead of replacing with \1\2, we replace with \1\3.

Just to talk about that portion specifically, namely \([^,"]*,\|"[^,"]*",\)*, this includes an \| in the middle, which says that it matches if it's either the [^,"]*, on the left or the "[^,"]*", on the right. The * after the end of the group repeats the whole grouping regex as many times as is necessary.

This isn't a particularly fast regular expression, and it needs to be re-run as many times as the maximum number of double-quoted commas on one line. But it works within the context of the environment you're asking for it to. It would probably be a better answer to tell whatever program is having the problem with these CSVs how to properly handle quotes, or to turn the other commas into some character that's not already in the file, such as possibly a tab, so that the file could be parsed using that other character as a separator instead, because that's something that would be easier to do with a single pass.

I feel it's probably important to note that by making the change you're looking for to name fields, you're changing the semantic meaning of those names. Based on your username, I would guess you understand this particular thing, but this answer is also for anyone else who's looking into handling this situations, so I felt important to say that it's usually a better approach to replace the separator with a different separator, such as ;, which isn't otherwise used within the data, but could be converted back to a comma when you're only dealing with that one value later on.

| improve this answer | |
  • Thanks for the answer with a great explanation. Can you tell me any resource, link anything, from where I can learn to write this type of expressions myself?? – Ujjwal Gupta Jan 27 at 9:44
  • In vim or gvim, :help – Ed Grimm Jan 28 at 2:00
0

The vim pattern "[^"]*\zs,\ze[^"]*" should match only the comma. However, if you have data like

"abc","ab,c"

it will match both commas.

The pattern matches

  • a quote ("), followed by
  • a bunch of "not-quotes" ([^"]*), followed by
  • a comma (,), but we only keep the comma as the match (\zs,\ze), followed by
  • more "not-quotes" ([^"]*), followed by
  • a quote (")

We need the "not-quotes" to avoid something like ".*", which matches all of

"abc"def"

because . matches the " in the middle, too. We could accomplish the same with non-greedy patterns (".\{-}\zs,\ze.\{-}"), but I find those harder to read.


Assuming that the pattern works for you (test it with / and n—preferably with hlsearch set, to make sure it only matches the commas you want), you can carry out the change with

:%substitute//;/

The short form is :%s//;.

Note that I am intentionally omitting the g global flag here.

| improve this answer | |
  • Would you please give some explanation on your expression?? It would help me to understand. And in my case, there are only two occurrence of " in a line. – Ujjwal Gupta Jan 22 at 21:04
  • @UjjwalGupta good point. Done. – D. Ben Knoble Jan 22 at 22:03
  • The problem that I've personally had with this sort of regular expression is that you also catch false positives. In your example, the first comma is actually OK, because it's between one double-quoted term and another. But that comma appears to be what would be caught if you fixed your pattern, rather than the next comma which is within a single double-quoted term. – Ed Grimm Jan 23 at 5:14
  • @EdGrimm i did state the pattern is broken for that input. You cannot reliably parse CSV with regex. When you can make assumptions about the input, however, (such as here, where only one column is quoted), it’s perfectly acceptable. – D. Ben Knoble Jan 23 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.