GREP pattern for financial summary output

[Russell B]

My bank accounts have a save to CSV option in a single Amount column, such as the following:

-2559.82
1168.41
-12
-7.21
23

I want to divide this into separate Credit & Debit columns, tab separated, such as

Credit | Debit

1168.41 | 2559.82

I can't create a robust GREP pattern to handle the existence or not of the decimal values. Can someone provide a suitable pattern?

Thanks

[eu...@gmx.de]

Hi Russell,

I’m not sure what you exactly want to do, but if you just want to put the negative lines into a second column in the CSV (comma separated values) file and you have just one column in the origin file, you can use the following pattern:

Search pattern:           "^(\-{1})([0-9]{1,20}\.{0,1}[0-9]{0,2})“

Replacement pattern: ",\1\2“

(without quotation marks)

(If you want to have tabs instead of commas to separate the columns use "\t“ instead of “,“)

All the negative values will be placed now in a second column, but always in a separate line.

The output will look like this:

,-2559.82

1168.41

,-12

,-7.21

23

Hope it helps.

Ulrich

--
This is the BBEdit Talk public discussion group. If you have a feature request or need technical support, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Twitter: <https://twitter.com/bbedit>
---
You received this message because you are subscribed to the Google Groups "BBEdit Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bbedit+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/783875e5-c1a0-438c-8071-678d598b71a1n%40googlegroups.com.

[Russell B]

That's perfect Ulrich. Here's me thinking I could do it with a simple string. I always forget the curly bracket option as I don't think it was an option in BBE way back when I first started, so not front of mind. I only dabble in this stuff every now and then. Here is what I did - created an extra column to push all the other columns back then inserted the debits into that column. BTW - if I wanted to create a string that said "find all strings that are not negative values, loosely (!-)", what does BBE use for 'not' or inversion I think its called? Just for future reference.

Update top of file

from

Date    Amount    Description

to

Date    Credit    Debt    Description

First pass - add column to right credits

\t([0-9]{1,20}\.{0,1}[0-9]{0,2})\t

\t\1\t\t

Second pass - push debits into new column

\t-([0-9]{1,20}\.{0,1}[0-9]{0,2})\t

\t\t\1\t

[eu...@gmx.de]

Only few people use the grep search+replace functions in BBEdit that often to use it blindly.

And the PCRE syntax isn’t very intuitive.

I developed a CMS software in the early 2k years (20 years ago ;-) using PHP and implemented extended input text filtering by using the PCRE compatible functions in PHP.

I really struggled with it, I can tell you!

Since BBEdit 13,0 I think, there is a nice support option to edit and try regex patterns in BBEdit, called "Pattern Playground“, there you get all the help you need for all the rarely used features of the grep-search. You can find it in the menu „Search“ on the 6th position from top.

Ulrich

To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/4c9d20ec-f4f4-4857-9865-9e2a9baba42dn%40googlegroups.com.