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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 06:17 PM   #31
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

Aslo note there are total 11 columns + ID

lookforsmt is offline   Reply With Quote
Old 07-17-2019, 06:28 PM   #32
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

Quote:
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/
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-20-2019)
Old 07-17-2019, 06:47 PM   #33
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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 is offline   Reply With Quote
Old 07-17-2019, 06:49 PM   #34
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

probably, i have done somemore changes which i have not mentioned caused this. I will try to recollect and play it to you.
lookforsmt is offline   Reply With Quote
Old 07-18-2019, 10:10 PM   #35
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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.
Quote:
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))
  FindAfter = Trim(Split(SearchIn, SearchAfter)(0))
  FindAfter = Trim(Split(FindAfter, "  ")(0))
End Function
will you be able to identify the mistake i have made in the code.
Attached Files
File Type: accdb Read Multiple Reports_2 LW.accdb (816.0 KB, 6 views)
File Type: txt LBW_1.txt (3.7 KB, 6 views)
lookforsmt is offline   Reply With Quote
Old 07-19-2019, 01:59 PM   #36
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

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								:")
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-20-2019)
Old 07-19-2019, 06:20 PM   #37
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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.

lookforsmt is offline   Reply With Quote
Old 07-20-2019, 02:57 AM   #38
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

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.
Attached Files
File Type: accdb Read Multiple Reports_V3.accdb (828.0 KB, 4 views)
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-20-2019)
Old 07-20-2019, 04:32 AM   #39
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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.
lookforsmt is offline   Reply With Quote
Old 07-20-2019, 05:00 AM   #40
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

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
MajP is offline   Reply With Quote
Old 07-20-2019, 06:37 PM   #41
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

Thank you MajP, i didn't realize you can go this extent to simplify from the user point of view.
Do i need to create a new button called "Import" How do i call this new code.

Definitely these will be unique in most of my reports where the top line and the report header would be different from each other.

I have not gone through all the reports. Will give it a try and comeback if there is any challenges.


I was wondering, if a table is created mentioning the text layout information as attached and then point the code to that table fields, this way the codes will be fixed and i only need to ensure the table fields update as per the text layout header and data.

May be this is not a good idea, i will scrap this idea. Your suggestions.

i have attached the db and added table called, "tblIW_txtLayout"

Thanks
Attached Files
File Type: accdb Read Multiple Reports_V3.accdb (852.0 KB, 6 views)
lookforsmt is offline   Reply With Quote
Old 07-21-2019, 05:27 AM   #42
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

I put a single button called to import any report

Code:
Private Sub cmdImport_Click()
 Dim fileName As String
 Dim ReportType As String
 
 fileName = GetFile
 
 If fileName <> "" Then
   ReportType = GetReportType(fileName)
   MsgBox "This is a " & ReportType & " report."
 End If

 Select Case ReportType
   Case "IW", "OW"
     Me.subFormData.SourceObject = "table.TblIW"
     Me.lblTable.Caption = "IW / OW Table"
     ImportIW fileName
   Case "LW"
     Me.subFormData.SourceObject = "table.TblLW"
     Me.lblTable.Caption = "LW Table"
     ImportLW fileName
 End Select
 Me.subFormData.Requery
End Sub
Then added the function to return report type
Code:
Public Function GetReportType(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
     StrIn = CleanAndRemoveSpaces(StrIn) ' leaves double space
     StrIn = Trim(StrIn)
    If StrIn = "INWARD ADDED=PQR" Then
     'you found an IW
     Close #intFile
     GetReportType = "IW"
     Exit Function
    ElseIf StrIn = "CLEARING ADDED=PQR" Then
     'you found an OW
     Close #intFile
     GetReportType = "OW"
     Exit Function
    ElseIf StrIn = "LBW" Then
     'you found an LW
     Close #intFile
     GetReportType = "LW"
     Exit Function
    End If
    Loop
  Close #intFile
End Function
So you would just add to both of these as you enter more Report types.

I think you could code this with some kind of tables to store structure, but do not think it would look like what you are proposing. I would think it is a few very simple tables. The structure in the reports does seem common but not exact. In general you have 4 types of things.
1) A single line that determines the report type
2) multiple lines that you need to get the information after "FindAfter"
Sol ID:
User ID:
...
3) The column headers
4) Columns of data with different data types


The one rule that is working so far that makes this easy is that you know you have X amount of columns of data. So for a given line you create the array.
In the LW there are 7 columns. The only lines in the report with 7 items is the header rows or data rows. The data rows all have numeric elements in the first column. If this rule works for other reports all you need to know is how many columns you are looking for.
Code:
 If UBound(TempArray) = 7 Then
        If IsNumeric(TempArray(0)) Then
So you know you have a data row if you have 7 elements are the first one is numeric.

So if your reports all follow this pattern, I am thinking you could have several small tables to automate this.

Code:
TblReportType  ' used to determine what type of report
  SearchLine  
  ReportType
example
LBW LW
IW INWARD ADDED = PQR

Code:
TblSearchAfter  'Used to find those fields liker User ID, SOL ID where data follows the :
  ReportID
  SearchFor
  SearchAfter
  FieldName   'In the table
Example
IW SOL ID : TableIW SOL_ID

Code:
TblColumnData 'For each report store information about the column
  ReportID
  ColumnNumber 
  DataType
  FieldName
This would tell you that column X of report IW is currency and needs to get inserted into field Amount.

Code:
tblMetaData  'This would hold meta data for the report
   ReportID
   NumberOfColumns  'Need to know for a given report how many data columns
   tableName ' name of table to insert

I think this could end up with some very short code and very simple tables if done correctly, but could be very difficult to trouble shoot. Without seeing the other reports and how common they are to these it would be difficult to say if this would be worth it.
Attached Files
File Type: accdb Read Multiple Reports_V5.accdb (856.0 KB, 6 views)
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-21-2019)
Old 07-21-2019, 07:27 PM   #43
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

Thank you for your valuable time and all the support in providing the import in one click solution.

Highly appreciate your work and dedication and passion to support others and provide valuable guidance.

Thanks once again.

On my proposal, i will try to understand and implement it. It will take me sometime, will share my outcome in sometime.

I dont want to take anymore of your valuable time. Will work on the existing reports which is my priority and complete them.
Thanks MajP
lookforsmt is offline   Reply With Quote
Old 07-22-2019, 02:38 AM   #44
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

I looked at doing the table solution, and I think what I proposed is doable. I will try it as a challenge. One table that was easy to implement and would save some time is the meta data table. This has the ReportID (IW, LW, OW...), the string that uniquely identifieds the report, and the table that the data is imported into.

Code:
ReportID	ReportLongName	StringIdentifier	TableName
IW	IW Report	INWARD ADDED=PQR	tblIW
LW	LBW Report	LBW	tblLW
OW	OW Report	CLEARING ADDED=PQR	tblIW
I added some helper functions, which are just dlookups with a wrapper.

Code:
Public Function GetReportID(strIn As String) As String
  'pass in a string and see if the search string can be found and return id"
  GetReportID = Nz(DLookup("ReportID", "tblReportMeta", "stringIdentifier = '" & strIn & "'"), "")
End Function

Public Function GetTableName(ReportID As String) As String
  'pass in a string and see if the search string can be found and return id"
  GetTableName = Nz(DLookup("TableName", "tblReportMeta", "ReportID = '" & ReportID & "'"), "")
End Function
Then you can use it to simplify the GetReportType code. You can add more reports without modifying the code.

Code:
Public Function GetReportType(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
     strIn = CleanAndRemoveSpaces(strIn) ' leaves double space
     strIn = Trim(strIn)
     GetReportType = GetReportID(strIn)
     If GetReportType <> "" Then
       Close #intFile
       Exit Function
     End If
     Loop
  Close #intFile
End Function
So it reads lines of code until it finds a string that is in the tblReportMeta
MajP is offline   Reply With Quote
Old 07-22-2019, 02:58 AM   #45
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Help to import text file of multiple formats

I spent another few minutes on it and got it to work. Here is the common import file. At this time it only works for IW since I only populated the tblColumns with data for IW. Try populating for the two other tables. If that works this may be a solution for all of your reports. To add a new report, make the new import table, fill in the meta data, and fill in the columns data.





Code:
Public Sub ImportReport(strFile As String, ReportType As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim strIn As String
    
  Dim TempArray() As String
  Dim FileType As String
  Dim StartRead As Boolean
  Dim SearchFor As String
  Dim tableName As String
  Dim fieldName As String
  Dim NumberDataFields As Integer
  Dim tempData As Variant
  Dim DataType As String
  Dim AfterFound As Boolean
  Dim RS_FindAfter As DAO.Recordset
  Dim RS_Columns As DAO.Recordset
  Dim RS_Update As DAO.Recordset
  Dim I As Integer
  intFile = FreeFile()
  Open strFile For Input As #intFile
  Set RS_FindAfter = CurrentDb.OpenRecordset("select * from tblFindAfter where ReportID_FK = '" & ReportType & "'")
  tableName = GetTableName(ReportType)
  'Table to write to
  Set RS_Update = CurrentDb.OpenRecordset(tableName)
  NumberDataFields = GetNumberDataFields(ReportType)
  Do While Not EOF(intFile)
    Line Input #intFile, strIn
    
    
    If Not strIn = "" Then
      'Debug.Print strIn
      RS_FindAfter.MoveFirst
      Do While Not RS_FindAfter.EOF
        AfterFound = False
        SearchFor = RS_FindAfter!SearchFor
        If InStr(strIn, SearchFor) > 0 Then
          AfterFound = True
          fieldName = RS_FindAfter!fieldName
          DataType = RS_FindAfter!DataType
          tempData = FindAfter(strIn, RS_FindAfter!SearchAfter)
          'tempData = ConvertData(tempData, DataType)
          Debug.Print SearchFor & " " & tempData
          'Hold the tempData in the table tblFindAfter
          'This is kind of clunky but would require more code to store it somewhere else with
          'field name and field value
          RS_FindAfter.Edit
            RS_FindAfter!TempFoundValue = tempData
          RS_FindAfter.Update
          Exit Do
        End If
        RS_FindAfter.MoveNext
      Loop
      
      If Not AfterFound Then
        TempArray = GetCleanArray(strIn)
        If UBound(TempArray) = NumberDataFields - 1 Then
          If IsNumeric(TempArray(0)) Then
            'At this point you have found the data elements and all of the find after elements.
            RS_FindAfter.MoveFirst
            'Load the FindAfters
            RS_Update.AddNew
            Do While Not RS_FindAfter.EOF
              Debug.Print "find after"
              fieldName = RS_FindAfter!fieldName
              DataType = RS_FindAfter!DataType
              tempData = RS_FindAfter!TempFoundValue
              tempData = ConvertData(tempData, DataType)
              'Debug.Print tempData & " temp data"
              RS_Update.Fields(fieldName) = tempData
              RS_FindAfter.MoveNext
             Loop
             'Load the data elements
             For I = 0 To NumberDataFields - 1
              fieldName = GetColumnField(ReportType, I + 1)
              DataType = GetColumnDataType(ReportType, I + 1)
              RS_Update.Fields(fieldName) = ConvertData(TempArray(I), DataType)
             Next I
            RS_Update.Update
          End If
        End If
      End If
     End If
  Loop
  Close #intFile
End Sub
Attached Files
File Type: accdb Read Multiple Reports_V6.accdb (1,000.0 KB, 6 views)

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
Print-To-File (Generic/Text only) formats incorrectly Bechert Reports 1 04-13-2017 07:26 AM
Importing multiple text files in different formats mims Modules & VBA 0 10-15-2011 05:40 AM
Import a single Text File into Multiple Tables minute37 Tables 1 10-15-2008 08:02 AM
Import from text file into multiple tables herbwarri0r Modules & VBA 2 04-21-2008 11:00 PM
Text file to MS Excel multiple file import application tokunbo General 3 07-22-2006 11:08 PM




All times are GMT -8. The time now is 03:13 AM.


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