Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 07:30 AM   #31
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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 is offline   Reply With Quote
Old 07-13-2018, 08:13 AM   #32
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

Try this version. Should be a little more robust. Clear out the debug window before running, and post back any results.
Attached Files
File Type: accdb ReadTextFile_20180713.accdb (652.0 KB, 13 views)
MajP is offline   Reply With Quote
Old 07-13-2018, 09:02 AM   #33
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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


Last edited by MajP; 07-13-2018 at 11:10 AM.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-15-2018)
Old 07-13-2018, 01:12 PM   #34
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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 is offline   Reply With Quote
Old 07-14-2018, 11:30 AM   #35
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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
lookforsmt is offline   Reply With Quote
Old 07-14-2018, 12:17 PM   #36
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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 is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-15-2018)
Old 07-14-2018, 12:35 PM   #37
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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 :

MajP is offline   Reply With Quote
Old 07-14-2018, 04:18 PM   #38
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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.

Last edited by lookforsmt; 07-14-2018 at 04:33 PM.
lookforsmt is offline   Reply With Quote
Old 07-14-2018, 06:20 PM   #39
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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.
MajP is offline   Reply With Quote
Old 07-14-2018, 06:49 PM   #40
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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

Last edited by lookforsmt; 07-14-2018 at 06:55 PM.
lookforsmt is offline   Reply With Quote
Old 07-14-2018, 09:46 PM   #41
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

This version has a report to show you how it all relates. The report tries to look like the original report.
Attached Files
File Type: accdb ReadTextFile4_20180714.accdb (1.41 MB, 9 views)
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-15-2018)
Old 07-15-2018, 10:09 AM   #42
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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.
lookforsmt is offline   Reply With Quote
Old 07-15-2018, 10:43 AM   #43
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

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.
Attached Files
File Type: accdb ReadTextFile4 Ver b_20180714.accdb (1.41 MB, 14 views)
MajP is offline   Reply With Quote
Old 07-16-2018, 10:12 AM   #44
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 523
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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
lookforsmt is offline   Reply With Quote
Old 07-16-2018, 10:22 AM   #45
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
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.

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create and open a text file IainMc2013 Modules & VBA 0 04-10-2013 07:38 AM
Create table in access from a text file Helpy Modules & VBA 0 07-21-2009 08:37 AM
Create Recordset and Export to Text File jketcher Modules & VBA 5 07-16-2009 11:48 PM
Create a function from a text file. Dan_T Modules & VBA 16 07-07-2005 09:24 AM
[SOLVED] Need to create text file netforce General 1 01-31-2003 09:20 PM




All times are GMT -8. The time now is 10:50 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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