Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   [SOLVED] Help with text file trim and create 2 tables (https://www.access-programmers.co.uk/forums/showthread.php?t=300423)

MajP 07-13-2018 07:30 AM

Re: Help with text file trim and create 2 tables
 
Code:

SEGMENT : CLASS1                                            Print Date 04-07-2018 12:40:37    Page      6 of 2000
 MOM ID: 1100      PQR - Test25
 Department Name: DEFAULT NAME1                                          CAT                  ABR120

---------------------------------------------------------------------------------------------------------------------------------
 1                  10          1,000.00  Rej Reason : Sxxxxxxx Ixxxxxx
                                                          No Exceptions or Warnings encountered
                                          InstXXXXX  Rejected
---------------------------------------------------------------------------------------------------------------------------------
 Total Amount:          2,000.00
 Other Reference Details:                                Axxxxxxx Bxxxxx        Hold Fxxxxx      Unxxxxxx          CA Lxxxx
---------------------------------------------------------------------------------------------------------------------------------
 1234567890123      INR PQR                              1,000.00                0.00              0.00                0.00
---------------------------------------------------------------------------------------------------------------------------------

This one is worse, and not sure if there is a way around this. This one does not have the header for S_No, Instrument, and amount. What is actually going on with the report here? Is this just a cut and paste error. Looks like a whole section is missing. Does this happen in other places? This would get extremely hard to apply any logic to extract those elements.

MajP 07-13-2018 08:13 AM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
Try this version. Should be a little more robust. Clear out the debug window before running, and post back any results.

MajP 07-13-2018 09:02 AM

Re: Help with text file trim and create 2 tables
 
If you cannot download. Here is the code. Should be able to just replace the current code.
Code:

Public Function GetFile() As String
  ' Set up the File Dialog.
  Dim fdialog As FileDialog
  Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
  With fdialog
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .Title = "Please select a file"
      ' Clear out the current filters, and add your own.
      .Filters.Clear
      .Filters.Add "Text File", "*.txt"
      '.Filters.Add "All Files", "*.*"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        'Loop through each file selected and add it to our list box.
        If fdialog.SelectedItems(1) <> vbNullString Then
          GetFile = fdialog.SelectedItems(1)
        End If
      Else
        MsgBox "You clicked Cancel in the file dialog box."
      End If
  End With
End Function
Public Sub ReadLineByLine(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim StrIn As String
  Dim TheSegment As String
  Dim ThePrintDate As String
  Dim ThePage As String
  Dim TheMOM_ID As String
  Dim TheMOM_Txt As String
  Dim TheMOM_MMC As String
  Dim TheDepartment As String
  Dim TheReferenceNo As String
  Dim TheReferenceName As String
  Dim TheS_No As String
  Dim TheInstrument As String
  Dim TheAmount As String
  Dim TheReject As String
  Dim TheWarning As String
  Dim TheException As String
  Dim TheError As String
  Dim InstRejected As Boolean
  Dim NoExceptions As Boolean
  Dim TempArray() As String
  Dim FirstRecord As Boolean
 
  intFile = FreeFile()
  Open strFile For Input As #intFile
 
  Do While Not EOF(intFile)
    Line Input #intFile, StrIn
    If InStr(StrIn, "SEGMENT :") > 0 Then
      'Debug.Print strIn
      TheSegment = FindAfter(StrIn, "SEGMENT :")
      'Debug.Print TheSegment
      ThePrintDate = FindAfter(StrIn, "Print Date ")
      'Debug.Print ThePrintDate
      ThePage = FindAfter(StrIn, "Page")
      'Debug.Print ThePage
      'Next line is MOM ID
      Line Input #intFile, StrIn
      'debug.print StrIn
      TheMOM_ID = FindAfter(StrIn, "MOM ID:")
      TheMOM_Txt = FindAfter(StrIn, "MOM ID: " & TheMOM_ID)
      ' Debug.Print "Mom_ID " & TheMOM_ID
      ' The next line is Department Name
      Line Input #intFile, StrIn
      'Debug.Print strIn
      TheDepartment = FindAfter(StrIn, "Department Name:")
      TheMOM_MMC = FindAfter(StrIn, "Department Name: " & TheDepartment)
      'Debug.Print TheDepartment
      InsertSegment TheSegment, ThePrintDate, ThePage, TheMOM_ID, TheMOM_Txt, TheMOM_MMC, TheDepartment
    ElseIf InStr(StrIn, "Reference:") > 0 And InStr(StrIn, "Reference Name:") > 0 Then
      TheReferenceNo = FindAfter(StrIn, "Reference:")
      'Debug.Print "ref No: " & TheReferenceNo
      TheReferenceName = FindAfter(StrIn, "Reference Name:")
      'Debug.Print "Ref Name " & TheReferenceName
    End If
    If InStr(StrIn, "S.No Instrument Number  Amount") > 0 Then
      'Move two lines down
      Line Input #intFile, StrIn
      FirstRecord = True
      Do
        Line Input #intFile, StrIn
        StrIn = Trim(StrIn)
        'Special code to handle the problem seen in sample error 1
        If InStr(StrIn, "Zone VALIDATION RUN") > 0 Then Exit Do
        If IsNumeric(Left(StrIn, 1)) Then
          'If the line has an S_No but not the first record than have to do an insert query because you have come to a second line
            If Not FirstRecord Then
              InsertRejection TheMOM_ID, TheReferenceNo, TheReferenceName, TheS_No, TheInstrument, TheAmount, TheReject, TheWarning, TheException, TheError, InstRejected, NoExceptions
            End If
            'this is a line with the S_No
            TempArray = GetCleanArray(StrIn)
            TheS_No = TempArray(0)
            'Debug.Print "S_No: " & TheS_No
            TheInstrument = TempArray(1)
          ' Debug.Print "Instrument " & TheInstrument
            TheAmount = TempArray(2)
            'Debug.Print "Amount " & TheAmount
        End If
        FirstRecord = False
        If InStr(StrIn, "Rej Reason :") > 0 Then TheReject = FindAfter(StrIn, "Rej Reason :")
        'Account for multiple warnings.
        If InStr(StrIn, "Warning :") > 0 Then
          If TheWarning = "" Then
            TheWarning = FindAfter(StrIn, "Warning :")
          Else
            TheWarning = TheWarning & "; " & FindAfter(StrIn, "Warning :")
          End If
        End If
        If InStr(StrIn, "Exception :") > 0 Then TheException = FindAfter(StrIn, "Exception :")
        If InStr(StrIn, "Error :") > 0 Then TheError = FindAfter(StrIn, "Error :")
        If InStr(StrIn, "Instrument Rejected") > 0 Then InstRejected = True
        If InStr(StrIn, "No Exception or Warnings encountered") > 0 Then NoExceptions = True
        'At the end of the block have to do another insert
        If Left(StrIn, 1) = "-" Then
          InsertRejection TheMOM_ID, TheReferenceNo, TheReferenceName, TheS_No, TheInstrument, TheAmount, TheReject, TheWarning, TheException, TheError, InstRejected, NoExceptions
          Exit Do
        End If
      Loop
  End If
  Loop
  Close #intFile
End Sub
Public Function FindAfter(ByVal SearchIn, SearchAfter) As String
  'This will find the text after a given search for text. The text is considered as a single item if no more than one space in the text
  'Do not know if this is any faster, probably not. Makes the code a little more reuseable
  'SEGMENT : CLASS7                                            Print Date 04-07-2018 12:40:37    Page        3 of 2000
  'If SearchAfter = "SEGMENT :" then it returns CLASS7, if "Print Date" it returns "04-07-2018 12:40:37", and "Page" returns "3 or 2000"
  FindAfter = Trim(Split(SearchIn, SearchAfter)(1))
  FindAfter = Trim(Split(FindAfter, "  ")(0))
End Function
Public Function GetCleanArray(ByVal StrIn As String) As String()
  Dim oldString As String
  'Remove three spaces and replace with two until only 2 spaces exist, then split on two spaces
  'A line like below can be put into an array of 1, 10, 2,000.00
  ' 1                10            2,000.00
  StrIn = Trim(StrIn)
  Do While StrIn <> oldString
    oldString = StrIn
    StrIn = Replace(StrIn, "  ", "  ")
  Loop
  GetCleanArray = Split(StrIn, "  ")
End Function
Public Function SqlText(TheText As String) As String
  'Prepares a string value for entry into a sql insert string
  If TheText = "" Then
    SqlText = "Null"
  Else
    TheText = Replace(Trim(TheText), "'", "''")
    SqlText = "'" & TheText & "'"
  End If
End Function
Public Function SQL_Date(strDate As String) As String
  'Turns a dd-mm-yyyy hh:mm:ss into a mm/dd/yyyy hh:mm:ss needed for a SQL insert regardless of regional settings
  '04-07-2018 becomes #07/04/2018# for the insert
  Dim aDate() As String
  If IsNull(strDate) Then
      SQL_Date = "Null"
  Else
      'Debug.Print strDate
      aDate = Split(strDate, "-")
      SQL_Date = aDate(1) & "/" & aDate(0) & "/" & aDate(2)
      If Not IsDate(SQL_Date) Then
        Debug.Print "Bad Date: " & SQL_Date
        SQL_Date = "Null"
      Else
        SQL_Date = "#" & SQL_Date & "#"
      End If
  End If
  'Debug.Print SQL_Date
End Function
Public Function SQL_Number(varNumber As Variant) As String
      If IsNumeric(varNumber) Then
        varNumber = Replace(varNumber, ",", "")
        SQL_Number = CStr(varNumber)
      Else
        SQL_Number = "NULL"
    End If
End Function
Public Sub InsertSegment(TheSegment As String, ThePrintDate As String, ThePage As String, ByVal TheMOM As String, TheMOM_Txt As String, TheMOM_MMC As String, TheDepartment As String)
  'Put your real table name below
  On Error GoTo errlbl
  Const Table1 = "Table1"
  Dim strSql As String
  TheSegment = SqlText(TheSegment)
  ThePage = SqlText(ThePage)
  ThePrintDate = SQL_Date(ThePrintDate)
  TheMOM = SqlText(TheMOM)
  TheMOM_Txt = SqlText(TheMOM_Txt)
  TheMOM_MMC = SqlText(TheMOM_MMC)
  TheDepartment = SqlText(TheDepartment)
  strSql = "Insert Into " & Table1 & "(Segment, Print_Date, Page, MOM_ID, MOM_TXT, MOM_MMC, Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", " & TheMOM & ", " & TheMOM_Txt & ", " & TheMOM_MMC & ", " & TheDepartment & ")"
  'Debug.Print strSql
  CurrentDb.Execute strSql
  Exit Sub
errlbl:
  Debug.Print Err.Number & " " & Err.Description & " " & vbCrLf & strSql
  Resume Next
End Sub
Public Sub InsertRejection(ByVal TheMomID As String, ByVal TheReferenceNo As String, ByVal TheReferenceName As String, TheS_No As String, TheInstrument As String, TheAmount As String, TheReject As String, TheWarning As String, TheException As String, TheError As String, InstRejected As Boolean, NoExceptions As Boolean)
  'Put your real table name below
  On Error GoTo errlbl
  Const Table1 = "Table2"
  Dim strSql As String
  TheReject = SqlText(TheReject)
  TheWarning = SqlText(TheWarning)
  TheException = SqlText(TheException)
  TheError = SqlText(TheError)
  TheReferenceNo = SqlText(TheReferenceNo)
  TheMomID = SqlText(TheMomID)
  TheAmount = SQL_Number(TheAmount)
  TheS_No = SQL_Number(TheS_No)
  TheInstrument = SQL_Number(TheInstrument)
  TheReferenceName = SqlText(TheReferenceName)
  strSql = "Insert Into " & Table1 & "(MOM_ID_FK, Reference_No, Reference_Name,S_No,Instrument_Number,Amount,Reject_Reason, Warning,Exception, Error_Msg, Instrument_rejected, No_Exc_No_Warn) VALUES ( " & TheMomID & ", " & TheReferenceNo & ", " & TheReferenceName & ", " & TheS_No & ", " & TheInstrument & ", " & TheAmount & ", " & TheReject & ", " & TheWarning & ", " & TheException & ", " & TheError & ", " & InstRejected & ", " & NoExceptions & ")"
  'Debug.Print strSql
  CurrentDb.Execute strSql
  TheS_No = ""
  TheInstrument = ""
  TheAmount = ""
  TheReject = ""
  TheWarning = ""
  TheException = ""
  TheError = ""
  InstRejected = False
  NoExceptions = False
  Exit Sub
errlbl:
  Debug.Print Err.Number & " " & Err.Description & " " & vbCrLf & strSql
  Resume Next
End Sub


lookforsmt 07-13-2018 01:12 PM

Re: Help with text file trim and create 2 tables
 
HI! MajP, replying to post 31. I am surprised too on the report behavior. I hope there is no more surprises. I am glad you have solution for anything and everything. My laptop is connected on work network so cannot download the code. I will have to do it at work only.

lookforsmt 07-14-2018 11:30 AM

Re: Help with text file trim and create 2 tables
 
HI! MajP, i have checked the latest db posted by you in post# 32.
It has finally looped through the entire 2413 records and without any error has updated table2 with all the 440 records and 389 records in table1.

Thank you for the time and patient in giving me the solution.

If may ask one final time, if i need to add any more lines similar to below, which line do i amend.

The example:
a) is adding a new reason
b) It is the existing reason, but the line continues on the second line.


PHP Code:

aAlready Carved:    Instant XXX granted for Insxxxxxxx

b
Exception :  Rexxx Axx DxxxxxCxxxxxxx                                  
                Axxxxxx Cxxxxx 

Thanks for all the support once again

MajP 07-14-2018 12:17 PM

Re: Help with text file trim and create 2 tables
 
Quote:

if i need to add any more lines similar to below, which line do i amend.
This is why I suggested that these "reasons" be put in a single table as stored below. Then a single line of code would suffice to update additional reaons.

Code:

ReasonType      ReasonDescription RunID
Exception          xxxxxxx                            3
Rej Reason        y1234                                3
Warning            aba                                    3
Already Carved  Instant XXX granted for Insxxxxxxx  3

The reasons would relate back to where they came from. I am not sure what that block of information is called. I called them runs. But it is that block of stuff that has an S_no, and instrument number, and amount. Example of multiple reasons relating back
Code:

1                10            2,000.00    Error :      C. I. DR TRAN TO DORMANT
                                              Warning :    Instrument already entered through ICTM
                                              Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
                                              Instrument Rejected

If the table was done that way, adding an additional reason would be easy. Currently each reason is a field in the table, so you have to modify the table and modify the insert query every time you find a new reason. You have to be very precise to get this all correct. With the other suggested approach you could have hundreds of reason and only have to update some of the code to find the reason. If you are absolutely confident this is the only other "reason" I recommend you send me an example so I can test it. You have to update the table, code, and insert query. If you think the future could be more reasons, I recommend modify the table as I said. Then it would be a single line of code versus a table and insert query change.

The above is doable either way. For the second part "It is the existing reason, but the line continues on the second line", that gets really tough. The question is, if there is some kind of rule to apply. If someone was to read this line "Axxxxxx Cxxxxx" manually how would they know it belongs to the previous line? Is there some indicator. There are other values that do not follow a colon such as "Instrument Rejected".

MajP 07-14-2018 12:35 PM

Re: Help with text file trim and create 2 tables
 
Out of curiosity is
Code:

Already Carved:
Really
Code:

Already Carved :
:
all of the other reasons have a space before the colon.
Exception :
Rej reason :
Warning :

lookforsmt 07-14-2018 04:18 PM

Re: Help with text file trim and create 2 tables
 
Thanks MajP,
I felt this was complete, but looking at the concerns raised there might be possibility this could happen in the future and writing the code would be never ending story.

Now i get your point which you were trying to advice me post#16. Since i was pretty sure that this would be the only reasons. But this is the only file which i have checked, but is was told yesterday only that there are on daily basis 4 files generated which i am sure there would be more of these new reasons somewhere that could appear.

I really don't want to barge you with my problem. But if you think that you have the time then i would really appreciate your help on this pls. This way i would kill the issue once for all if i would get any of this additional reasons in future.

But just thinking how would it be done. Below is my understanding.
PHP Code:

MessageType         MessageDetail                            RunID_FK

 Warning 
:          Memo Pxx Exxxx                           1
 Exception 
:        Ixxxxxxxxxxx Axxxx BAL EXCP              2
 Reject             Instrument Rejected                      3 
*(This starts after a space of 45 characters on the next line below Exception :)
 
Warning :          Rxxxx Axx Dxxxxx Cxxxxxxx              4
 Reject             Insturment Rejected                      5 
*(This starts after a space of 45 characters on the next line below Exception :)
 
Error              CIDR TRAN TO DORMANT                 6 
 Warning 
:          Instrument already entered through ICTM  7
 Rej Reason 
:       Pxxxxxxx Rxxxxxx                         8
 Rej Reason 
:       Sxxxxxxx Ixxxxxx                         9
 Blank1             No Exception 
or Warnings encountered     10 

Kindly note above reasons are independent on their own and can be combination of either Warning or Reject or Error or any of the combinations as given in the earlier txt files attached.
I am not sure what would be your approach. May be it would be simple logic, but i am guessing this will be a tough one.

MajP 07-14-2018 06:20 PM

Re: Help with text file trim and create 2 tables
 
Quote:

I really don't want to barge you with my problem. But if you think that you have the time then i would really appreciate your help on this pls
No problem. Some people like doing crossword puzzles, some like Soduko, and some enjoy video games. I like solving harder Access problems. The way I have become one of the top Access experts is by answering threads like this. I have an engineering and mathematical modeling background so I like the database problems that are dealing with processes more than the administration and finances.
I write well designed code. It is done in a way that it is flexible, easily modified, and easily reused. If you look at the separate functions they are all generic and can be reused. In fact most of this code is reused from somewhere else. That said, to modify this is not hard.
You have these blocks of data

Code:

1                  10          1,000.00  Warning :    MXXX PXX EXISTS
                                          Exception :  IXXXXXXXXXX AXXXXX BAL EXCP
                                          Instrument Rejected

 1                  50          1,000.00  Exception :  IXXXXXXXXXX AXXXXX BAL EXCP
                                          Instrument Rejected

Looking at this there are 2 blocks of information. Without knowing your process I called a block a "Run". Do you have better name for it?
The first "run" is 1, 10, 1000 and its reasons
The second "run" is 1, 50, 1000 and its reasons

Each "run" has multiple "Reasons". These Reasons relate to the first "run"
Code:

Warning :    MXXX PXX EXISTS
Exception :  IXXXXXXXXXX AXXXXX BAL EXCP
Instrument Rejected

I would put each "reason" in its own table and relate it back to the "run". Unfortunately, a "run" does not seem to have a unique id. But these two runs occur under
Reference: 1234567890123 Reference Name: PQR
If reference numbers do not repeat then that makes a good natural key. The first "run" 1, 10, 1000 would get a primary key of 1234567890123PQR_1 and the second run would get the PK 1234567890123PQR_2. All of the reasons would get this run ID as a foreign key to relate back to the run. I will make a demo. If that works I will modify the code one final time so that all of the possible reason types ("rej reason:, Exception:, Warning:....) can be added in a single line of code. Then it will loop the reason types. This will simplify maintenance of the code.

lookforsmt 07-14-2018 06:49 PM

Re: Help with text file trim and create 2 tables
 
HI! MajP,
Nice knowing you, I wish i could one day write such codes on my own. It would require a lot of dedication and willingness.
Hope with your codes and explanation i will try to understand and apply in my future projects of similar nature.
Run would be a good name.
PHP Code:

Looking at this there are 2 blocks of informationWithout knowing your process I called a block a "Run". Do you have better name for it
The first "run" is 1101000 and its reasons
The second 
"run" is 1501000 and its reasons 

1,10,1000 or 1,50,1000 can be anything.

PHP Code:

I would put each "reason" in its own table and relate it back to the "run"Unfortunately"run" does not seem to have a unique idBut these two runs occur under
Reference
1234567890123 Reference NamePQR
If reference numbers do not repeat then that makes a good natural keyThe first "run" 1101000 would get a primary key of 1234567890123PQR_1 and the second run would get the PK 1234567890123PQR_2All of the reasons would get this run ID as a foreign key to relate back to the run

There will be repetitive Reference & Reference Name. in the data.
Each of them can have anything from
PHP Code:

1101000 denotes

1 as running number starting with 1, 2, 3....
10 is the reference nos which can be anything from 1 to 999999 and
1000 is the amount again can be anything.

Will the above information help you in building the code

MajP 07-14-2018 09:46 PM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
This version has a report to show you how it all relates. The report tries to look like the original report.

lookforsmt 07-15-2018 10:09 AM

Re: Help with text file trim and create 2 tables
 
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 07-15-2018 10:43 AM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
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
Code:

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.
Code:

Exception :  Rexxx Axx Dxxxxx- Cxxxxxxx                                 
                      Axxxxxx Cxxxxx

It will enter that second line by itself.

lookforsmt 07-16-2018 10:12 AM

Re: Help with text file trim and create 2 tables
 
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 07-16-2018 10:22 AM

Re: Help with text file trim and create 2 tables
 
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.


All times are GMT -8. The time now is 02:48 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World