Import Text file to access database

[anishkgt]

Hi All,

i am totally new to VBA coding. I've only used macro for my work with access2013. Now i have a requirement for importing a text file into the accessdb.



I've managed to read the text file (with some help for the coding) and display it on a form. The text is not clearly formated(attached). I wish to automate the import process from a folder C:\MIR\ into a table.

    Option Compare Database
    
    Private Sub cmd1_Click()
        
        Dim TextLine As String
        Dim MIR As String
        Dim IATACode As String
        Dim MIRDate As String
        Dim IssueAirline As String
        Dim ALC As String
        Dim AL2C As String
        Dim DateOfTravel As String
        Dim InputGTID As String
        Dim OutputGTID As String
        Dim BkgTkgPCC As String
        Dim IATAnumber As String
        Dim PNR As String
        Dim BkgSignOnTkgSignOn As String
        Dim PNRDate As String
        Dim Passenger As String
        Dim pax As String
        Dim AirSector As String
        Dim sectors As String
        Dim BaseFare As String
        Dim BFC As String
        Dim TotalFare As String
        Dim ActualAMTCol As String
        Dim Tax As String
        Dim Tax1 As String
        Dim PhoneField As String
        Dim FOP As String
        
    If IsNull(txtMIRPath) = True Then
    MsgBox ("Check MIR file path")
    Exit Sub
    End If
    MIR = [Forms]![form1]![txtMIRPath] & ".MIR"
        DoCmd.Hourglass True
        Open MIR For Input As #1 'Open specified file
        Do While Not EOF(1)
            Line Input #1, TextLine
            
            If Left(TextLine, 2) = "T5" Then
                
                IATACode = Trim(Mid(TextLine, 5, 4)) 'Reads the IATA Code
                MIRDate = Trim(Mid(TextLine, 21, 7)) 'Reads MIR Creation date and time
                IssueAirline = Trim(Mid(TextLine, 38, 24)) 'Reads the Issuing AirLine
                ALC = Trim(Mid(TextLine, 35, 3)) 'Reads Airline Code
                AL2C = Trim(Mid(TextLine, 33, 2)) 'Reads Airline two letter code
                DateOfTravel = Trim(Mid(TextLine, 62, 7)) 'Reads Date of travel
                InputGTID = Trim(Mid(TextLine, 69, 6)) 'Reads InputGTID
                OutputGTID = Trim(Mid(TextLine, 75, 6)) 'Reads Output GTID
                
                txtIATA.Value = IATACode
                txtMIRDate.Value = MIRDate
                txtIssueAirline.Value = ALC & " " & AL2C & " " & IssueAirline
                txtDateOfTravel.Value = DateOfTravel
                txtIOGTID.Value = InputGTID & " " & OutputGTID
                
                Line Input #1, TextLine 'Start reading next line
                BkgTkgPCC = Trim(Mid(TextLine, 1, 4)) & " " & Trim(Mid(TextLine, 5, 4)) 'Booking and ticketing PCC respectively
                IATAnumber = Trim(Mid(TextLine, 9, 7)) 'Reads IATA number assinged by BSP
                PNR = Trim(Mid(TextLine, 18, 15)) 'Reads PNR
                BkgSignOnTkgSignOn = Trim(Mid(TextLine, 33, 6)) & " " & _
                Trim(Mid(TextLine, 39, 1)) & " " & _
                Trim(Mid(TextLine, 40, 2)) & " " & _
                Trim(Mid(TextLine, 42, 2)) 'Reads Booking and Ticketing SignOn
                PNRDate = Trim(Mid(TextLine, 44, 7)) 'Reads PNR creation data
                txtBookingPCC.Value = BkgTkgPCC
                txtIATANumber.Value = IATAnumber
                txtPNR.Value = PNR
                txtBookingSignOn.Value = BkgSignOnTkgSignOn
                txtPNRDate.Value = PNRDate
            End If
            
            If Left(TextLine, 3) = Trim("A02") Then 'First A02(PAX) line
                pax = Trim(Mid(TextLine, 4, 30))
                TKT = ALC & Trim(Mid(TextLine, 49, 10))
                Passenger = TKT & " " & pax
                Line Input #1, TextLine
                Line Input #1, TextLine
                
                Do While Left(TextLine, 3) = ("A02") 'Loops through following A02 (Pax) lines (if any)
                    pax = Trim(Mid(TextLine, 4, 30))
                    TKT = ALC & Trim(Mid(TextLine, 49, 10))
                    Passenger = Passenger & vbNewLine & TKT & " " & pax
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If
            txtTKT.Value = Passenger
            
            If Left(TextLine, 3) = "A04" Then  'Reads First A04(Secotor) Line
                sectors = Trim(Mid(TextLine, 50, 13)) & "-" & Trim(Mid(TextLine, 66, 13))
                AirSector = sectors
                Line Input #1, TextLine
                
                Do While Left(TextLine, 3) = "A04" 'Loops through following A04(Sectors) lines (if any)
                    sectors = Trim(Mid(TextLine, 50, 13)) & "-" & Trim(Mid(TextLine, 66, 13))
                    AirSector = AirSector & vbNewLine & sectors
                    Line Input #1, TextLine
                Loop
            End If
            txtSector.Value = AirSector
            
            If Left(TextLine, 3) = "A07" Then 'Reads base fare, Total fare and 5 Tax's
                    BaseFare = Trim(Mid(TextLine, 9, 12))
                    BFC = Trim(Mid(TextLine, 6, 3)) & ": "
                    EQAmt = Trim(Mid(TextLine, 39, 12))
                    TotalFare = Trim(Mid(TextLine, 24, 12))
                    Tax = Trim(Mid(TextLine, 57, 8)) & "+" & Trim(Mid(TextLine, 70, 8)) & "+" & Trim(Mid(TextLine, 83, 8)) & "+" & Trim(Mid(TextLine, 96, 8)) & "+" & Trim(Mid(TextLine, 109, 8))
            End If
            txtBaseFare.Value = BFC & BaseFare & "/-"
            txtEqamt.Value = "QAR: " & EQAmt & "/-"
            txtTax1.Value = "QAR: " & Tax & "/-"
            txtTotalFare.Value = "QAR: " & TotalFare & "/-"
            
            If Left(TextLine, 3) = "A11" Then 'Reads Form of payment
                FOP = Trim(Mid(TextLine, 4, 2))
                ActualAMTCol = Trim(Mid(TextLine, 6, 12))
                txtFOP.Value = FOP
                txtActualAmt.Value = "QAR: " & ActualAMTCol
                Line Input #1, TextLine
            End If
            txtFOP.Value = FOP
            
             If Left(TextLine, 3) = "A12" Then 'Reads Phone field
                PhoneField = Trim(Mid(TextLine, 10, 50))
                phoneFieldCont = PhoneField
                Line Input #1, TextLine

                Do While Left(TextLine, 3) = "A12" 'Reads (any) following Phone fields
                    PhoneField = Trim(Mid(TextLine, 10, 50))
                    Line Input #1, TextLine
                    phoneFieldCont = phoneFieldCont & vbNewLine & PhoneField
                Loop
                txtPhoneField.Value = phoneFieldCont
            End If
        Loop
        DoCmd.Hourglass False
        Close #1   ' Close file
    End Sub

what would be the simplest way to import these fields into a table

[ranman256]

Manually import the text file, in the 'import screen' ,bottom left corner is ADVANCED
define your text as an IMPORT SPEC.
SAVE this spec.

in the import button onClick event
the event would be:

DoCmd.TransferText acImportDelim, SPECname, Table, vFile, True

[anishkgt]

Sorry runman ! idid not understand what you meant. Which import screen are u referring to ?

[anishkgt]

ok got it but it does not allow me skip characters, i believe it has to be continuous letters. any idea how i can go with this.

[anishkgt]

Hi Ranman,

i do not want to read the file again, i've already done that with the code mentioned above. I just need to import those fields into the table attached.