Help to import text file of multiple formats (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Thank you so much MajP for the code and the db. I just tried adding some more records and it gives me a perfect output.

I will try and understand your code and comeback pls.

Its amazing work that you do.
Thanks once again for all the help provided.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
Looking at the OW the format seems the same. I could not see a difference. However SOL and USER ID have a lot of spaces before the ":". Is that intentional? This will change the code a little.
Code:
SOL ID                              :
USER ID				:
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Dear MajP, i am in rush to office, extremly sorry but i will answer your query by evening, if i am not able to access this in office.
Yes the space is as is in OW file

bye
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
HI! MajP, I am extremely sorry, had to rush to office it was 7:30 morning here.


i tried to import actual IW file. I am getting below error:
Run-time error '9'. Subscript of out of range

The subform is blank, but when i close and reopen the form again it displays partial records only from the 1st group.
The IW file has 14 pages.
1st group goes from page1 to page2. After i import only the 1st page is imported. i am unable to find the difference between the actual file and the sample file i created.

It could be the space between the headers.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
That error would be do to the fact that TempArray has fewer elements than expected
Code:
      TempArray = GetCleanArray(StrIn)
      TheLoanAcctNo = TempArray(0)
      TheDebitAcctNo = TempArray(1)
      TheAcceptedDate = TempArray(2)
      TheAmount = TempArray(3)
      TheStartChqNo = TempArray(4)
      TheBankName = TempArray(5)
      ThePresentationDate = TempArray(6)
      TheChqLodgedNo = TempArray(7)
      TheSetNo = TempArray(8)

After finding the userID it starts reading each line and parsing it into an array. If that line does not have 9 columns then it will throuw an error when you try to return something greater than the amount of elements. Example only 3 columns in the string, then the function getCleanArray will return an array with 3 elements. If you do "TheBankName = TempArray(5)" you will get a subscript out of range.

There is no error checking at this time, but probably needs to get added. You may want to look at the error checking in the other sub. However, you need to see where it is failing and why because it may be an issue in the logic and not a problem with the file. You can probably see what records got added and then look at the record it fails on. There are a lot of things that could cause this. Remember it looks for the userID and then starts splitting and reading the columns. If there is a space or a line without 9 elements that could cause it to fail. Also it then stops when it finds the first empty line after the columns. If that line is not empty that could cause an issue. Look at the table for the last data entered. Then go look at the sheet for something different in format after the last processed line of data. If you can send a sanitized version of the problem file, I can take a look.

You can add a debug.print StrIn. This would print every line that it reads so you can see where it failed.
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Thanks MajP for the detailed explanation to search for the issue. I think the issue is still in the file and not in your logic as it runs quite well with the sample file that i provided. But when i ran it on the original file it throws me the error and i could see the below line it stops
"The LoanAcctNo = TempArray(0)

If there is a space or a line without 9 elements that could cause it to fail. Also it then stops when it finds the first empty line after the columns. If that line is not empty that could cause an issue. Look at the table for the last data entered.
I am trying to find the difference between these two files. As mentioned earlier the space or the tab before the User Id may be the problem.

But i am not sure. Unfortunate i am not able to send the original file out.
But i will look closely at every line and try to identify the difference between these two files,
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Hi! MajP, i have figured out the difference between the two txt files.
There is a space after the Set Number Data "C6752444" and also on the next line which i had not kept.
I feel this is the reason. I have attached new version of this file. I am aware the issue but not able to code this.

Can you pls test on this IW Sample2 text file and help me.
 

Attachments

  • IW Sample2.txt
    4.5 KB · Views: 102

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
Try this.
The logic is that is splits a line into an array as before. If that array has 9 elements (columns) and the first element is numeric (not a header) then it is a data line.


Code:
Public Sub ImportIW(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim StrIn As String
  Dim TheSOL_ID As String
  Dim TheUser_ID As String
  Dim TheLoanAcctNo As String
  Dim TheDebitAcctNo As String
  Dim TheAcceptedDate As String
  Dim TheAmount As String
  Dim TheStartChqNo As String
  Dim TheBankName As String
  Dim ThePresentationDate As String
  Dim TheChqLodgedNo As String
  Dim TheSetNo As String
  Dim TempArray() As String
  Dim FileType As String
  Dim StartRead As Boolean
  intFile = FreeFile()
  Open strFile For Input As #intFile
  
  Do While Not EOF(intFile)
    Line Input #intFile, StrIn
   
    If InStr(StrIn, "SOL ID") > 0 Then
       'Debug.Print strIn
       TheSOL_ID = FindAfter(StrIn, ":")
       Debug.Print TheSOL_ID
    ElseIf InStr(StrIn, "USER ID") > 0 Then
       TheUser_ID = FindAfter(StrIn, ":")
       StartRead = True
     ' Debug.Print TheUser_ID
    ElseIf StrIn = "" Then
      StartRead = False
    ElseIf StartRead Then
      TempArray = GetCleanArray(StrIn)
      If UBound(TempArray) = 8 Then
        If IsNumeric(TempArray(0)) Then
          TheLoanAcctNo = TempArray(0)
          TheDebitAcctNo = TempArray(1)
          TheAcceptedDate = TempArray(2)
          TheAmount = TempArray(3)
          TheStartChqNo = TempArray(4)
          TheBankName = TempArray(5)
          ThePresentationDate = TempArray(6)
          TheChqLodgedNo = TempArray(7)
          TheSetNo = TempArray(8)
          Debug.Print TheLoanAcctNo
        ' Debug.Print TheDebitAcctNo
        ' Debug.Print TheAcceptedDate
        ' Debug.Print TheAmount
        ' Debug.Print TheStartChqNo
        ' Debug.Print TheBankName
        ' Debug.Print ThePresentationDate
        ' Debug.Print TheChqLodgedNo
        ' Debug.Print TheSetNo
         InsertIW TheSOL_ID, TheUser_ID, TheLoanAcctNo, TheDebitAcctNo, TheAcceptedDate, TheAmount, TheStartChqNo, TheBankName, ThePresentationDate, TheChqLodgedNo, TheSetNo
        End If
      End If
    End If
  Loop
  Close #intFile
End Sub

The only change in the code is the two if checks
Code:
 If UBound(TempArray) = 8 Then
        If IsNumeric(TempArray(0)) Then

I also modified how it searches for user and SOL ID so now it works on the OW report as well
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
If you have some other reports send them. As shown you will likely need a sub routine for each report with just a slight variation.
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Thank you MajP for the code and amazing work!
This has worked perfectly for both IW and OW file.

Now i have applied the same logic on my next file and got the result with just the 1st three columns SOL ID; LINE OF BUSINESS & RM ID takes the header names instead of data (1203 & RETL) respectively. Here RM ID does not have any data after ":"

Text file LD_1 imported without an error. However just one place i need your help. The structure and data layout is almost the same with few differences.

There is additional line between SOL ID and RM ID (refer image) LINE OF BUSINESS

Below is the changes:
Code:
  Do While Not EOF(intFile)
    Line Input #intFile, StrIn
   
    If InStr(StrIn, "SOL ID") > 0 Then
       'Debug.Print strIn
       TheSOL_ID = FindAfter(StrIn, ":")
       Debug.Print TheSOL_ID

[COLOR="Blue"]    ElseIf InStr(StrIn, "LINE OF BUSINESS") > 0 Then
       TheLINEOFBUSINESS = FindAfter(StrIn, ":")
       StartRead = True
[/COLOR]
[COLOR="Sienna"]    ElseIf InStr(StrIn, "RM ID") > 0 Then
       TheRM_ID = FindAfter(StrIn, ":")
       StartRead = True[/COLOR]
     ' Debug.Print TheRM_ID
    ElseIf StrIn = "" Then
      StartRead = False
    ElseIf StartRead Then
      TempArray = GetCleanArray(StrIn)
      If UBound(TempArray) = [COLOR="Blue"]7[/COLOR] Then
any suggestions

Thank you so much once again and you have made my day.
 

Attachments

  • LD Image_1.jpg
    LD Image_1.jpg
    87.6 KB · Views: 98

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
Now i have applied the same logic on my next file and got the result with just the 1st three columns SOL ID; LINE OF BUSINESS & RM ID takes the header names instead of data (1203 & RETL) respectively.
I cannot explain that. I would not think that is possible. Can you provide a file/
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Can i provide this by evening as i need to replicate it on my personal laptop.
if you see the image i have attached, there are two images over each other. The form snapshot i have one line data shown which 1st 3 columns is the header names in the subform.

i will send you the text file and the data once i have it on my personal laptop.

Thank you.
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
probably, i have done somemore changes which i have not mentioned caused this. I will try to recollect and play it to you.
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
HI! MajP,
Sorry, couldnt send this early as replicating the text file and db.

i have created a new table, tblLW. also source txt file attached.

As mentioned earlier, the output 1st 3 columns takes the header as data.
ID SOL_ID Line_Of_Business RM_ID
1 SOL ID LINE OF BUSINESS RM ID
2 SOL ID LINE OF BUSINESS RM ID
3 SOL ID LINE OF BUSINESS RM ID
4 SOL ID LINE OF BUSINESS RM ID
5 SOL ID LINE OF BUSINESS RM ID
6 SOL ID LINE OF BUSINESS RM ID
7 SOL ID LINE OF BUSINESS RM ID
8 SOL ID LINE OF BUSINESS RM ID
9 SOL ID LINE OF BUSINESS RM ID

i missed to mention i added a line in the code in module "mdlReadTextFile" besides the changes mentioned in post#30
Code:
Public Function FindAfter(ByVal SearchIn, ByVal 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"
  SearchIn = CleanAndRemoveSpaces(SearchIn)
  FindAfter = Trim(Split(SearchIn, SearchAfter)(1))
[B][COLOR="Blue"]  FindAfter = Trim(Split(SearchIn, SearchAfter)(0))[/COLOR][/B]
  FindAfter = Trim(Split(FindAfter, "  ")(0))
End Function

will you be able to identify the mistake i have made in the code.
 

Attachments

  • Read Multiple Reports_2 LW.accdb
    816 KB · Views: 102
  • LBW_1.txt
    3.7 KB · Views: 111

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
You did not make a mistake in the code. The FindAfter function was specifically designed for your first thread, and luckily it worked this far. In truth I am trying to understand, how it did not fail. In your original thread the strings were more complex. For example you could have a string like
''SEGMENT : CLASS7 Print Date: 04-07-2018 12:40:37

You always had a ":" one space from the header. The function was hardwired to pull all strings after a given string as long as it only had one space. For example
FindAfter(strIn,"Print Date:")
would pull the whole string "04-07-2018 12:40:37" because it only had a single space between elements. It would stop pulling data once it hit two spaces. In the LW file there are two spaces after the ":" and it messes things up. For the LW add this modification to the module and call FindAfter2 instead of FindAfter.
Code:
Public Function FindAfter2(ByVal SearchIn, ByVal SearchAfter) As String
  'Find after was meant for the original problem where you had multiple items in the line following a header and ":"
  'Use this for lines with a single header followed by ":"
  SearchIn = CleanField(SearchIn)
  SearchIn = RemoveSpaces(SearchIn)
   FindAfter2 = Trim(Split(SearchIn, SearchAfter)(1))
End Function

If I have time I will see if I can change the logic to handle all cases.

You could probably use the original findAfter but since you have so many spaces it would be ugly

Code:
 If InStr(StrIn, "SOL ID								:") > 0 Then
       TheSOL_ID = FindAfter(StrIn, "SOL ID								:")
 

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Thanks MajP, i tried my luck in the air and luckly it was somewhat right on the code part.

trying to understand your code so i can do the job on my own.
Thanks for all the support.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
I modified it a little so you can change between what table you are importing and viewing. You can now test all your reports on a single form, by adding buttons.
 

Attachments

  • Read Multiple Reports_V3.accdb
    828 KB · Views: 99

lookforsmt

Registered User.
Local time
Today, 05:53
Joined
Dec 26, 2011
Messages
672
Thanks MajP for all the support and revised DB. I have an fair idea on the coding and will apply on the remaining text files. I will close this thread now.

Thanks once again.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:53
Joined
May 21, 2018
Messages
8,463
One thing you may want to add is a way to determine what type of report it being imported instead of making the user pick a button. So if there is some unique line or words in the report you read until you determine that line. These are examples only and may not be unique

Code:
Public Sub DetermineFileType(strFile as string)
' ***************************************************
' * Open a Text File And Loop Through It determine file type           *
' ***************************************************
  Dim intFile As Integer
  Dim StrIn As String
  intFile = FreeFile()
  Open strFile For Input As #intFile
  
  Do While Not EOF(intFile)
    Line Input #intFile, StrIn
     If InStr(StrIn, "Inward=PQR") > 0 Then
     'you found an IW
     close #Infile 
     importIW StrFile
    elseif InStr(StrIn, "Clearing Added=PQR") > 0 then
     'you found an OW
     close #Infile 
     importIW StrFile
    elseif InStr(StrIn, "RM ID") > 0 then
     'you found an LW
     close #Infile 
     importLW StrFile
    elseif.....
    elseif
    ...
    end if
    Loop
  Close #intFile
End Sub
 

Users who are viewing this thread

Top Bottom