String functions

[Wolters]

I have to trim and clean up bank statements that are copied to the Clip board, and also insert <TAB> to finally get the content to Excel.

The problem is that each line in the bank statement is of variable length, for example:

 

 

2010-01-25 Payment to such and such, ref: 3493 -3 456,00 12 345,05

2010-01-26 Giro 345-345, Bill Gates -254,50 12090,55

 

 

The first part of the line is easy, that's the date, but then comes a string with variable length. After that comes a space and then the actual amount beginning with a minus sign. The amount always contains a comma. And finally the accounts balance, which also always contains a comma.

So I guess that I will have to read each line "backwards" until I can capture the transaction amount??? However I wonder if there are any built in string functions that could help me with this? Like getting the string lenght and getting the n:th character from the right, and finding characher X in a string, etc, etc

 

As I am new to Macro Express I would be most happy if you can point me in any direction where I can find out more about how you construct macros of the above type!!!!

 

Regards,

 

 

Dag

[Yehnfikm8Gq]

Take a look at the options in Variable Set Integer (length of string, position of text) Variable Set String (from Clipboard) and Variable Modify String (copying, deleting, replacing substring). You can do all the tasks you mentioned although not necessarily directly. It sounds like you have a rough idea of what to do. Ask if you need more info.

 

PS, If you do a substring search and it is not found, the position integer variable will be 0.

[terrypin]

If you're like me, then a solid demonstration will be the most helpful. This uses the first of your specific examples. (I wasn't sure where the 'middle' finished and the amount started, so you might need to adapt a little.)

 

Also, to find the final space (the one nearest the end), I realise after seeing John's post just now that I could have done that much more elegantly using

Variable Set Integer > Get Position of Text in a Text Variable

 

Script

// Example for Dag, ME3.
// Set T1 to a fixed string
Variable Set String %T1% "2010-01-25 Payment to such and such, ref: 3493 -3 456,00 12 345,05"
// Display the original string
Text Box Display: Original string
// Get the length N1 of the string T1
Variable Set Integer %N1% from Length of Variable %T1%
// Determine the Date: easy, as it appears to be a fixed length of 10 chars.
Variable Modify String: Copy Part of %T1% to %T2%
Text Box Display: Date
// Remove the first 11 chars, to focus on the rest
Variable Modify String: Delete Part of %T1%
Text Box Display: Remainder of string
// Determine the Amount
// Find the final space, which will be the one nearest the end
// (Identifying the final comma seems unnecessary)
// T3 is the single char copied from the char in position N2 of T1
// N2 will start at the full length of the remaining string and get progressively smaller
// Until T3 is identified as a space
Variable Set Integer %N2% from Length of Variable %T1%
Repeat Until %T3% = " "
 Variable Modify String: Copy Part of %T1% to %T3%
 Variable Modify Integer: Dec (%N2%)
Repeat End
// Extract the varying middle section
Variable Modify String: Copy Part of %T1% to %T4%
Text Box Display: Middle section
// Extract the Amount
// Calculate length of Amount
Variable Set Integer %N5% from Length of Variable %T1%
Variable Set Integer %N6% from Length of Variable %T4%
Variable Modify Integer: %N5% = %N5% - %N6%
// Adjust N2 ready for final extraction
Variable Modify Integer: %N2% = %N2% + 2
Variable Modify String: Copy Part of %T1% to %T5%
// Final extraction of Amount
Text Box Display: Amount
// Assemble full result
Text Box Display: Full resut
// Save to clipboard for possible further use
Variable Set String %T5% "%T2%; %T4%; %T5%"
Variable Modify String: Save %T5% to Clipboard

 

Code

 

<REM2:Example for Dag, ME3.><REM2:Set T1 to a fixed string><TVAR2:01:01:2010-01-25 Payment to such and such, ref: 3493 -3 456,00 12 345,05><REM2:Display the original string><TBOX4:T:1:000821Center000548000200:000:Original string%T1%><REM2:Get the length N1 of the string T1><IVAR2:01:12:1><REM2:Determine the Date: easy, as it appears to be a fixed length of 10 chars.><TMVAR2:10:02:01:001:010:><TBOX4:T:1:000821Center000548000200:000:Date%T2%><REM2:Remove the first 11 chars, to focus on the rest><TMVAR2:11:01:00:001:011:><TBOX4:T:1:000821Center000548000200:000:Remainder of string%T1%><REM2:Determine the Amount><REM2:Find the final space, which will be the one nearest the end><REM2:(Identifying the final comma seems unnecessary)><REM2:T3 is the single char copied from the char in position N2 of T1><REM2:N2 will start at the full length of the remaining string and get progressively smaller><REM2:Until T3 is identified as a space><IVAR2:02:12:1><REP3:08:000001:000001:0003:0:01: ><TMVAR2:10:03:01:N02:001:><NMVAR:09:02:0:0000001:0:0000000><ENDREP><REM2:Extract the varying middle section><TMVAR2:10:04:01:001:N02:><TBOX4:T:1:000821Center000548000200:000:Middle section%T4%><REM2:Extract the Amount><REM2:Calculate length of Amount><IVAR2:05:12:1><IVAR2:06:12:4><NMVAR:02:05:1:0000005:1:0000006><REM2:Adjust N2 ready for final extraction><NMVAR:01:02:1:0000002:2:0000002><TMVAR2:10:05:01:N02:N05:><REM2:Final extraction of Amount><TBOX4:T:1:000821Center000548000200:000:Amount%T5%><REM2:Assemble full result><TBOX4:T:1:000821Center000548000200:000:Full resutDate = %T2%
Variable length middle section = %T4%
Amount = %T5%

These are also on clipboard as
Date; Middle; Amount><REM2:Save to clipboard for possible further use><TVAR2:05:01:%T2%; %T4%; %T5%><TMVAR2:16:05:00:000:000:>

 

The MEX file:

ParsingExampleDag.mex

 

--

Terry, East Grinstead, UK

[Wolters]

Wow!!!

 

Thanks a lot Terry!

 

That was really a great way to get to know Macro Express!!!

 

I will play around with your code until I get what I need!

 

Thank you once more!

 

 

Dag, Sweden