Help with text file trim and create 2 tables (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
HI!

PHP:
Can all of the cases be duplicated? (Rej Reason, Exception, Error, Warning ?)

I checked the entire data and did not find duplicates other than "warning"
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
I will post an update to the database. I did some code clean up, which will make it a little easier to follow. This is the first time trying to extract data from a text file so there may be some smarter ways if anyone has recommendations.

Here is the main procedure which just reads a file line by line looking for key lines. Once it finds the line it does standard string manipulation to get the data elements. It calls the helper functions to aid in getting the data elements. Once it gets the correct data it calls insert the functions that make the insert queries.
Code:
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)
       TheMOM_MMC = Trim(Split(TheMOM_Txt, "-")(0))
      ' Debug.Print "Mom " & TheMOM_MMC
      ' The next line is Department Name
       Line Input #intFile, StrIn
      'Debug.Print strIn
      TheDepartment = FindAfter(StrIn, "Department Name:")
      'Debug.Print TheDepartment
      InsertSegment TheSegment, ThePrintDate, ThePage, TheMOM_ID, TheMOM_Txt, TheMOM_MMC, TheDepartment
    End If
    If 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)
         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

These are the helper functions that reduce repetitive tasks on string an array manipulations.
Code:
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
    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
   Debug.Print strDate
   aDate = Split(strDate, "-")
   SQL_Date = "#" & aDate(1) & "/" & aDate(0) & "/" & aDate(2) & "#"
   'Debug.Print SQL_Date
End Function

This is the code for the two insert queries. The code uses the helper functions to format the strings and date.

Code:
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
  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
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
  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 = Replace(TheAmount, ",", "")
  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
End Sub
 

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
Hi! Majp,

i tested the db with higher data and encountered the below error. I was trying to find out the mis-match which could help to reslove this issue:

PHP:
Runtime error 3134
Syntax error in Insert into statement

It highlighted on "CurrentDB.Execute strSql"

table1 imported 379 of 2413 lines. Thats because MOM_ID is AutoNumber. But this is fine. I am checking if it has missed any MOM_ID.

table2: 1st data available on page 431 and imported data till page 1067. Total 149 records imported between these (431 to 1067)pages, whereas there are more than 180+ records. I was trying to figure out from the data but unable to identify.

There are records on page 1068, but it did not import records after this page.

Any particular reason
 

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
Thank you for the coding and explanation in post#23. I am checking why the data after page 1068 is not getting picked up till the end
 

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
Further observations:
table1: field MOM_ID is split into two fields (MOM_ID & MOM_TXT) whereas MOM_MMC is showing same details as MOM_TXT. KInldy note it should populate data after "Department Name" which is 3 length character based in the center. (Kindly refer to the earlier image posted in post#18.
Although your db uploaded is showing a pefect details. I may have given dummy names so changing them to the actual original names matching witht the notepad report.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
With the MOM_MMC, I may have cheated based on the example. In the example all MOM_Txt has a "-" in the name. My code was based on that, and will fail if not.
I get this
Code:
MOM_TXT	MOM_MMC
ABC - Test1	    ABC
PQR - Test25	    PQR
MNO - Test50	    MNO
PQR - Test25	    PQR
MNO - Test50	    MNO
PQR - Test25	   PQR

I split the MOM_ID based on "-". But if there is no "-" then it will not work. I will modify the code to look instead after the department name.
I will send a new database with error checking in it. When the code fails it then will print to the immediate window the Sql string. I will need you to send it back to me so I can see what it does not like.

There are records on page 1068, but it did not import records after this page.
Any particular reason
Once it failed on the error, it does not go any further. I will put in a resume next so it continues on. However, we will then have to look at all of the errors in the debug window to see what was missed. That is the reason I did not put in error checking, because want to understand where it fails and not keep going.
 

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
Sure MajP, i will do. Now i understand the logic why you had put the check if it fails. However, i have to admit it will be long way for me to understand the basic and what the code does. You have explained until now step by step and above all you have great sense of patient with novice like me.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
I also made the assumption that S_No, Instrument Number where real numbers and Amount was a currency. If you do not need these stored that way it will be easier to store them as strings. That way if there are exceptions such as sometimes the values are not real numbers (ex. "NA") then they will still get inserted. Howerver, if you need to do real number stuff like sum the amount or sort the S_No then you want them stored as real numbers. An example of a number not really a number is a Serial number, phone number, social security number. Just because it looks like 1234789, you are never going to do math on it so better off storing as a string most of the time.
 

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
Thank you MajP, i will try the updated db and give you my feedback. I was trying to identify the error that came on the previous db. I came across below data in the text file which appears to be inconsistent due to which it gives an error. I changed it to how it should look and it went on smoothly till the end without giving error.

I am posting the snap shot of the same text which were in 3 different ways. The below text is placed inconsistent on 4 places and 3 of them were different

PHP:
 S.No Instrument Number  Amount               Error Description                    |

I think it looks for the below text after it reads the above text and if it does not find then gives an error. Is it possible to read the below independently. But i need to check if that data is also consistent across the text file.

PHP:
---------------------------------------------------------------------------------------------------------------------------------
 1                 10            2,000.00     Warning :

Unfortunate, i will not be able to try your new DB as i am at home till Sunday and i cannot download your updated db on my office laptop, but i will try to go to office tomorrow to download the file. I have tried on my home laptop on the earlier replicated text file and it works fine.

I tried on the attached snapshot scenarios on new db and it gave me the below error. As you mentioned i checked the immediate window and got the below error
PHP:
3134 Syntax error in INSERT INTO statement. 
Insert Into Table2(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 ( Null, '1234567890999', 'ABC Co Ltd', , , , Null, Null, Null, Null, False, False)
3134 Syntax error in INSERT INTO statement. 
Insert Into Table2(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 ( Null, '1234567890567', 'HAT', , , , Null, Null, Null, Null, False, False)

I have seen you have seprated SOL_ID; SOL_TXT & SOL_MMC in different field.

PHP:
I also made the assumption that S_No, Instrument Number where real numbers and Amount was a currency. If you do not need these stored that way it will be easier to store them as strings. That way if there are exceptions such as sometimes the values are not real numbers (ex. "NA") then they will still get inserted. Howerver, if you need to do real number stuff like sum the amount or sort the S_No then you want them stored as real numbers. An example of a number not really a number is a Serial number, phone number, social security number. Just because it looks like 1234789, you are never going to do math on it so better off storing as a string most of the time.
I have checked this across the data. I would require the amount field to calculate the sum, it will not have NA since if there is no data it will be only the 1st part which is captured in table1.

I will try to check this by tomorrow at office and give you my feedback
 

Attachments

  • Sample_Error_1.txt
    7.3 KB · Views: 111

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
Code:
 S.No Instrument Number  Amount               Error Description                    |
---------------------------------------------------------------------------------------------------------------------------------
                                                        xxxxxxx yyy
                                                        IXXXXX CXXXXXXX Zone VALIDATION RUN
                                                    Interxxxxxxx Rxxxx List as on   04-07-2018
This looks like the report has broken in the wrong place. I would expect on the third line to see an S_No, Instrument Number and Amount, but it looks like it is starting on a new report. I have a fix that in this case you get a record but it only has the reference number, name, and the foreign key to the MOM_ID.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
Try this version. Should be a little more robust. Clear out the debug window before running, and post back any results.
 

Attachments

  • ReadTextFile_20180713.accdb
    652 KB · Views: 114

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
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
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
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

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
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:
a) Already Carved:    Instant XXX granted for Insxxxxxxx

b) Exception :  Rexxx Axx Dxxxxx- Cxxxxxxx                                  
                Axxxxxx Cxxxxx

Thanks for all the support once again
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
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:
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      		C. I. DR 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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:40
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 16:40
Joined
Dec 26, 2011
Messages
672
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:
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
1,10,1000 or 1,50,1000 can be anything.

PHP:
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.
There will be repetitive Reference & Reference Name. in the data.
Each of them can have anything from
PHP:
1, 10, 1000 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
 
Last edited:

Users who are viewing this thread

Top Bottom