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

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Hi! all,
I have a unique problem, not sure if there is any solution for this.

I have a account statement in txt format which runs in quite number of pages, with plenty of spaces and empty rows but has a standard format across all pages. What i am looking is to get the Output result (refer attached snapshot)
The rest of the information in the txt file is not required.

Any help or suggestions to start this pls.
 

Attachments

  • Sample_text.txt
    8 KB · Views: 136
  • Output Result.png
    Output Result.png
    27 KB · Views: 180

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
Hi
It's not really a unique problem
Basically, you just need code to read selected parts of your text file.

I demonstrate one way of doing that in this sample database How to create a web version checker

In that, I first download a web page to a text file (which you can omit), search for a specified text string and read the section after that to a variable.
You just need to do that many times but the principle is the same.
In your case, you would need to update your two tables with each of the extracted values

Other people may have different solutions e.g. using the split function
 

plog

Banishment Pending
Local time
Today, 13:40
Joined
May 11, 2011
Messages
11,611
There's no solution other than choose a programming language and build your own text parser.

Google 'text parser tutorial'. Essentially you will write a program to open your source file, tell it how to identify the start of a new record, teach it how to extract out all the fields in that record and then what signifies the end of the record. You have it loop through that until it reaches the end of the file, then have it spit all the records it found out into a destination file. Then you import the destination file into your database.

Of course you have 2 tables you want to populate so that means doing the above twice.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
I played with this a little to see what it would take and if it would be overly difficult. Often this depends on how well formatted the data is. I was able to do the first part with the sample data. It is not very bullet proof so it will depend on how truly common your format is. Basically I read it line by line. Then cleaned it up to remove a lot of special characters. Then checked the beginning of the line. Then used split functions to get the values I wanted. Then passed to an insert query. Needs some work, but appears to work

Code:
Public Sub ReadLineByLine(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim strIn As String
  Dim strOut As String
   Dim strSegment As String
  Dim strMOM As String
  Dim strDepartment As String
  Dim TheSegment As String
  Dim ThePrintDate As String
  Dim ThePage As String
  Dim TheMOM As String
  Dim TheDepartment As String
  intFile = FreeFile()
  
  Open strFile For Input As #intFile
  
  Do While Not EOF(intFile)
    Line Input #intFile, strIn
    strIn = CleanField(strIn)
    If Left(strIn, Len("Segment")) = "Segment" Then
       strSegment = strIn
       'Debug.Print strSegment
       TheSegment = Trim(Split(strSegment, ":")(1))
       TheSegment = Trim(Split(TheSegment, " ")(0))
      ' Debug.Print TheSegment
       ThePrintDate = Trim(Split(strSegment, TheSegment)(1))
       ThePrintDate = Trim(Split(ThePrintDate, " ")(2))
       'Debug.Print ThePrintDate
       ThePage = Mid(strSegment, InStr(strSegment, "Page") + Len("Page"))
       ThePage = Trim(ThePage)
       'Debug.Print ThePage
    ElseIf Left(strIn, Len("MOM ID:")) = "MOM ID:" Then
      strMOM = strIn
      TheMOM = Trim(Split(strMOM, ":")(1))
      TheMOM = (Replace(TheMOM, "  ", ""))
      'Debug.Print TheMOM
    ElseIf Left(strIn, Len("Department Name:")) = "Department Name:" Then
      strDepartment = strIn
      'Debug.Print strDepartment
      TheDepartment = Trim(Split(strDepartment, ":")(1))
      TheDepartment = Split(TheDepartment, "  ")(0)
      'Debug.Print TheDepartment
    End If
    If TheSegment <> "" And ThePrintDate <> "" And ThePage <> "" And TheMOM <> "" And TheDepartment <> "" Then
      InsertRecord TheSegment, ThePrintDate, ThePage, TheMOM, TheDepartment
      TheSegment = ""
      ThePrintDate = ""
      ThePage = ""
      TheMOM = ""
      TheDepartment = ""
    End If
  Loop

  Close #intFile

End Sub
Public Sub testRead()
  ReadLineByLine "C:\Users\500989\Documents\Personal\AccessDatabasesVBA\Text Utilities\sample_text.txt"
End Sub
Public Function CleanField(strField As String) As String
   'turn spaces and nulls to empty strings
   CleanField = strField & ""
   
  ' CleanField = (Replace(strField, "  ", ""))
   'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
   'You can loop them all except 32 since that is a space which could be inside your string
   CleanField = Replace(CleanField, Chr(9), "")  'Tab
   CleanField = Replace(CleanField, Chr(10), "") 'Line Feed
   CleanField = Replace(CleanField, Chr(13), "") 'Carriage return
   CleanField = Trim(CleanField)
   'either leave anwer as "" string or change all to null
End Function
Public Sub InsertRecord(TheSegment As String, ThePrintDate As String, ThePage As String, TheMOM As String, TheDepartment As String)
  'Wrint an insert query here
  Dim strSql As String
  TheSegment = "'" & TheSegment & "'"
  ThePage = "'" & ThePage & "'"
  ThePrintDate = "'" & ThePrintDate & "'"
  '...
  strSql = "Insert Into Table1 (Segment,PrintDate,Page,MOM_ID,Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", ...)"
  Debug.Print strSql
  'currentdb.executeStrSql
End Sub
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Good morning all

Thank you for your suggestions and directions to take this forward. I did look over the net but this one is out of the world for me.

Dear Majp, i tried your code in my db, I am getting this error message:

The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

not sure what am i doing wrong.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
I doubt that has anything to do with the code. More likely something to do with the form. That error means something on the form is named twice with the same name. You may be able to go into the vba window and hit compile to find the culprit. I would put that code in a standard module not the forms module. To test it put a button on a form. The click event should be

Code:
ReadLineByLine "C:\yourPath\sample_text.txt"

Where "yourpath" is the complete pass. The code does not actually do the insert query at this time. Did not finish, since just a demo of potential approach. But it does correctly get all the values as shown in your example and prints it out in the immediate window. So at least with the sample data it is a viable solution. However, the solution is based on the format of the data so you will only know how bullet proof it is by running a bigger test. Then if it breaks on certain data you will need to add more logic.

The good news if you get this to work the second table looks as easy or easier to make. For example I read line by line and check if "Rej Reason :" is in that line. Then I take that line and can split it a couple of ways to get S.Number, Instrument Number, Amount, and the reject reason.
Code:
 1                 1              1,000.00    Rej Reason : Sxxxxxxx Ixxxxxx


I will try to put a full working demo for both tables, when I get some time. I will include the file browser so you can click a button and browse for your files. Bottom line, if the rest of the files are formatted as consistently, this should all be easy to do.
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thanks MajP for your response, yes you are right i had named the form same as he sub name so getting the error.

One thing i missed to mentioned, the text file uploaded is excatly the same as the original text file and it is pretty consistence in terms of space and format across the data.

I am never done this before therefore clueless on how to go about it. I appreciate your help in this.

I checked the immediate window and it shows the table1 result as i would like to.
 

lookforsmt

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

Besides the "Rej Reason :" I also wanted it to capture the next two lines in separate column/field

"Warning : "
"Exception :"

PHP:
1    1    1,000.00    Rej Reason : Sxxxxxxx Ixxxxxx    Warning : Mxxx Pxx Exxxxx      Exception :  Ixxxxxxxxxxx Axxxx

Will the below be a field name or part of the text
"Rej Reason :"
"Warning : "
"Exception :"

Thanks again.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
Here is a working demo. You can browse for a file then it will import into the two tables. To prove it works there is a button to clear out the tables first. It works with the demo text files, but you will have to try bigger data sets and see if it fails. Example: I did not add code if a data element is missing. If that is possible than I would have to account for that.

There is not a lot of "new" code here. I pulled the file browser code from old stuff I had and available on the internet. The code to read a text file can also be easily found on the internet. I also pulled some array code from Pearson's site.

So once you can pick a file and read it the trick is to look for something. You only have to find 2 things. "SEGMENT :" and "Reject Reason :" because once you find "Segment" you take the next 2 lines, and once you find Reject Reason you take the next three lines. You can use an "instr" function to search for these lines. Once you find the lines you want, the trick is to pull out the data elements. I used the "Split" function which lets you take a string and break it into an array based on any delimiter. A good one is ":" because the thing immediately after that is data. But I had to use several string functions. Once I found the data elements I pushed them to a procedure to do an insert query.

View attachment 71283
ReadText.jpg

There may be a more eloquent way to do this, but seems to work fine for the test set.

Here is the code

This is the main part of the code. It reads line by line, sees if the line is what You are looking for. If the line is what you are looking for then use some string functions (mostly the split function). Once it gets all the data elements it passes to 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 strSegment As String
  Dim strMOM As String
  Dim strDepartment As String
  Dim TheSegment As String
  Dim ThePrintDate As String
  Dim ThePage As String
  Dim TheMOM As String
  Dim TheDepartment As String
  Dim StrReject As String
  Dim strWarning As String
  Dim strException 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 TempArray() As Variant
  Dim I As Integer
  intFile = FreeFile()
  
  Open strFile For Input As #intFile
  
  Do While Not EOF(intFile)
    Line Input #intFile, strIn
   ' strIn = CleanField(strIn)
    If InStr(strIn, "SEGMENT :") > 0 Then
       strSegment = strIn
       Debug.Print strSegment
       TheSegment = Trim(Split(strSegment, ":")(1))
       TheSegment = Trim(Split(TheSegment, " ")(0))
      ' Debug.Print TheSegment
       ThePrintDate = Trim(Split(strSegment, TheSegment)(1))
       ThePrintDate = Trim(Split(ThePrintDate, " ")(2))
       'Debug.Print ThePrintDate
       ThePage = Mid(strSegment, InStr(strSegment, "Page") + Len("Page"))
       ThePage = Trim(ThePage)
       'Debug.Print ThePage
       'Next line is MOM ID
       Line Input #intFile, strIn
       strMOM = strIn
       TheMOM = Trim(Split(strMOM, ":")(1))
       TheMOM = (Replace(TheMOM, "  ", ""))
      'Debug.Print TheMOM
      'next line is department nam
       Line Input #intFile, strIn
      strDepartment = strIn
      'Debug.Print strDepartment
      TheDepartment = Trim(Split(strDepartment, ":")(1))
      TheDepartment = Split(TheDepartment, "  ")(0)
      'Debug.Print TheDepartment
      InsertSegment TheSegment, ThePrintDate, ThePage, TheMOM, TheDepartment
      TheSegment = ""
      ThePrintDate = ""
      ThePage = ""
      TheMOM = ""
      TheDepartment = ""
    End If
    
    If InStr(strIn, "Rej Reason :") > 0 Then
      StrReject = strIn
      TheReject = Trim(Split(StrReject, ":")(1))
      Debug.Print "Reject Reason: " & TheReject
      'Get a clean array with only items that are non blank
      TempArray = CleanArray(StrReject)
      TheS_No = TempArray(0)
      Debug.Print "S_No: " & TheS_No
      TheInstrument = TempArray(1)
      Debug.Print "Instrument " & TheInstrument
      TheAmount = TempArray(2)
      Debug.Print "Amount " & TheAmount
      'Move to next line
      Line Input #intFile, strIn
      TheWarning = Trim(Split(strIn, ":")(1))
      Debug.Print "Warning: " & TheWarning
      'Move to next line
      Line Input #intFile, strIn
      TheException = Trim(Split(strIn, ":")(1))
      Debug.Print "The Exception " & TheException
      InsertRejection TheS_No, TheInstrument, TheAmount, TheReject, TheWarning, TheException
      TheReject = ""
      TheS_No = ""
      TheInstrument = ""
      TheAmount = ""
      TheWarning = ""
      TheException = ""
    End If
  Loop

  Close #intFile

End Sub

Once I find the data elements I pass to these functions to do the insert query.
Code:
Public Sub InsertSegment(TheSegment As String, ThePrintDate As String, ThePage As String, TheMOM As String, TheDepartment As String)
  'Write an insert query here
  'Put your real table name below
  Const Table1 = "Table1"
  Dim strSql As String
  TheSegment = "'" & TheSegment & "'"
  ThePage = "'" & ThePage & "'"
  'If you want to save this as a real date will need some more logic
  ThePrintDate = SQL_Date(ThePrintDate)
  TheMOM = "'" & TheMOM & "'"
  TheDepartment = "'" & TheDepartment & "'"
  strSql = "Insert Into " & Table1 & "(Segment,Print_Date,Page,MOM_ID,Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", " & TheMOM & ", " & TheDepartment & ")"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub
Public Sub InsertRejection(TheS_No As String, TheInstrument As String, TheAmount As String, TheReject As String, TheWarning As String, TheException As String)
  'Write an insert query here
  'Put your real table name below
  Const Table1 = "Table2"
  Dim strSql As String
  TheReject = "'" & TheReject & "'"
  TheWarning = "'" & TheWarning & "'"
  TheAmount = Replace(TheAmount, ",", "")
  'If you want to save this as a real date will need some more logic
  TheException = "'" & TheException & "'"
  strSql = "Insert Into " & Table1 & "(S_No,Instrument_Number,Amount,Reject_Reason, Warning,Exception) VALUES ( " & TheS_No & ", " & TheInstrument & ", " & TheAmount & ", " & TheReject & ", " & TheWarning & ", " & TheException & " )"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

One way to get the data elements would be to read the string character by character and only take those things that did not have more than two spaces in them. I choose to split the string by a single space. Then I used this to create a new array with only those array elements that were not blank.
Code:
Public Function CleanArray(TheString As String) As Variant
  'Send in a string and only return the items that are non blank
  Dim I As Integer
  Dim counter As Long
  Dim TempArray() As String
  Dim TempArray2() As Variant
  TempArray = Split(TheString, " ")
  For I = 0 To UBound(TempArray)
    If Not Replace(TempArray(I), " ", "") = "" Then
      TempArray2 = AddElement(TempArray2, Trim(TempArray(I)))
      counter = counter + 1
    End If
  Next I
  CleanArray = TempArray2
End Function

Code from the internet dynamically add elements.
Code:
Public Function AddElement(ByVal vArray As Variant, ByVal vElem As Variant) As Variant
      ' This function adds an element to a Variant array
      ' and returns an array with the element added to it.
      Dim vRet As Variant ' To be returned
      If IsEmpty(vArray) Or Not IsDimensioned(vArray) Then
          ' First time through, create an array of size 1.
          vRet = Array(vElem)
      Else
          vRet = vArray
          ' From then on, ReDim Preserve will work.
          ReDim Preserve vRet(UBound(vArray) + 1)
          vRet(UBound(vRet)) = vElem
      End If
      AddElement = vRet
  End Function
Public Function IsDimensioned(ByRef TheArray) As Boolean
      If IsArray(TheArray) Then ' we need to test it! otherwise will return false if not an array!
                      ' If you put extensive use to this function then you might modify
                      ' it a lil' bit so it "takes in" specific array type & you can skip IsArray
                      ' (currently you can pass any variable).
        On Error Resume Next
            IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)
        On Error GoTo 0
        'FYI: Erros are not always generated by Ubound & LBOund
        '     when array is empty (when arrays are "made" empty in some "specic way").
        '     So we cant use usual "decide" logic: bool = (err.number <> 0))
        'ie.:
        '    str = VBA.StrConv("", vbFromUnicode) 'generally you should use strconv when
        '                                          you plan converting string to bytearray,
        '                                          (here StrConv actually not needed for "",
        '                                          it's length is 0 anyway)
        '    ByteArr() = str
        '     UBound(ByteArr) => -1
        '     LBound(ByteArr) => 0
        'but:
        '    Erase ByteArr
        '     UBound(ByteArr) => Causes Error
        '     LBound(ByteArr) => Causes Error
        ' NOTE: I'm not sure, but I'm guessing (based on OnErr0r "knowledge" - > [url]http://www.xtremevbtalk.com/showthread.php?threadid=105700[/url])
        '    that "ByteArr() = str" will cause ByteArray point to SAFEARRAY in any case,
        '    even if "str" is empty while "Erase ByteArr" will remove that.
        ' QUESTION: can we make some how empty SAFEARRAYS for other types than
        '           ByteArrays as well????  I can't fiqure it out right now...
        '           Maybe doing something in low level...
    Else
        'IsDimensioned = False ' is already false by default
        Call Err.Raise(5, "IsDimensioned", "Invalid procedure call or argument. Argument is not an array!")
    End If
End Function

Regardless of your regional settings and any format, SQL needs dates in MM/DD/YYYY format for a proper insert

Code:
Public Function SQL_Date(strDate As String) As String
  'Turns a dd-mm-yyyy into a mm/dd/yyyy needed for a SQL insert regardless of regional settings
   '04-07-2018 becomes #07/04/2018# for the insert
   Dim aDate() As String
   aDate = Split(strDate, "-")
   SQL_Date = "#" & aDate(1) & "/" & aDate(0) & "/" & aDate(2) & "#"
End Functio
n
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Sorry for the delay, i just came from work and couldnt wait to respond.
Thanks MajP, for detailed explanation and solution. I simply don't have words to express myself. If i had to write this code it would have taken a light year to get this.
I seriously have to think of learning this code which can make anything happen.

I tested your db on the actual data consisting of 2416 pages and i got the below error. So reduced the pages to 1 and took data which had "Rej Reason" on the second line and still got the below error.

PHP:
Run-time error "9":
Subscript out of range

I think this was because the field "Rej Reason : is sometimes on the 1st line and sometimes on the 2nd line. Also, other fields "Warning"; "Exception" can be on any line or in some cases they may be absent.

Is it possible to get the second table2 to read the line which can be either of the below:
PHP:
Exception :
Warning :
Rej Reason :

Below are which i did not see before, but they also can be part of the lines
Error : 
Warning :  (This one is duplicate but has different reason which is fixed "Instrument already entered through ICTM"

What line of code do i change i need to add another column in the future in table1.
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
HI! I tried to replicate another sample_text2 with the different scenarios. I tried to copy but it gave me the "Run-time error 9"

attached Sample_Text2
Thanks
 

Attachments

  • Sample_text2.txt
    17.1 KB · Views: 105

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
As I expected, it is much more challenging then I thought.
In the original data I assumed everything will have a rej Reason, Warning, Exception. Like so:
Code:
Rej Reason : Pxxxxxxx Rxxxxxx
Warning :    Mxxx Pxx Exxxxx
Exception :  Ixxxxxxxxxxx Axxxx
Instrument Rejected
So the logic was very simple based on that. But the new data has all kinds of possibilities. I am going to have to add a lot more logic to see if those elements exist or not.
Two warnings:
Code:
Warning :    Mxxx Pxx Exxxxx
Warning :    Instrument already entered through ICTM
Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
Instrument Rejected

Or no Exception
Code:
Rej Reason : Sxxxxxxx Ixxxxxx
Warning :    Mxxx Pxx Exxxxx
Instrument Rejected

Or no reject reason, but an Error and no warning
Code:
Error :      C. I. DR TRAN TO DORMANT
Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
Instrument Rejected
Adding an extra column for Error will be easy. However, what do you want it to do if you have 2 warnings or 2 of something else? This will take a bit more than a single line of code change. However, this is what is normally expected when doing something like this. You have to code for all the possibilities. I will try to code this sample, but it code will continue to grow as you account for the possibilities. I will take a look when I get a chance.
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thanks for your response,
I tried my best to get all the various scenarios from the 2600 odd lines. Just a thought, can the code look for any of the below lines if they match with the list in another table (table3) then put them in the respective fields in table2. This way if i encounter any error. i just need to add that line in table3. I may be wrong or may not be possible.

PHP:
Warning :    MXXX PXX EXISTS
Exception :  IXXXXXXXXXX AXXXXX BAL EXCP
Warning :    Instrument already entered through ICTM
Rej Reason : Pxxxxxxx Rxxxxxx
Error :      C. I. DR TRAN TO DORMANT
Instrument Rejected                                (If it reads this put in separate field)
            No Exception or Warnings encountered   (if it reads this entire sentence then put in separate field as "No Exp or Wrng")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
Yes. It is going to take a litlle more logic because you no longer know where to start and stop. The new logic will find this line to start looking
Code:
S.No Instrument Number  Amount               Error Description
Then the start of a record will be a line with the first character being numeric
Code:
1                 1              1,000.00    Rej Reason : Sxxxxxxx Ixxxxxx
It will then look for any in any order
Rej Reason, Error, Warning, Exception, Instrument Rejected, No Exception or Warnings encountered

It will do the insert when it finds either another record (a line starting with an S_No) or stops when it finds the line
Code:
---------------------------

Do you want the fields for:
Code:
Instrument Rejected                                
No Exception or Warnings encountered
To be yes no fields? Since they will be their own field.

Still not sure what you want to do when you have two of something like two Warnings. I can concatenate it like:

Code:
MXXX PXX EXISTS; Instrument already entered through ICTM

Also I am guessing there is something more not being accounted for. Don't you need table 1 and 2 related. I do not know your business rules but it looks like a test is run. That test is uniquely described by a composite key which is the combination of
Segment, MOM_ID, and Test
Then the second table are the test results. I would assume you need to relate those test results back to table one.

Would three tables make sense. I would think you have in table 1, The same fields and maybe a simple Primary Key (so I do not have to deal with a composite key)
Table 2 could just have S_No, Instrument_No, Amount, and a field to relate it back to table1. Table 3 would have all the error codes not in their own fields, but in a single fields of error codes and error code types. This would relate back to table 2..

Table 3 would look like
ErrorCodeType ErrorCode TestRunID_ForeignKey
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thank you for your response. I will try my best to answer your questions. Kindly bear with me please.
PHP:
Do you want the fields for:
Code:
Instrument Rejected                                
No Exception or Warnings encountered
To be yes no fields? Since they will be their own field.
Yes. This would be two fields in table2 "Instrument Rejected" and "No Exception or Warnings encountered" and if they are present in the line then should be a "Yes" in respective fields

PHP:
Still not sure what you want to do when you have two of something like two Warnings. I can concatenate it like:

Code:
MXXX PXX EXISTS; Instrument already entered through ICTM
Yes, if it is concatenated would be good idea

PHP:
Also I am guessing there is something more not being accounted for. Don't you need table 1 and 2 related. I do not know your business rules but it looks like a test is run. That test is uniquely described by a composite key which is the combination of
Segment, MOM_ID, and Test
Then the second table are the test results. I would assume you need to relate those test results back to table one.
Would three tables make sense. I would think you have in table 1, The same fields and maybe a simple Primary Key (so I do not have to deal with a composite key)
The above if i suggest in table1 if you can split the MOM ID into two fields which is "0001" and another field named as MOM TXT which could be the second part "ABC - Test1" then in table2 also if you can add MOM ID which is again "0001" then we can have a relation between the table1 & table2.
Then we don't need a composite key and a simple Primary key is enough to do the relationship.

Besides this i also wanted additionnal 2 fields in table2 which is below:
Reference Name: PQR
Reference : 1234567891023

Kindly refer to attachment:
a) table1 & table2 field names respectively
b) Relation between table1 & table2
c) table3 field names

PHP:
Table 2 could just have S_No, Instrument_No, Amount, and a field to relate it back to table1. 
Table 3 would have all the error codes not in their own fields, but in a single fields of error codes and error code types. 
This would relate back to table 2..

Table 3 would look like
ErrorCodeType ErrorCode TestRunID_ForeignKey
In table3 does this mean that it would allow me to add error codes whenever i encounter new errors in the line. If this the functionality and my understanding correct.

Hope i have answered your queries. I thank you for doing this for my project and so grateful.
 

Attachments

  • table field names & relationship.png
    table field names & relationship.png
    84.3 KB · Views: 106

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
I edited the below example for discussion, and took out some lines that I though were summary data.

Code:
                                                        IXXXXX CXXXXXXX Zone VALIDATION RUN
                                                    
 SEGMENT : CLASS7                                            Print Date 04-07-2018 12:40:37     Page        5 of 2000
 MOM ID: 1100       MNO - Test50
 Department Name: DEFAULT NAME2                                           CAT                  ABR120

---------------------------------------------------------------------------------------------------------------------------------
 Reference: 1234567890567            Reference Name: MAD
 Effective Axxx:                   1,200.00       Hold Refrence:       200.00   BA Lxxxx:                   0.00


---------------------------------------------------------------------------------------------------------------------------------
 S.No Instrument Number  Amount               Error Description                    |
---------------------------------------------------------------------------------------------------------------------------------
 1                 10            2,000.00     Warning :    Instrument already entered through ICTM
                                              Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
					      Instrument Rejected
---------------------------------------------------------------------------------------------------------------------------------

 Reference: 1234567890567            Reference Name: HAT
 Effective Axxx:                     200.00       Hold Refrence:       200.00   BA Lxxxx:                   0.00
---------------------------------------------------------------------------------------------------------------------------------
                                            Today's Ixxxxx Cxxxxxxx                |    Oxxxxxx Cxxxxxxx Details
---------------------------------------------------------------------------------------------------------------------------------
 S.No Instrument Number  Amount               Error Description                    |
---------------------------------------------------------------------------------------------------------------------------------
 1                 1              400.00      Error :      C. I. DR TRAN TO DORMANT
					      Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
					      Instrument Rejected
 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
---------------------------------------------------------------------------------------------------------------------------------


From Looking at this example I would think you have the following Tables. I am guessing and do not know how you use the data or what the data is. Also I am guessing at good names of the types of tables.

Code:
table1 (maybe tblValidation)  
  MOM_ID:  Primary Key with value of "1100 MNO - Test50"  
  SEGMENT
  Print_Date
  Page
  Department
It looks like you cannot just use MOM_ID = 1100 as the primary key because I see you have other tests with the same MOM_ID. Could this table be called TblValidation? It appears to describe a test validation

Inside one of these test validations there appears to be multiple "References"

Code:
table2  (maybe tblReferences?)
  ReferenceID:  This could be the combination 1234567890567MAD and be the primary key 
  RefernceNumber: 1234567890567
  ReferenceName: MAD           
  MOM_ID_FK: This is a foreign key to table 1 = "1100 MNO - Test50"
I see two items in this table: 1234567890567MAD and 1234567890567HAT

Then it appears as if references has multiple "runs" or details. It looks like some kind of test run. MAD has 1 run and HAT has 2 runs.

Code:
table3 (maybe tblReferenceRuns)
  RefRun_ID:  I do not see a natural key so I would add an autonumber
  S.No 
  Instrument_Number  
  Amount
  ReferenceID_FK : This is a foreign key relating these records to the reference table (1234567890567MAD and 1234567890567HAT)

Then it looks like a run can have many error description. Do you really need individual fields?

Code:
table4 (maybe tblErrorDescripton)
  ErrorType: (values include Error, Exception, Warning, Rej Reason, Other)
  ErrorDetail: (example C. I. DR TRAN TO DORMANT,  Ixxxxxxxxxxx Axxxx BAL EXCP, Instrument Rejected)
  ReferenceRun_FK:  relates the error descriptions to that ReferenceRun

Thoughts?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
Based on the above my tables would look something like this. The assumption is that MOMID does not repeat in your data and that those Reference IDs also do not repeat. If you do not care about the reference you could just relate the run information back to the MOM_ID

Code:
MOM_ID             Segment  PrintDate   Page      Department
1100 MNO - Test50  CLASS7   04/07/2018  5 of 2000 DEFAULT NAME2

Code:
Reference_ID      ReferenceNumber ReferenceName  MOM_ID_FK
1234567890567MAD  1234567890567   MAD            1100 MNO - Test50
1234567890567HAT  1234567890567   HAT            1100 MNO - Test50


Code:
RunID S_No  Inst_No        Amount        Reference_FK
123   1      10            2,000.00      1234567890567MAD
124   1      1             400.00        1234567890567HAT
125   1      10            2,000.00      1234567890567HAT


Code:
MessageType MessageDetail                            RunID_FK

 Warning    Instrument already entered through ICTM  123
 Exception  Ixxxxxxxxxxx Axxxx BAL EXCP              123
 Reject	    Instrument Rejected                      123
 Error      C. I. DR TRAN TO DORMANT                 124 
 Exception  Ixxxxxxxxxxx Axxxx BAL EXCP              124
 Other	    Instrument Rejected                      124
 Error      C. I. DR TRAN TO DORMANT                 125
 Warning    Instrument already entered through ICTM  125
 Exception  Ixxxxxxxxxxx Axxxx BAL EXCP              125
 Reject	    Instrument Rejected                      125
 

lookforsmt

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

I am referring to your response in post#16 below
PHP:
It looks like you cannot just use MOM_ID = 1100 as the primary key because I see you have other tests with the same MOM_ID. Could this table be called TblValidation? It appears to describe a test validation

table1 = tblValidation
MOM_ID is unit name indentification where 0001 is numberic and MNO - Test50 is text name in full and we also have abbveration(MMC) code just on the next line whcih is "CAT"
I feel such a idiot of providing the wrong info.
My applogies, while coping to replicate data, i have accidently copied wrong MOM_ID without changing the MOM_TXT.

Below samples MOM_ID is uniquie and in no circumstance will two MOM_ID have the same text.

PHP:
MOM_ID            MOM_TXT     MOM_MMC
0001           MNO - Test50      MNO
1100	       ABC - Test25	  ABC
1215	       CBC - Test28	  CBC
1219	       XYZ - Test29	  XYZ
1230	       PQR - Test30	  PQR

i have attached image where these 3 field lies in the text file. I am so sorry to take you to different direction due to my poor communication.
I think the initial db was fine, only addition things that were required.
i am listing them down due to the mis-communication.

table1
1) Split field MOM_ID data as MOM_ID and MOM_TXT
2) Add a text field MOM_MMC
(total 8 fields)

table2
1) Add below fields:
a) Error
b) Instrument_Rejected
c) No_Exception_or_Warnings_encountered
d) MOM_ID (duplicate existing in table1 as well)
e) Reference_Name
f) Reference
(total 13 fields)

Then we don't need a composite key and a simple Primary key is enough to do the relationship. (Kindly refer to my post #15 pls)

Applogies once again, hope you accept.
 

Attachments

  • image3.png
    image3.png
    45.7 KB · Views: 99

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:40
Joined
May 21, 2018
Messages
8,463
OK can do that.
So for this case with duplicates
Code:
1                 10            2,000.00     Warning :    Mxxx Pxx Exxxxx
					      Warning :    Instrument already entered through ICTM
                                              Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
					      Instrument Rejected
The Warning field will be concatenated
Code:
Mxxx Pxx Exxxxx; Instrument already entered through ICTM
Can all of the cases be duplicated? (Rej Reason, Exception, Error, Warning ?)
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thank you for the understanding.

Concatenate only the Warning field pls. The others should be in separate fields.
 

Users who are viewing this thread

Top Bottom