Read a file and import its data into a table

Okie :) got it. Onto something ;)
 
managed to make the form to what i've intented. Attached the same here. I've read the first line and it reads very well. Now how can i read the next Line. Looping is one option but the characters in the line changes from line to line shown here
Code: 
TextLine, 62, 7
 
The first if statement works as u've showed but when adding the second it does not work no error codes are returned. Is the below code correct ?
Code: 
Option Compare Database

Private Sub cmd1_Click()
Dim TextLine As String
Dim IATACode As Integer
Dim MIRDate As String
Dim IssueAirline As String
Dim DateOfTravel As String
Dim InputOutputGTID As String
Dim BkgTkgPCC As String
Dim IATAnumber As String
Dim PNR As String
Dim BkgTkgSignOn As String
Dim PNRDate As String

Open "c:\test.MIR" For Input As #1 'Open specified file

   Line Input #1, TextLine 'Start reading line
   
 'Reading line "T5"(Line 1)
If Left(TextLine, 2) = "T5" Then
    IATACode = Mid(TextLine, 5, 4) 'Reads the IATA Code
    MIRDate = Mid(TextLine, 21, 11) 'Reads MIR Creation date and time
    IssueAirline = Mid(TextLine, 38, 24) 'Reads the Issuing AirLine
    DateOfTravel = Mid(TextLine, 62, 7) 'Reads Date of travel
    InputOutputGTID = Mid(TextLine, 69) 'Reads Input and Output GTID
   
   'Copying variable data to Text boxes
   txtIATA.Value = IATACode
   txtMIRDate.Value = MIRDate
   txtIssueAirline.Value = IssueAirline
   txtDateOfTravel.Value = DateOfTravel
   txtIOGTID.Value = InputOutputGTID
   End If
 'Reading line "6VQ"(Line 2)
If Left(TextLine, 3) = "6VQ" Then
    BkgTkgPCC = Mid(TextLine, 1, 8) 'Booking and ticketing PCC respectively
    IATAnumber = Mid(TextLine, 9, 7) 'Reads IATA number assinged by BSP
    PNR = Mid(TextLine, 18, 15) 'Reads PNR
    BkgTkgSignOn = Mid(TextLine, 34, 10) 'Reads Booking and Ticketing SignOn
    PNRDate = Mid(TextLine, 44, 7) 'Reads PNR creation data
   
   'Copying variable data to Text boxes
   txtBookingPCC.Value = BkgTkgPCC
   txtIATANumber.Value = IATAnumber
   txtPNR.Value = PNR
   txtBookingSignOn.Value = BkgTkgSignOn
   txtPNRDate.Value = PNRDate
   End If
   
Close #1   ' Close file.

End Sub
 
Indentations we do for a reason, keeping them clean helps with keeping your code readable thus maintainable....

Where did the do while disappear to? This takes care of reading the file, line by line.... Since you dont have any do while, it wont read the next line thus not trigger your second if....

Code: 
Option Compare Database

Private Sub cmd1_Click()
    Dim TextLine As String
    Dim IATACode As Integer
    Dim MIRDate As String
    Dim IssueAirline As String
    Dim DateOfTravel As String
    Dim InputOutputGTID As String
    Dim BkgTkgPCC As String
    Dim IATAnumber As String
    Dim PNR As String
    Dim BkgTkgSignOn As String
    Dim PNRDate As String
    
    Open "c:\test.MIR" For Input As #1 'Open specified file

    Line Input #1, TextLine 'Start reading line
   
    'Reading line "T5"(Line 1)
    If Left(TextLine, 2) = "T5" Then
        IATACode = Mid(TextLine, 5, 4) 'Reads the IATA Code
        MIRDate = Mid(TextLine, 21, 11) 'Reads MIR Creation date and time
        IssueAirline = Mid(TextLine, 38, 24) 'Reads the Issuing AirLine
        DateOfTravel = Mid(TextLine, 62, 7) 'Reads Date of travel
        InputOutputGTID = Mid(TextLine, 69) 'Reads Input and Output GTID
       
        'Copying variable data to Text boxes
        txtIATA.Value = IATACode
        txtMIRDate.Value = MIRDate
        txtIssueAirline.Value = IssueAirline
        txtDateOfTravel.Value = DateOfTravel
        txtIOGTID.Value = InputOutputGTID
    End If
    'Reading line "6VQ"(Line 2)
    If Left(TextLine, 3) = "6VQ" Then
        BkgTkgPCC = Mid(TextLine, 1, 8) 'Booking and ticketing PCC respectively
        IATAnumber = Mid(TextLine, 9, 7) 'Reads IATA number assinged by BSP
        PNR = Mid(TextLine, 18, 15) 'Reads PNR
        BkgTkgSignOn = Mid(TextLine, 34, 10) 'Reads Booking and Ticketing SignOn
        PNRDate = Mid(TextLine, 44, 7) 'Reads PNR creation data
       
       'Copying variable data to Text boxes
       txtBookingPCC.Value = BkgTkgPCC
       txtIATANumber.Value = IATAnumber
       txtPNR.Value = PNR
       txtBookingSignOn.Value = BkgTkgSignOn
       txtPNRDate.Value = PNRDate
    End If
   
    Close #1   ' Close file.

End Sub

Also you seem to be writing to controls (on a form?) ? I thought your intent was to save the data into a table?
 
The reason am not adding to the database table is, like you said learning to walk before i run:)

here is the code after the do while statement, but its still not going to the next line.
Code: 
Option Compare Database

Private Sub cmd1_Click()
    Dim TextLine As String
    Dim IATACode As Integer
    Dim MIRDate As String
    Dim IssueAirline As String
    Dim DateOfTravel As String
    Dim InputOutputGTID As String
    Dim BkgTkgPCC As String
    Dim IATAnumber As String
    Dim PNR As String
    Dim BkgTkgSignOn As String
    Dim PNRDate As String
    
    Open "c:\test.MIR" For Input As #1 'Open specified file
    Do While Not EOF(1)
        Line Input #1, TextLine 'Start reading line
        
'Reading line "T5"(Line 1)
        If Left(TextLine, 2) = "T5" Then
            IATACode = Mid(TextLine, 5, 4) 'Reads the IATA Code
            MIRDate = Mid(TextLine, 21, 11) 'Reads MIR Creation date and time
            IssueAirline = Mid(TextLine, 38, 24) 'Reads the Issuing AirLine
            DateOfTravel = Mid(TextLine, 62, 7) 'Reads Date of travel
            InputOutputGTID = Mid(TextLine, 69) 'Reads Input and Output GTID
            
'Copying variable data to Text boxes
            txtIATA.Value = IATACode
            txtMIRDate.Value = MIRDate
            txtIssueAirline.Value = IssueAirline
            txtDateOfTravel.Value = DateOfTravel
            txtIOGTID.Value = InputOutputGTID
        End If
'Reading line "6VQ"(Line 2)
        If Left(TextLine, 3) = "6VQ" Then
            BkgTkgPCC = Mid(TextLine, 1, 8) 'Booking and ticketing PCC respectively
            IATAnumber = Mid(TextLine, 9, 7) 'Reads IATA number assinged by BSP
            PNR = Mid(TextLine, 18, 15) 'Reads PNR
            BkgTkgSignOn = Mid(TextLine, 34, 10) 'Reads Booking and Ticketing SignOn
            PNRDate = Mid(TextLine, 44, 7) 'Reads PNR creation data
            
'Copying variable data to Text boxes
            txtBookingPCC.Value = BkgTkgPCC
            txtIATANumber.Value = IATAnumber
            txtPNR.Value = PNR
            txtBookingSignOn.Value = BkgTkgSignOn
            txtPNRDate.Value = PNRDate
            
        End If
Loop
Close #1   ' Close file
        
    End Sub
 
After reading the first line, it should pickup the second line when passing thru the Line Input ....

Code: 
    Do While Not EOF(1)
        Line Input #1, TextLine 'Start reading line
' try adding the following line here:
        debug.print TextLine
 
It still does not read the next line and returns no error
Code: 
Do While Not EOF(1)
        Line Input #1, TextLine 'Start reading line
        Debug.Print TextLine
'Reading line "T5"(Line 1)
        If Left(TextLine, 2) = "T5" Then
 
Aha, your file is a unix file is it? i.e. it only has linefeeds and not char returns

Result will be that the file is read as one long line, instead of seperate lines.
 
Looking at your test file that you uploaded at the start of this thread, your second line doesnt start with "6VQ", it starts with " 6VQ"

Which then offcourse is 4 characters instead of 3, or use the trim function to remove the space(s) from your line.
 
That is because the space is reserved for the PCC which is normally 4 characters long. " 6SQ" is one of those PCC which is just 3 characters long.

Moreover your code read line by line which was correct why not now.
 
It does read line by line, but your IF is out of place... it is looking for "6VQ" instead of " 6VQ"

Try using
If Left(TextLine, 3) = " 6VQ" Then
or
If Left(Trim(TextLine), 3) = "6VQ" Then

It is either that space that is the issue or (yet unanwered) it is a unix file.
 
Nope, that does not work either. I've tried changing the line from 6VQ to A02. That also did not work either. No errors returned.

It has to be something else as it was working on the code you had shown earlier.
 
Note: I left both lines as 3 characters where the first line (obiously) should be 4

I downloaded your file from your first post, took your code from post #66 and changed it ever so slightly removing mostly the textboxes...

Code: 
Option Compare Database


Private Sub x()
    Dim TextLine As String
    Dim IATACode As Integer
    Dim MIRDate As String
    Dim IssueAirline As String
    Dim DateOfTravel As String
    Dim InputOutputGTID As String
    Dim BkgTkgPCC As String
    Dim IATAnumber As String
    Dim PNR As String
    Dim BkgTkgSignOn As String
    Dim PNRDate As String
    
    Open "H:\desktop\test.txt" For Input As #1 'Open specified file
    Do While Not EOF(1)
        Line Input #1, TextLine 'Start reading line
        
'Reading line "T5"(Line 1)
        If Left(TextLine, 2) = "T5" Then
            IATACode = Mid(TextLine, 5, 4) 'Reads the IATA Code
            MIRDate = Mid(TextLine, 21, 11) 'Reads MIR Creation date and time
            IssueAirline = Mid(TextLine, 38, 24) 'Reads the Issuing AirLine
            DateOfTravel = Mid(TextLine, 62, 7) 'Reads Date of travel
            InputOutputGTID = Mid(TextLine, 69) 'Reads Input and Output GTID
            
'Copying variable data to Text boxes
            MsgBox IATACode & vbNewLine & _
                   MIRDate & vbNewLine & _
                   IssueAirline & vbNewLine & _
                   DateOfTravel & vbNewLine & _
                   InputOutputGTID
        End If
'Reading line "6VQ"(Line 2)
        If Left(TextLine, 4) = " 6VQ" Then
            BkgTkgPCC = Mid(TextLine, 1, 8) 'Booking and ticketing PCC respectively
            IATAnumber = Mid(TextLine, 9, 7) 'Reads IATA number assinged by BSP
            PNR = Mid(TextLine, 18, 15) 'Reads PNR
            BkgTkgSignOn = Mid(TextLine, 34, 10) 'Reads Booking and Ticketing SignOn
            PNRDate = Mid(TextLine, 44, 7) 'Reads PNR creation data
            
'Copying variable data to Text boxes
            MsgBox BkgTkgPCC & vbNewLine & _
                   IATAnumber & vbNewLine & _
                   PNR & vbNewLine & _
                   BkgTkgSignOn & vbNewLine & _
                   PNRDate
            
        End If
Loop
Close #1   ' Close file
        
    End Sub
Seems to work for me
 
Strange !

Just tried your code and it just showed only one msg box. Shouldn't it show the next one automatically.
 
It should....

First shows
7733
11JUN140930
EMIRATES
12NOV14
E265E3BC1387

Second
6VQ 6VQ
9999999
JFLQHH
N3438N38AG
11JUN14

With the debug.print added after the line input, how does the output look in the immediate window? (if you cant see the Immediate window, open it by pressing CTRL+G)
 
OK got it working.

Now the second line that says 6VQ changes from file to file. it has character count of 4. So if i were to read another file the second line read would fail. Is there any other option to accomplish this.

And thanks namliam for baring with me.
 
Well you can make one for "6VQ" and one for " 6VQ" is that what you mean?
 
That is not what i meant. Let me share the MIR structure with you in short.

The MIR file is generated by different offices and this 6VQ is just once office code. different offices have a unique code. So this will change from file to file and it does not have more than 4 characters and not less than 3 characters. so that line to identify it is not correct. That line is identified by the T5 at the beginning.
 
Aha, so what you are saying is the second line is NOT really an identifyable line...
It is simply the line after the one with T5 ?

Unidentifiable lines like that are "messy" but can be dealt with something like ...
Code: 
Option Compare Database


Private Sub x()
    Dim TextLine As String
    Dim IATACode As Integer
    Dim MIRDate As String
    Dim IssueAirline As String
    Dim DateOfTravel As String
    Dim InputOutputGTID As String
    Dim BkgTkgPCC As String
    Dim IATAnumber As String
    Dim PNR As String
    Dim BkgTkgSignOn As String
    Dim PNRDate As String
    
    Open "H:\desktop\test.txt" For Input As #1 'Open specified file
    Do While Not EOF(1)
        Line Input #1, TextLine 'Start reading line
        
'Reading line "T5"(Line 1)
        If Left(TextLine, 2) = "T5" Then
            IATACode = Mid(TextLine, 5, 4) 'Reads the IATA Code
            MIRDate = Mid(TextLine, 21, 11) 'Reads MIR Creation date and time
            IssueAirline = Mid(TextLine, 38, 24) 'Reads the Issuing AirLine
            DateOfTravel = Mid(TextLine, 62, 7) 'Reads Date of travel
            InputOutputGTID = Mid(TextLine, 69) 'Reads Input and Output GTID
            
'Copying variable data to Text boxes
            MsgBox IATACode & vbNewLine & _
                   MIRDate & vbNewLine & _
                   IssueAirline & vbNewLine & _
                   DateOfTravel & vbNewLine & _
                   InputOutputGTID
            ' get the office information
            Line Input #1, TextLine 'Start reading line
            BkgTkgPCC = Mid(TextLine, 1, 8) 'Booking and ticketing PCC respectively
            IATAnumber = Mid(TextLine, 9, 7) 'Reads IATA number assinged by BSP
            PNR = Mid(TextLine, 18, 15) 'Reads PNR
            BkgTkgSignOn = Mid(TextLine, 34, 10) 'Reads Booking and Ticketing SignOn
            PNRDate = Mid(TextLine, 44, 7) 'Reads PNR creation data
            
'Copying variable data to Text boxes
            MsgBox BkgTkgPCC & vbNewLine & _
                   IATAnumber & vbNewLine & _
                   PNR & vbNewLine & _
                   BkgTkgSignOn & vbNewLine & _
                   PNRDate

        End If
    Loop
    Close #1   ' Close file
        
End Sub
Key here is to start from something that IS identifiable, i.e. T5
 
You must log in or register to reply here.

Similar threads

I
Get a field ID
Replies
3
Views
606
Isaac
Isaac
M
Importing PowerQuery results into SQL Server
Replies
3
Views
262
arnelgp
arnelgp
T
  • Question Question
Advice needed please. Importing Sharepoint Text file into List and Deleting previous days data
Replies
4
Views
799
Isaac
Isaac
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
757
oxicottin
oxicottin
brucemc777
Solved Can one import a named table from Excel into either a new table, or an empty table created for the purpose, in Access?
Replies
1
Views
499
azhar2006
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom