How to format csv with regex

Question

I have a csv that looks like this:

1,wer123,name,address1,age,2,cde789,name,address1,address2,age,3,529owq,name,address1,age

and so on.

I want this csv to be formatted as

1, wer123, name, address1, age
2, cde789, name, address1 address2, age
3, 529owq, name, address1, age

This is scrapped data where address field sometimes have two lines . I tried to find the fifth coma using 5f, and then r<C-r> but it caused problem where address have two values.

The logic is to find the fifth coma, check whether the next field is a number and insert a new line . If it is not a number then delete the previous coma and find the next number and insert a new line.

If this procedure is correct is it possible to do this using vim or should I go for python ? If it is possible then how to do it ? The csv is a big file with some 5000+ values.

Edit 1

All the columns other than the first two ie; name, address and age are of utf-8 format . So here age is not a number . Sorry that i forgot to mention that earlier .

Answer 1

This solution assumes there are no commas except for the column separators (i.e. no commas in quotes or escaped with a \ or anything like that). It also assumes that the column after the row number will never be only digits. If that can't be guaranteed, then I think things become significantly more complicated.

If my assumptions were acceptable then you should be able to do this in three steps:

:s/\d\+\%(,[^,]*\)\{4}\%(,[^,]*\)\?\zs,\ze\d\+,/\r/g
:%s/\%([^,]*,\)\{3}[^,]*\zs,\ze[^,]*,[^,]/ /
:%s/,\zs\ze/ /g

First, split each record into separate rows:

:s/\d\+\%(,[^,]*\)\{4}\%(,[^,]*\)\?\zs,\ze\d\+,/\r/g

Explanation:

• :s/ - Start the substitution command. I'm assuming everything currently exists on one line. If there are multiple lines add a % before the s.
• \d\+ - Find one or more digits. This finds the row number of the record.
• \%( - Group the following characters until the \)
• , - Find a comma.
• [^,]* - Find as many non-comma characters as possible.
• \) - End the group of characters started by \%(.
• \{4} - Find exactly 4 matches of the group contained by the \%( and \). This finds the column after the row number, the name, address 1 and either address 2 or the age (depending on whether address 2 is present).
• \%(,[^,]*\)\? - Create another group of a comma followed by as many non-comma characters as possible. This time, however, the match is optional because of the \?. This matches age if the previous group had address 2. If there was no address 2, then this matches nothing (this is because of the \d\+, that appears a bit later).
• \zs,\ze - Find a comma. The \zs and \ze sets the start and end range that will be replaced. This basically means that only this comma will be replaced with the replacement pattern (the \r that appears later).
• \d\+, - Find another set of digits followed by a comma. This finds the row number of the next record.
• / - Separator between the search pattern and the replacement pattern,
• \r - Replace the comma that was between the \zs and \ze in the search pattern with a new line character.
• /g - Specifies that all occurrences of the search pattern on the line should be replaced.

This should work on the example you provided as well as a few others that I tried. I can't promise that it will work in all cases, though.

Next, get rid of the comma between the address 1 and address 2 columns:

:%s/\%([^,]*,\)\{3}[^,]*\zs,\ze[^,]*,[^,]/ /

Explanation:

• :%s/ - Start the substitution command, but this time perform it on all lines in the file.
• \%([^,]*,\)\{3} - Create a group of as many non-comma characters as possible followed by a comma and match exactly 3 of these groups. This finds the row number, the next column and the name.
• [^,]* - Find as many non-comma characters as possible. This finds address 1.
• \zs,\ze - Set the replacement range around the comma after address 1 (the one we want to get rid of).
• [^,]*,[^,] - Find as many non-comma characters followed by a comma followed by more non-comma characters. This matches address 2 and age (and the comma separating them).
• / / - Separator between the search pattern and the replacement pattern followed by a space (the space is what will replace the comma between \zs and \ze) followed by another / to end the command. The last / isn't strictly necessary, but I added it to make it clear that there is a space in there. Also, the g isn't necessary like in the first step since we are only looking to replace the first occurrence of the search pattern (there would only ever be one one each line in this case anyway).

Finally, add a space after the remaining commas to make it look pretty:

:%s/,\zs\ze/ /g

Explanation:

• :%s/ - Start the substitution command on the whole file again.
• , - Find a comma.
• \zs\ze - Set the start and end of the replacement range. Since there is nothing between them, it will basically insert the replacement pattern here.
• / /g - Basically the same thing as in the second step. This time the g is necessary because we want to perform the replacement for every comma on each line.