Help with text file trim and create 2 tables

[MajP]

This version has a report to show you how it all relates. The report tries to look like the original report.

 

[lookforsmt]

Thanks MajP for the updated version. I will test the same and let you know tomorrow after i come back from work.

Amazing work, you have changed the look of the report to pretty solid one even without asking one.
Thank you and God bless you.

 

[MajP]

Try this version as well. After changing the table structure this really simplifies things so I could change the logic.

No longer have to look for specific lines of text (i.e Warning, Exception, ...) once entering the block below

1                 10            2,000.00     Warning :    Mxxx Pxx Exxxxx
					      Warning :    Instrument already entered through ICTM
                                              Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
					      Instrument Rejected
 2		  155              500.00     Rej Reason : Pxxxxxxx Rxxxxxx
					      Warning :    Mxxx Pxx Exxxxx
                                              Exception :  Ixxxxxxxxxxx Axxxx
					      Instrument Rejected 
---------------------------------------------------------------------------------------------------------------------------------

Instead just read every line until you come to "-------" at the end of the block. If the line has a number in the first character save the S_No, Instrument, and Amount. Also check for anything following the Amount. Then on each line if there is a ":" save the ReasonType and ReasonDescription by splitting on ":". If it does not have ":" like "Instrument Rejected" just save "Instrument Rejected" in the ReasonDescription. Since you no longer have to look for specific values (Rej Reason, Warning, Etc) you no longer have to code for it and it will return any of the potential lines of text in the Error Description area. This will even sort of handle the case where it wraps.

 Exception :  Rexxx Axx Dxxxxx- Cxxxxxxx                                   
                      Axxxxxx Cxxxxx

It will enter that second line by itself.

 

[lookforsmt]

Thank you very much, i tried the latest uploaded db and it gives me perfect result as i required. I have tried in different scenarios and it gives me the result without any pause.

The report gives a dynamic look and easy to read/

My next steps is to match this data with exactly similar type data. The difference between these two data will tell me what action was taken by users.
I must admit prior to this the data was getting checked the hard way. This is huge time saving and efficient way to check the difference.

So for all the users, this is the work of MajP, who has gone out of his way to give me this solution. I want to thank him from the bottom of my heart and wish you all the very best. Keep up the good work.

I will now close the thread as Solved

 

[MajP]

Glad to hear it works. Like I said this is the best way to learn. There are definitely some good tricks I learned doing this that I would reuse if ever have to do it again.