Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import to Table (A2000)

    With the attached document, does anyone recognise it's delimited ??? format.

    I have been asked to decifer it enough to pull it into a table.
    Each time I try (change extension from EST to TXT) the results and fields are all over the place.

    If anyone has any success with it, I'd be greatful for some tips on it's structure.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    If it helps, here is a report generated via it's residing program:
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    It does not have a real table structure. It is a text file with | as delimiter, and probably Ascii character 10 or 13 as record separator. I have attached a version imported into Excel using | as delimiter.
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Thats pretty much as I got it also.

    Column A seems to be confusing though, could it be XML or something ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    No, it's not XML, it's not hierarchic (the codes are not nested), just a kind of coding system. As far as I can see, codes with V stand for Vehicle, codes with P for Parts, etc. You will no doubt be able to make a better guess than I can.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Aaaaaaaaahhh

    E = Estimate
    O = Owner
    I = Insurer
    P = Parts etc etc etc .


    Thanks Hans

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Hans

    Is it possible to return the value directly after <E3>.
    This seems to be the link (Vehicle Registration)to the estimate and the Vehicle.

    If I have this value, I can tie the details with the car.

    I'll be using this in a VB project, but since this thread started here, I thought it should continue unles you decide to move it over.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    Put the following function into a general module:

    Public Function GetCarRegistration(strFile) As String
    Dim ff As Integer
    Dim strLine As String
    Dim intPos1 As Integer
    Dim intPos2 As Integer
    Dim intPos3 As Integer

    On Error GoTo ErrHandler

    ff = FreeFile
    Open strFile For Input As #ff
    Line Input #1, strLine
    intPos1 = InStr(strLine, "<E3>")
    If intPos1 > 0 Then
    intPos2 = InStr(intPos1 + 4, strLine, "|")
    If intPos2 > 0 Then
    intPos3 = InStr(intPos2 + 1, strLine, Chr(10))
    If intPos3 > 0 Then
    GetCarRegistration = Mid(strLine, intPos2 + 1, intPos3 - intPos2 - 1)
    End If
    End If
    End If

    ExitHandler:
    Close #ff
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    Use it like this (with the appropriate path and file name substituted):

    Dim strReg As String
    strReg = GetCarRegistration("C:ImportCOR_CENST1301_00000915 .EST")
    If strReg = "" Then
    MsgBox "No registration found in file"
    Else
    ...
    End If

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Hans
    Does this work with you.
    I get "No registration found in file" every time.

    <pre>Dim strReg As String
    strReg = GetCarRegistration(Me.txtPath & Me.txtFile)
    If strReg = "" Then
    MsgBox "No registration found in file"
    Else
    '...
    End If</pre>


    txtPath and txtFile substituted for the path.
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    Could it be that the file is in use (you display it on the form at the same time)? The code works for me, at least with the file you attached earlier.
    Attached Images Attached Images
    • File Type: png x.png (1.6 KB, 0 views)

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    <pre> intPos1 = InStr(strLine, "<E3>")</pre>


    Looks like HTML strikes again <<<<< & lt;??

    Thanks Hans

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    To avoid confusion, I have attached the code in a text file.
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Hans

    Is it possible for an explanation of the code or even comment it?
    I may expand it to gather other information from the EST file.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table (A2000)

    I have attached a commented version.
    Attached Files Attached Files

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table (A2000)

    Thanks Hans

    Thats extremely useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •