CODEWITHSUNDEEP
regexcsvtextmate
holyredbeard
holyredbeard

Reputation: 21218

Replacing data in CSV file with Regex

I have a CSV file (exported data from iWork Numbers) which contains of a list of users with information. What I want to do is to replace ;;;;;;;;; with ; on all lines accept "Last login".

By doing so and importing the file to Numbers again the data will (hopefully) be divided in rows like this:

User 1 | Points: 1 | Registered: 2012-01-01 | Last login 2012-02-02
User 2 | Points: 2 | Registered: 2012-01-01 | Last login 2012-02-02

How the CSV file looks:

;User1;;;;;;;;;
;Points: 1;;;;;;;;;
;Registered: 2012-01-01;;;;;;;;;
;Last login: 2012-02-02;;;;;;;;;
;User2;;;;;;;;;
;Points: 2;;;;;;;;;
;Registered: 2012-01-01;;;;;;;;;
;Last login: 2012-02-02;;;;;;;;;

So my question is what Regex code should I type in the Find and Replace fields?

Thanks in advance!

Upvotes: 1

Views: 2286

Answers (1)

mmdemirbas
mmdemirbas

Reputation: 9158

See the regex in action:

Find   : ^(;(?!Last).*)(;{9})
Replace: $1;

Output will be:

;User1;
;Points: 1;
;Registered: 2012-01-01;
;Last login: 2012-02-02;;;;;;;;;
;User2;
;Points: 2;
;Registered: 2012-01-01;
;Last login: 2012-02-02;;;;;;;;;

Explanation

Find:

^                 # Match start of the line
(                 # Start of the 1st capture group
    ;(?!Last)     # Match a semicolon (;), only if not followed by 'Last' word.
    .*            # Match everything
)                 # End of the 1st capture group
(                 # Start of the 2nd capture group
    ;{9}          # Match exactly 9 semicolons
)                 # End of the 2nd capture group

Replace:

$1;               # Leave 1st capture group as is and append a semicolon.

Upvotes: 2

Related Questions