Read a file and import its data into a table (3 Viewers)

A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Now for A04 there are multiple lines strating by the same
Code: 
A0401AA001AMERICAN AIR7975S HK18DEC0800 1340 2DOHDOHA         ORDCHICAGO/O HARIN    X0   777    07128F AC:QATAR AIRWAYTK:YJT:14.40ANL:AMERICAN AIRLINES       ACL:QATAR AIRWAYS                           DDL:18DEC14
A0402AA001AMERICAN AIR3177S HK18DEC1820 2105 2ORDCHICAGO/O HARSATSAN ANTONIO  DN    O0   CR7 T3 01043F AC:ENVOY AIR ASTK:YJT:02.45ANL:AMERICAN AIRLINES       ACL:ENVOY AIR AS AMERICAN EAGLE             DDL:18DEC14
A0403AA001AMERICAN AIR3137S HK01JAN1400 1715 2DENDENVER       ORDCHICAGO/O HARDN    X0   CR7    00887F AC:ENVOY AIR ASTK:YJT:02.15ANL:AMERICAN AIRLINES       ACL:ENVOY AIR AS AMERICAN EAGLE             DDL:01JAN15
A0404AA001AMERICAN AIR7974S HK01JAN1925 1740 3ORDCHICAGO/O HARDOHDOHA         IN    O0   777 T3 07128F AC:QATAR AIRWAYTK:YJT:13.15ANL:AMERICAN AIRLINES       ACL:QATAR AIRWAYS                           DDL:01JAN15
how can i only pick characters from the first A04 Line
Code: 
Mid(TextLine, 47, 26)
Used the above but its reading the last line of the A04 Lines.Need do the same for the other A04 lines.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
This sounds like a subtable , you want to pick up each of the stops (I think it is)
Again you dont have a unique thing here so you start from your unique first A04, then within the IF use additional Line Input commands to read and subsequently process each individual line of A04
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Not sure if i need to pick up the stops, sometime between them also.
Code: 
       If Left(TextLine, 3) = "A04" Then
       Line Input #1, TextLine
            sector = Mid(TextLine, 47, 26)
            txtSector.Value = sector
        End If
Still the result is the same.
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Aite got it figured out after a little R&D
Code: 
If Left(TextLine, 3) = "A04" Then
            sector1 = Mid(TextLine, 47, 34)
            Line Input #1, TextLine
            sector2 = Mid(TextLine, 47, 34)
            Line Input #1, TextLine
            sector3 = Mid(TextLine, 47, 34)
            Line Input #1, TextLine
            sector4 = Mid(TextLine, 47, 34)
            Line Input #1, TextLine
            txtSector.Value = sector1 & vbNewLine & _
                              sector2 & vbNewLine & _
                              sector3 & vbNewLine & _
                              sector4 & vbNewLine & _
                              sector5 & vbNewLine & _
                              sector6
        End If
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Now A02 like A04 has a few more lines but need to omit the lines between each A02 line
Code: 
A02CARPENTER/ETHANJAMESMSTR         239495638044526715622702         C09   01  Y
NR:P-C09                            TL:27AUG14C35:N
A02CARPENTER/JOSEDAVIDMSTR          239495639004526715622902         C10   02  Y
NR:P-C10                            TL:27AUG14C35:N
To read the above, i've coded it as
Code: 
If Left(TextLine, 3) = "A02" Then 'First A02 line
            pax1 = Mid(TextLine, 4, 33)
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Second A02 Line
            pax2 = Mid(TextLine, 4, 33)
            Line Input #1, TextLine
        End If
txtPax.Value = pax1 & vbNewLine & _
                       pax2 & vbNewLine & _
                       pax3 & vbNewLine & _
                       pax4 & vbNewLine & _
                       pax5 & vbNewLine & _
                       pax6
The result shows only the last passenger, for some reason the first one is not read.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
Because the Input line reads the next line, if you want to skip the next line you have to do another Input line...

Code: 
       If Left(TextLine, 3) = "A02" Then 'First A02 line
            pax1 = Mid(TextLine, 4, 33)
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Second A02 Line
            pax2 = Mid(TextLine, 4, 33)
            Line Input #1, TextLine
        End If
txtPax.Value = pax1 & vbNewLine & _
                       pax2 & vbNewLine & _
                       pax3 & vbNewLine & _
                       pax4 & vbNewLine & _
                       pax5 & vbNewLine & _
                       pax6
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
Some efficiencies can be made probably, but your seem to be well on your way :)
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Ah yes ! the line input, was putting everywhere to get this done. Yea i was thinking of asking you about the efficiencies in the code. Will do that at the end once i finish with reading file part. Thanks :)
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Now on A04 output i get
Code: 
DOHDOHA         ORDCHICAGO/O HARIN
ORDCHICAGO/O HARSATSAN ANTONIO  DN
DENDENVER       ORDCHICAGO/O HARDN
ORDCHICAGO/O HARDOHDOHA         IN
from
Code: 
A0401AA001AMERICAN AIR7975S HK18DEC0800 1340 2DOHDOHA         ORDCHICAGO/O HARIN    X0   777    07128F AC:QATAR AIRWAYTK:YJT:14.40ANL:AMERICAN AIRLINES       ACL:QATAR AIRWAYS                           DDL:18DEC14
A0402AA001AMERICAN AIR3177S HK18DEC1820 2105 2ORDCHICAGO/O HARSATSAN ANTONIO  DN    O0   CR7 T3 01043F AC:ENVOY AIR ASTK:YJT:02.45ANL:AMERICAN AIRLINES       ACL:ENVOY AIR AS AMERICAN EAGLE             DDL:18DEC14
A0403AA001AMERICAN AIR3137S HK01JAN1400 1715 2DENDENVER       ORDCHICAGO/O HARDN    X0   CR7    00887F AC:ENVOY AIR ASTK:YJT:02.15ANL:AMERICAN AIRLINES       ACL:ENVOY AIR AS AMERICAN EAGLE             DDL:01JAN15
A0404AA001AMERICAN AIR7974S HK01JAN1925 1740 3ORDCHICAGO/O HARDOHDOHA         IN    O0   777 T3 07128F AC:QATAR AIRWAYTK:YJT:13.15ANL:AMERICAN AIRLINES       ACL:QATAR AIRWAYS                           DDL:01JAN15
how can i get it to display as
Code: 
DOHA / CHICAGO
             CHICAGO / SATSAN ANTONIO
             DENVER / CHICAGO
             CHICAGO / DOHA
 
Last edited:
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
ok never mind the above. Would you be knowing how to better code the below.
Code: 
'Display those variables that Validate to not Null
        If IsNull(pax1 & pax2 & pax3 & pax4 & pax5 & pax6 & pax7 & pax8 & pax9) = True Then GoTo Validated
Validated:
        txtPax.Value = ALC & "-" & TKT1 & vbNewLine & _
                       ALC & "-" & TKT2 & vbNewLine & _
                       ALC & "-" & TKT3 & vbNewLine & _
                       ALC & "-" & TKT4 & vbNewLine & _
                       ALC & "-" & TKT5 & vbNewLine & _
                       ALC & "-" & TKT6 & vbNewLine & _
                       ALC & "-" & TKT7 & vbNewLine & _
                       ALC & "-" & TKT8 & vbNewLine & _
                       ALC & "-" & TKT9
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
anishkgt said:
Now on A04 output i get

<snip>
how can i get it to display as
<snip>
Click to expand...

You dont, atleast not straight from the file... I would do this using a "lookup" table that will translate the bad names to proper names...
Also I would probably make some logic to split the two into a "from" and "to" column, possibly straight from the file or if need be from the lookup table.

The lookuptable would have a column
From File
DOHDOHA ORDCHICAGO/O HARIN
Departure
DOHA
Destination
CHICAGO

Having the departure and Destination seperated will eventually enable you much easer to find all flights to CHICAGO for example

ok never mind the above. Would you be knowing how to better code the below.
Click to expand...
Not really, not unless you tell me what the heck you are trying to do?

When ever you have numbered fields like that that usually is a clear pointer that you have sometimes only Pax1 and sometimes 1 thru 5 and rarely 9... plus what happens if you get 10?
To "really" solve this it may be better to store this information in a seprate table.
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
I've done the sectors with a different approach and it works as needed. Not sure the if number of data increases or decreases for example T1, T2, may not be there at times. So the code returns an error. I've used an if statement to check for that line. What if there are more Tax, from T1 to T20 and similarly for the passengers, and sectors. What would be the best way to address this ?
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
What am trying to do is first read and check the lines display as needed on the form and then if possible upload to an access database
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
anishkgt said:
What am trying to do is first read and check the lines display as needed on the form and then if possible upload to an access database
Click to expand...

I understand that, I dont mean that... I mean what are you trying to do with the particular piece of data.

If you always have 1 of something and then have possibly x other lines of the same (like you do with A04), you use a do while

Something like...
Code: 
Do while left(YourLine,3) = "A04" then

     input line
loop
Which will read each of the A04 lines and move to the next line each time.
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Thought i was not clear.

Ok let me try that. Will update once done.
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
Retruns an error as 'Syntax error' and Compite error: Expected: end of statement
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
It should be obvious (by now) that that sample code is far from complete ?
 
A

anishkgt

Registered User.
Local time
Today, 07:17
Joined
Nov 4, 2013
Messages
384
without the 'then' it returns the passenger in the last A02. So previously my code
Code: 
If Left(TextLine, 3) = "A02" Then 'First A02(PAX)  line
            pax1 = Trim(Mid(TextLine, 4, 33))
            TKT1 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Second A02(PAX)  Line
            pax2 = Trim(Mid(TextLine, 4, 33))
            TKT2 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Third A02(PAX)  Line
            pax3 = Trim(Mid(TextLine, 4, 33))
            TKT3 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Fourth A02(PAX)  Line
            pax4 = Trim(Mid(TextLine, 4, 33))
            TKT4 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Fifth A02(PAX) Line
            pax5 = Trim(Mid(TextLine, 4, 33))
            TKT5 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Sixth A02(PAX) Line
            pax6 = Trim(Mid(TextLine, 4, 33))
            TKT6 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        If Left(TextLine, 3) = "A02" Then 'Seventh A02(PAX) Line
            pax7 = Trim(Mid(TextLine, 4, 33))
            TKT7 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        If Left(TextLine, 3) = "A02" Then 'Eighth A02(PAX) Line
            pax8 = Trim(Mid(TextLine, 4, 33))
            TKT8 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        If Left(TextLine, 3) = "A02" Then 'Ninth A02(PAX) Line
            pax9 = Trim(Mid(TextLine, 4, 33))
            TKT9 = ALC & Trim(Mid(TextLine, 49, 10))
            Line Input #1, TextLine
            Line Input #1, TextLine
        End If
        
        txtTKT.Value = TKT1 & " " & pax1 & vbNewLine & _
                       TKT2 & " " & pax2 & vbNewLine & _
                       TKT3 & " " & pax3 & vbNewLine & _
                       TKT4 & " " & pax4 & vbNewLine & _
                       TKT5 & " " & pax5 & vbNewLine & _
                       TKT6 & " " & pax6 & vbNewLine & _
                       TKT7 & " " & pax7 & vbNewLine & _
                       TKT8 & " " & pax8 & vbNewLine & _
                       TKT9 & " " & pax9
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 06:17
Joined
Aug 11, 2003
Messages
11,695
Should (compacted into a do while) be something like:
Code: 
        Dim myText as string
        If Left(TextLine, 3) = "A02" Then 'First A02(PAX)  line
            pax1 = Trim(Mid(TextLine, 4, 33))
            TKT1 = ALC & Trim(Mid(TextLine, 49, 10))
            myText = TKT1 & " " & pax1
            Line Input #1, TextLine
            Line Input #1, TextLine
            Do while left(TextLine,3) = "A02" then ' Second and further A02 lines
                pax1 = Trim(Mid(TextLine, 4, 33))
                TKT1 = ALC & Trim(Mid(TextLine, 49, 10))
                myText = myText & vbnewline & TKT1 & " " & pax1
                Line Input #1, TextLine
                Line Input #1, TextLine
            loop
            txttkt.value = mytext
        End If
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Exporting a form as text and then importing.?
Replies
15
Views
166
cheekybuddha
cheekybuddha
R
Linked Table - Excel - Blank Column A
2
Replies
21
Views
994
pdanes
P
Guus2005
Import must be read as-is into a table.
Replies
9
Views
530
Pat Hartman
P
C
exporting access table data to excel
Replies
10
Views
438
raziel3
raziel3
M
Importing data from excel with file name
Replies
15
Views
823
Gasman
Gasman

Users who are viewing this thread

Total: 3 (members: 0, guests: 3)
Top Bottom