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"
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.
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.
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
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
The issue was that there was a line of code missing to call the import routine for the LW case.
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"
ImportReport fileName, ReportType
'ImportIW fileName
Case "LW"
Me.subFormData.SourceObject = "table.TblLW"
Me.lblTable.Caption = "LW Table"
'ImportLW fileName
'
[COLOR="Red"][B]ImportReport fileName, ReportType[/B][/COLOR]
End Select
Me.subFormData.Requery
End Sub
However, since the data is now stored in the tables this can be modified.
Code:
Private Sub cmdImport_Click()
Dim fileName As String
Dim ReportType As String
fileName = GetFile
If fileName <> "" Then
ReportType = GetReportType(fileName)
If ReportType <> "" Then
MsgBox "This is a " & ReportType & " report."
Me.subFormData.SourceObject = "table." & GetTableName(ReportType)
Me.lblTable.Caption = ReportType & " Report"
ImportReport (fileName)
Else
MsgBox "Report type not determined", vbInformation, "Undetermined Report"
End If
Me.subFormData.Requery
End If
End Sub
This should work for other similar reports, but there are two ways this will break. Currently it determines a line of data is a line that has as many "columns" as in the Columns table. For IW/OW for example it looks for a line with 9 columns. Then it checks for the first column to be numeric. It just so happens that the only line with 9 columns is the header row or a data row. If the first element of the row is numeric than it is a data row and it imports it. This can break in two ways. Other reports could have rows matching the number of columns that are not header or data rows, or you could have a data row that does not begin with a numeric. If this is the case we will have to come up with some better rule for determining what is and what is not a data row.
Thanks MajP, for the explanation in detailed.
Firstly, apologies for not responding on time, got busy with work.
Secondly, i will go through your codes and understand its significance and how it works and apply to my rest of the reports.
Other reports could have rows matching the number of columns that are not header or data rows, or you could have a data row that does not begin with a numeric. If this is the case we will have to come up with some better rule for determining what is and what is not a data row.
There would be these type of reports and also i have one seen one report where the header is not repetitive on each page. It has header on first page and the rest is all data. but it will break the page as below:
this is just one of the difference. I will first try to close all similar in nature reports and then take on the different types.
Its been pleasure interacting with you and getting to understand the codes. It will be a long way for me to understand the codes and apply this in my other reports, nevertheless i will try and share my feedback.
Thank you once again dear MajP for the help.
have one report where the header is not repetitive on each page. It has header on first page and the rest is all data. but it will break the page as below:
That case should not be a problem since the code does nothing with the header rows
For the other cases we can make the check even more robust. Instead of just checking that the first column is numeric you could use the column information from tblColumns and verify each non text column. In other words if the 3rd and 6th columns are dates and the 7th is a long you could check those 3. If you can find one of these reports send it to me.
The above should work except there are 2 drawbacks. It could make the import much slower and if there is bad data it will reject the row. For example in your LBW report there are bad dates in the form of 04-OAUG-19. Since that is not a date it would reject the whole row. Now it just rejects the one piece of data.
1) based on the report type and the tblColumns it determines how many data elements there should be
2) it reads a line of text
3) It puts that line of text into an array and counts how many data elements
NEW Function below:
4) it then reads the tblColumns and determines which columns should be numeric or dates, and validates each of those columns. For example LBW has
numerics in columns 1 and 6 and dates in 3,4,8. It verifies each of these. So this should be a pretty robust solution
The issue however is bad data. In the LBW case drops complete rows because of the bad date that cannot be converted to a date.
If it was me I would probably add functionality to show all lines that were not imported so you can do a validation. I added that to the form.
Code:
Public Function ValidateColumns(tempArray() As String, ReportType As String) As Boolean
'this will try to validate the tempArray by seeing if the date fields and numeric fields match
Dim rsColumn As DAO.Recordset
Dim colNumber As Integer
Dim dataType As String
Set rsColumn = CurrentDb.OpenRecordset("select * from tblColumns where ReportID_FK = '" & ReportType & "' and DataType <> 'Text'")
Do While Not rsColumn.EOF
dataType = rsColumn!dataType
colNumber = rsColumn!ColumnNumber
Select Case dataType
Case "Long", "Integer", "Currency", "Double"
If Not IsNumeric(tempArray(colNumber - 1)) Then Exit Function
Case "Date"
If Not IsDate(tempArray(colNumber - 1)) Then Exit Function
End Select
rsColumn.MoveNext
Loop
ValidateColumns = True
End Function
HI! MajP,
Thank you so much for the code and make it more simpler. I can now see which items have been dropped from the table.
I tried to import another table tblC_ILR. Most of the text structure is almost same as the previous text.
Updated tblColumns; tblFindAfter & tblReportMeta and tried to import to tblC_ILR.
I am getting below error in module mdlCommonReport on line
But that error comes when the index is not in the collection, so most likely the field name in the tblColumns is spelled wrong
example
so if the name in tblColumns is AcctNo and the field in the import recordset is really AcctNumber
RS_Update.Fields("AcctNo") will throw that error
HI! i have added the code and getting the error on the text field, but i am not able to identify the exact reason.
The Instr_No is a text field and not a Number, will this be an issue.
I have attached the text file and add tblC_ILR to the db
In you tblColumns the column names do not match the import table. Each field in the import table needs a match. There is not a col_ref_no but there is a coll_Code and CCY. These two do not match.
HI! MajP, i am able to get most of the reports except the below which is somewhat similar to my 1st project.
This report is continuation of that project, its called as Validation2
It contains all the data of the 1st report minus the modified transactions and its in different text format
I have attached sample report and its end result in another text
Form1 & Form2.
Can this be made similar way the current project is considered.
I tried to do this on the existing but it requires code changes i guess. Can you help me to code this pls.
