Forum > Beginners

How would I Identify and merge Transaction Date/Description Transaction Detls

(1/2) > >>

BlueDragon:
I have imported a Bank statement text file into a Tstringlist where transaction records  have been split across two lines. Date/Description and Transaction.

Eg. Text File records:
11 Aug GARDENFRESH BOORAGOON WA AUS
31.95  $                  $81.10

I want :
11 Aug GARDENFRESH BOORAGOON WA AUS                       31.95  $                  $81.10


The first and all other ODD numbered lines Start with a Date: dd mmm (7 characters- eg 01 Jan ) followed by a transaction description (upto 50 Characters)
11 Aug GARDENFRESH BOORAGOON WA AUS

The Second and all EVEN numbered records indicates ONE Type of transaction :  Debit, Credit or Balance fields.

DEBIT :    nnnn.nn $               $ nnnn.nn  ,  Two $ characters padded with blanks :  numeric  Dollar values followed by $ Terminator , the blank Credit Field,Numeric balance
CREDIT :                $ nnnn.nn $               ,  Two $ characters:  First Start Character $  followed by  numeric  Dollar values has a $ terminator indicating Start of Balance Field.
BALANCE:                                $ nnnn.nn ,  One $ terminates the Credit Field, starts the Balance Feld




Thank you  :)

MarkMLl:
Use a while to process the stringlist, over i := 0..Count-1.

On each iteration, if is identified as the first line of a pair and [i+1] as the second then merge the second into the first and delete line [i +1] and increment i, otherwise handle as an error.

A for loop won't work if you're processing the text in place, since your termination condition isn't constant. A while will work because the count decreases each time you merge lines.

MarkMLl

howardpc:
If you don't understand how to implement Mark's suggestion, attach two files to your post with a few typical lines.
One file with typical raw original text data. The other file the desired processed data based on the first file.
It is not clear to me what your original data contains or precisely what your "merge" needs to accomplish, and what, if anything, the output will discard.

MarkMLl:

--- Quote from: howardpc on March 28, 2022, 11:51:28 am ---If you don't understand how to implement Mark's suggestion, attach two files to your post with a few typical lines.
One file with typical raw original text data. The other file the desired processed data based on the first file.
It is not clear to me what your original data contains or precisely what your "merge" needs to accomplish, and what, if anything, the output will discard.

--- End quote ---

Also it's considered good form to attach your attempted solution. Please note the Wiki article which describes how to tag code so that it appears sensibly.

I think his attempt at posting example data fell foul of forum formatting. Again, the link below is relevant.

https://wiki.freepascal.org/Forum

MarkMLl

BlueDragon:
Sorry I don't think I provided sufficient details about my pre-processing of the semi-structured text file  with 2 Stringlists
StrListInDataFile: Determined Number of Lines with Count_InputLines := StrListInDataFile.Count;
The Processed records were assigned to StrListOutPutDatafile , then Saved to disk -   Outputfile.txt 
StrListOutDataFile: Saved OUTPUTxtFile with transactions Formated as Described

I imported InputDataFile.txt to StrListInDataFile and processed with FOR LOOP removing spurious character sub-strings and unwanted information.
I am trying to differentiate between the Date/description and Transaction lines:
 
Date/description: Characters 1-2 are numbers, 4-6 are month names( Jan - Dec) - looping through a predefined array. (No '$' Characters)
Transactions Debit/Credit/Balance: Locate and Count $ characters, determine if numeric values exist at predefined Character Locations eg Debit 1-7; Credit: 10-14; balance: 26 -34.

Using a  for loop something like:  for i := Count_Lines - 1 DownTo 0 Do

My intention is to Identify the Date format  and Description with something like :
  StrTransactionLine[1] :=StrTransactionLine + StrTransactionLine[i+1];
  StrListOutDataFile.Delete(i); 

eg Debit Transaction.
11 Aug GARDENFRESH BOORAGOON WA AUS
31.95  $                  $81.10
nnnn.nn $               $ nnnn.nn

first(ODD-Line)  : Identify the start of a transaction with the Date Format "dd mmm ' using  by identifying the day with isNumber(LeftStr(StrInputLn,2)) and locating the Month by looping through an array of Months (Jan .. DEC)
The Second(EVEN-Line):
With a for loop,Identify and Count the '$" characters with iPos:= Pos('$',StrInputLn) , delete('$',iPos,1) the first '$' character then search for the second.
With the IsNumber() fuction Identify the numeric characters for DEBIT, CREDIT and BALANCE from their predefined format start  Locations eg Debit IPos= 1; Credit- IPos2= 10, Balance IPos3 = 26.

The isNumber Function Produced an error: identifier not found 'IsNumber'
Locating the Two $ character was successful.
Thanking you once again

I hope this is clearer

Navigation

[0] Message Index

[#] Next page

Go to full version