0

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 .

  • Assuming age is really a number and it always follows a number: Can you do :s/\(\d\+\),\(\d\+\)/\1\r\2/g? – Peter Rincker Mar 8 '18 at 17:55
  • Really sorry for not making clear that other than first two columns all other are utf-8 format ie; name, address and age is in utf-8 format , ie; age is not a number here . – Anoop D Mar 9 '18 at 0:39
  • I'm still a little confused. Are you saying the age is literally the string "age" or just that it's a string representation of the age like, for example, "20" or "twenty"? – Pak Mar 9 '18 at 0:49
  • Ii IS a string ....... not just a representation . – Anoop D Mar 9 '18 at 1:16
2

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.
  • Thank you for your valuable suggestion . There is a problem while splitting records into separate rows , you have assumed that there will only be either a second address ( address 2 ) OR age ...... but as i have shown in the example it is address2 along with age so \{4} will skip the next line after one with both address2 and age. – Anoop D Mar 9 '18 at 3:46
  • It should work as is. You are correct that the \{4} only finds the first four columns after the row number, but the \%(,[^,]*\)\? finds an optional 5th column. This should match the age column in the event that there was an address 2 column for the record. I tested it with the example that you provided and everything appeared to work as expected. – Pak Mar 9 '18 at 3:51
  • Sorry , that was my fault . I had a space in between row number . I just deleted those and it works ! Thank you . – Anoop D Mar 9 '18 at 5:23
  • Glad I could help. – Pak Mar 9 '18 at 15:17

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.