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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2018, 01:31 PM   #16
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

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 is offline   Reply With Quote
Old 07-11-2018, 04:01 AM   #17
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

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
MajP is offline   Reply With Quote
Old 07-11-2018, 06:32 AM   #18
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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 am referring to your response in post#16 below
PHP Code:
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_IDCould this table be called TblValidationIt 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(MMCcode 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 Code:
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.
Attached Images
File Type: png image3.png (45.7 KB, 11 views)

lookforsmt is offline   Reply With Quote
Old 07-11-2018, 07:12 AM   #19
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

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 ?)
MajP is offline   Reply With Quote
Old 07-11-2018, 07:48 AM   #20
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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 for the understanding.

Concatenate only the Warning field pls. The others should be in separate fields.
lookforsmt is offline   Reply With Quote
Old 07-11-2018, 09:38 AM   #21
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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!

PHP Code:
Can all of the cases be duplicated? (Rej ReasonExceptionErrorWarning ?) 
I checked the entire data and did not find duplicates other than "warning"
lookforsmt is offline   Reply With Quote
Old 07-12-2018, 12:59 PM   #22
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

I will post an update to the database. I did some code clean up, which will make it a little easier to follow. This is the first time trying to extract data from a text file so there may be some smarter ways if anyone has recommendations.

Here is the main procedure which just reads a file line by line looking for key lines. Once it finds the line it does standard string manipulation to get the data elements. It calls the helper functions to aid in getting the data elements. Once it gets the correct data it calls insert the functions that make 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 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)
       TheMOM_MMC = Trim(Split(TheMOM_Txt, "-")(0))
      ' Debug.Print "Mom " & TheMOM_MMC
      ' The next line is Department Name
       Line Input #intFile, StrIn
      'Debug.Print strIn
      TheDepartment = FindAfter(StrIn, "Department Name:")
      'Debug.Print TheDepartment
      InsertSegment TheSegment, ThePrintDate, ThePage, TheMOM_ID, TheMOM_Txt, TheMOM_MMC, TheDepartment
    End If
    If 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)
         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
These are the helper functions that reduce repetitive tasks on string an array manipulations.
Code:
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
    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
   Debug.Print strDate
   aDate = Split(strDate, "-")
   SQL_Date = "#" & aDate(1) & "/" & aDate(0) & "/" & aDate(2) & "#"
   'Debug.Print SQL_Date
End Function
This is the code for the two insert queries. The code uses the helper functions to format the strings and date.

Code:
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
  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
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
  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 = Replace(TheAmount, ",", "")
  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
End Sub

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-12-2018)
Old 07-12-2018, 01:25 PM   #23
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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 tested the db with higher data and encountered the below error. I was trying to find out the mis-match which could help to reslove this issue:

PHP Code:
Runtime error 3134
Syntax error in Insert into statement

It highlighted on 
"CurrentDB.Execute strSql" 
table1 imported 379 of 2413 lines. Thats because MOM_ID is AutoNumber. But this is fine. I am checking if it has missed any MOM_ID.

table2: 1st data available on page 431 and imported data till page 1067. Total 149 records imported between these (431 to 1067)pages, whereas there are more than 180+ records. I was trying to figure out from the data but unable to identify.

There are records on page 1068, but it did not import records after this page.

Any particular reason
lookforsmt is offline   Reply With Quote
Old 07-12-2018, 01:30 PM   #24
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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 for the coding and explanation in post#23. I am checking why the data after page 1068 is not getting picked up till the end
lookforsmt is offline   Reply With Quote
Old 07-12-2018, 01:50 PM   #25
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with text file trim and create 2 tables

Further observations:
table1: field MOM_ID is split into two fields (MOM_ID & MOM_TXT) whereas MOM_MMC is showing same details as MOM_TXT. KInldy note it should populate data after "Department Name" which is 3 length character based in the center. (Kindly refer to the earlier image posted in post#18.
Although your db uploaded is showing a pefect details. I may have given dummy names so changing them to the actual original names matching witht the notepad report.
lookforsmt is offline   Reply With Quote
Old 07-12-2018, 02:27 PM   #26
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

With the MOM_MMC, I may have cheated based on the example. In the example all MOM_Txt has a "-" in the name. My code was based on that, and will fail if not.
I get this
Code:
MOM_TXT	MOM_MMC
ABC - Test1	    ABC
PQR - Test25	    PQR
MNO - Test50	    MNO
PQR - Test25	    PQR
MNO - Test50	    MNO
PQR - Test25	   PQR
I split the MOM_ID based on "-". But if there is no "-" then it will not work. I will modify the code to look instead after the department name.
I will send a new database with error checking in it. When the code fails it then will print to the immediate window the Sql string. I will need you to send it back to me so I can see what it does not like.

Quote:
There are records on page 1068, but it did not import records after this page.
Any particular reason
Once it failed on the error, it does not go any further. I will put in a resume next so it continues on. However, we will then have to look at all of the errors in the debug window to see what was missed. That is the reason I did not put in error checking, because want to understand where it fails and not keep going.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-12-2018)
Old 07-12-2018, 10:30 PM   #27
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with text file trim and create 2 tables

Sure MajP, i will do. Now i understand the logic why you had put the check if it fails. However, i have to admit it will be long way for me to understand the basic and what the code does. You have explained until now step by step and above all you have great sense of patient with novice like me.
lookforsmt is offline   Reply With Quote
Old 07-13-2018, 05:07 AM   #28
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

I also made the assumption that S_No, Instrument Number where real numbers and Amount was a currency. If you do not need these stored that way it will be easier to store them as strings. That way if there are exceptions such as sometimes the values are not real numbers (ex. "NA") then they will still get inserted. Howerver, if you need to do real number stuff like sum the amount or sort the S_No then you want them stored as real numbers. An example of a number not really a number is a Serial number, phone number, social security number. Just because it looks like 1234789, you are never going to do math on it so better off storing as a string most of the time.
MajP is offline   Reply With Quote
Old 07-13-2018, 07:06 AM   #29
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
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 MajP, i will try the updated db and give you my feedback. I was trying to identify the error that came on the previous db. I came across below data in the text file which appears to be inconsistent due to which it gives an error. I changed it to how it should look and it went on smoothly till the end without giving error.

I am posting the snap shot of the same text which were in 3 different ways. The below text is placed inconsistent on 4 places and 3 of them were different

PHP Code:
 S.No Instrument Number  Amount               Error Description                    
I think it looks for the below text after it reads the above text and if it does not find then gives an error. Is it possible to read the below independently. But i need to check if that data is also consistent across the text file.

PHP Code:
---------------------------------------------------------------------------------------------------------------------------------
 
1                 10            2,000.00     Warning 
Unfortunate, i will not be able to try your new DB as i am at home till Sunday and i cannot download your updated db on my office laptop, but i will try to go to office tomorrow to download the file. I have tried on my home laptop on the earlier replicated text file and it works fine.

I tried on the attached snapshot scenarios on new db and it gave me the below error. As you mentioned i checked the immediate window and got the below error
PHP Code:
3134 Syntax error in INSERT INTO statement
Insert Into Table2(MOM_ID_FKReference_NoReference_Name,S_No,Instrument_Number,Amount,Reject_ReasonWarning,ExceptionError_MsgInstrument_rejectedNo_Exc_No_WarnVALUES Null'1234567890999''ABC Co Ltd', , , , NullNullNullNullFalseFalse)
3134 Syntax error in INSERT INTO statement
Insert Into Table2(MOM_ID_FKReference_NoReference_Name,S_No,Instrument_Number,Amount,Reject_ReasonWarning,ExceptionError_MsgInstrument_rejectedNo_Exc_No_WarnVALUES Null'1234567890567''HAT', , , , NullNullNullNullFalseFalse
I have seen you have seprated SOL_ID; SOL_TXT & SOL_MMC in different field.

PHP Code:
I also made the assumption that S_NoInstrument Number where real numbers and Amount was a currency. If you do not need these stored that way it will be easier to store them as stringsThat way if there are exceptions such as sometimes the values are not real numbers (ex"NA"then they will still get insertedHowerver, if you need to do real number stuff like sum the amount or sort the S_No then you want them stored as real numbersAn example of a number not really a number is a Serial numberphone numbersocial security numberJust because it looks like 1234789you are never going to do math on it so better off storing as a string most of the time
I have checked this across the data. I would require the amount field to calculate the sum, it will not have NA since if there is no data it will be only the 1st part which is captured in table1.

I will try to check this by tomorrow at office and give you my feedback
Attached Files
File Type: txt Sample_Error_1.txt (7.3 KB, 6 views)
lookforsmt is offline   Reply With Quote
Old 07-13-2018, 07:21 AM   #30
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,806
Thanks: 34
Thanked 547 Times in 519 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help with text file trim and create 2 tables

Code:
 S.No Instrument Number  Amount               Error Description                    |
---------------------------------------------------------------------------------------------------------------------------------
                                                        xxxxxxx yyy
                                                        IXXXXX CXXXXXXX Zone VALIDATION RUN
                                                    Interxxxxxxx Rxxxx List as on   04-07-2018
This looks like the report has broken in the wrong place. I would expect on the third line to see an S_No, Instrument Number and Amount, but it looks like it is starting on a new report. I have a fix that in this case you get a record but it only has the reference number, name, and the foreign key to the MOM_ID.

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 11:58 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