Read a file and import its data into a table

[anishkgt]

i need to add a different data from another place in the file but in the same record in the next column and not as new record. tired one and gives me an error like

 object invalid or not set

The record from A02 is added and updated. haven't closed the record as i need to add more data in the same recod. Then tried the same in A07. The A07 lies below the A02 code, below is the code

If Left(TextLine, 3) = Trim("A02") Then  'First A02(PAX) line
                Pax = Trim(Mid(TextLine, 4, 30))
                tkt = ALC & Trim(Mid(TextLine, 49, 10))
                PaxType = Trim(Mid(TextLine, 67, 6))
                RS.AddNew
                RS![FileName] = srcfile
                RS![Passengers] = Pax
                RS![PNR] = PNR
                RS![tkt#] = tkt
                RS![PaxType] = PaxType
                RS.Update
                txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                tktDisp = ALC & Trim(Mid(TextLine, 49, 10))
                If Trim(Mid(TextLine, 49, 10)) = "" Then
                    Close #1
'Name SrcFolder & srcfile As NoTicket & srcfile
                    txtMIRActivity.Value = srcfile & " " & PNR & " " & "No Ticket number " & vbNewLine & txtMIRActivity.Value
                    GoTo NextFile 'Abort and move to next file
                Else
                    tktNum = Trim(Mid(TextLine, 49, 10))
                End If
                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))
                    PaxType = Trim(Mid(TextLine, 67, 6))
                    txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                    RS.AddNew
                    RS![FileName] = srcfile
                    RS![Passengers] = Pax
                    RS![PNR] = PNR
                    RS![tkt#] = tkt
                    RS![PaxType] = PaxType
                    RS.Update
                    'RS.Close
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If
            
            If Left(TextLine, 3) = "A07" Then 'Reads base fare, Total fare and 5 Tax's
                BFC = Trim(Mid(TextLine, 6, 3)) & ": "
                BaseFare = Mid(TextLine, 9, 12)
                If Trim(Mid(TextLine, 39, 12)) = "" Then
                    EQAmt = 0
                Else
                    EQAmt = CDbl(Mid(TextLine, 39, 12))
                    [COLOR=Red]RS![EquivalentAmount] = EQAmt[/COLOR]
                    RS.Update
                End If
                TotalFare = 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

 

[namliam]

using rs.update you "close" the editing of your record...
If you want to reopen it you use rs.edit, assuming you are still on the same record.

Better would be to only rs.addnew and then only rs.update once you are sure you are done with your record.

 

[anishkgt]

don't seem to be getting it. here is what is working so far

If Left(TextLine, 3) = Trim("A02") Then  'First A02(PAX) line
                Pax = Trim(Mid(TextLine, 4, 30))
                tkt = ALC & Trim(Mid(TextLine, 49, 10))
                PaxType = Trim(Mid(TextLine, 67, 6))
                RS.AddNew
                RS![FileName] = srcfile
                RS![Passengers] = Pax
                RS![PNR] = PNR
                RS![tkt#] = tkt
                RS![PaxType] = PaxType
                RS.Update
                txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                tktDisp = ALC & Trim(Mid(TextLine, 49, 10))
                If Trim(Mid(TextLine, 49, 10)) = "" Then
                    Close #1
'Name SrcFolder & srcfile As NoTicket & srcfile
                    txtMIRActivity.Value = srcfile & " " & PNR & " " & "No Ticket number " & vbNewLine & txtMIRActivity.Value
                    GoTo NextFile 'Abort and move to next file
                Else
                    tktNum = Trim(Mid(TextLine, 49, 10))
                End If
                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))
                    PaxType = Trim(Mid(TextLine, 67, 6))
                    txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                    RS.AddNew
                    RS![FileName] = srcfile
                    RS![Passengers] = Pax
                    RS![PNR] = PNR
                    RS![tkt#] = tkt
                    RS![PaxType] = PaxType
                    RS.Update
                    RS.Close
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If

Now i need to add the below in the two records created. for now will try append to one record. how can i go about this ? need to to add the RS![EquivalentAmount] = EQAmt to the table for each record where the EQamt would be different for each ticket number. where do i add that code in the below code to show it up in the first record.

 If Left(TextLine, 3) = "A07" Then 'Reads base fare, Total fare and 5 Tax's
                BFC = Trim(Mid(TextLine, 6, 3)) & ": "
                BaseFare = Mid(TextLine, 9, 12)
                If Trim(Mid(TextLine, 39, 12)) = "" Then
                    EQAmt = 0
                Else
                    EQAmt = CDbl(Mid(TextLine, 39, 12))
                End If
                TotalFare = 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

 

[namliam]

so, you are reading multiple A02 lines first, then finding the A07 line and want to update your read A02 lines?

1) Is A02 always 2 lines/records?
2) Is there some identifier shared between the A07 and A02 line? like PNR? or some other not yet processed piece of information?

FYI:
RS.Close means you are closing your table, do you want to close the table after reading the A02 record? This also means that if you find 3 A02 records, you should get an error on the RS.Addnew, since there is no RS currently open to Add the new too....

 

[anishkgt]

1) Is A02 always 2 lines/records?
No, there can be more than 2
2) Is there some identifier shared between the A07 and A02 line? like PNR? or some other not yet processed piece of information?
Yes. In the Orginal file A02 has 01

A02SAJIDA/BIBIMRS                   331208349014657274650901         ADT   [COLOR=Red][B]01[/B][/COLOR]  N
A02FATIMA/AJWAMISS                  331208350044657274651001         C04   [COLOR=Red][B]02[/B]  [/COLOR]N

the 01 and 02 marked in red is linked with the below for the A07 line

A07[COLOR=Red][B]01[/B][/COLOR]PKR       36830QAR        1830QAR        1330QART1:      10PZT2:     190RGT3:     300XT
A07[COLOR=Red][B]02[/B][/COLOR]PKR       27630QAR        1500QAR        1000QART1:      10PZT2:     190RGT3:     300XT

Well what this means is the fare detail of passenger 01 is mentioned in A0701 and fare details of passenger 02 is mentioned in A0702. To this i'wll need to add the respective sectors from a different line like the A04 and other data

 

[anishkgt]

so when using the rs.edit how do i go about it.

                    RS.Edit
                    RS![Sector] = AirSector
                    RS.Update

does not seem to be working at it gives me an error "Obkect invalid or not set"

 

[namliam]

Object invalid is benauwend you use rs.close
IT closes the table so you can no longer use it

So this 01, 02, etc is this sequence per flight? Or per what?
It sounds like i would put this into a separate table

 

[anishkgt]

that sequence is per passenger.Like for example
if there are 3 passengers in line A02 then at the end of each line it would be 01, 02 , 03 respectively. Now these passengers have a different fares when one of them is a child or an infant. So the fare part is mentioned in line A07. now for each passenger there is a base fare, tax's and total fare. Fares per passenger is denoted as A0701 for passenger 01(from line A02.......01), A0702 for passenger 2(from line A02......02) and so on.

Attached file for your reference. Open it with Wordpad as it is more formatted than notepad.

 

[namliam]

No, not what I mean, the sequence seems to be per file, per file there is 01,02,03, etc???

there is also A0801, 02, A0901,02, this also contains information pertinent to your relevent passengers from A02 01 and 02 ??

I think the best way to tackle this is to store your information in seperate tables.
If you want your ultimate target table to have everything in one record per passenger, these seperate tables can act as staging tables...
The key though is the 01,02 to be unique Per File, and/or for your file to have a Key to it, if I recall you had something like a flight number or something?

In any case to further your work I strongly suggest you use the 01/02 passenger sequence to store your information into seperate tables.
tblA02
tblA04
tblA07
tblA08
tblA11

Or something along those lines if you have better names for them, then simply store the required information from those lines into those tables. A query can later "fix" it all into one record if you so desire, that offcourse is assuming you can make a PK from the Flight number and this passenger sequence or if need be only the passenger sequence.

Side note:
What happens if one file contains more than 99 passengers??

 

[anishkgt]

No, not what I mean, the sequence seems to be per file, per file there is 01,02,03, etc???

Yes, that is correct those come per file depending on the number of passengers. For this file there are two passengers so 01 and 02 only applies.

there is also A0801, 02, A0901,02, this also contains information pertinent to your relevent passengers from A02 01 and 02 ??

The A08 line is the Fare basis line and the A09 is the Fare construction section which we are not using at the moment. From the account side this line is not needed or rather we don't need it. The A07 line is the only that shows the price of each passenger which is what we need here.

Considering your idea about putting each line to a table.......
tblA02 - tblPAX
tblA04 - tblSector
tblA07 - tblPAXFareValue
tblA08 - tblFareBasis (not required)
tblA11 - tblFormOfPayment (not required)

The flight number number will be duplicated as new files are imported into the table. So that would not be good idea. The table for tblPAX will hold all the passengers with the ticket number being the PK, which is ok. As you say a query can then be used to pull all into one record but the A07 does not have any information relating to the A02 except the 01, 02 etc. but those cannot be used as PK when using the query. i've attached a screeshot what i want or what i think should be coded like.

 

[namliam]

Well if even only for now you dont need any information from A07 or what ever line(s) or aka table(s) there is no need to add them for the sake of adding them.
Only create that which you need currently or expect to use in the foreseeable future.

While FlightNumber will be duplicated, will atleast filename be unique?
Or something like Flightnumber + Date of the file (27 nov 2014 or 05 dec 2014 in above sample)?
If you dont have anything that will make the whole thing unique as long as the 01,02 are unique to the passenger within this file, there shouldnt be any trouble in using the sequence as (part of) the primary key, atleast for now while you are processing the file.

I would probably make a table, tblFiles (FileID, FileName, PNR)
After creating the FileID you can store it in a variable to populate later/other tables with like in tblPAX, add the FileID instead of the FileName, also add a column PAXSeq (to hold the 01,02,03, etc)
This FileID combined with the PAXSeq can then also be stored in the tblPAXFareValue table.

The A04 is that 01 also PAX related? or is that more flight related? Since this file only has one line of this A04

 

[anishkgt]

Sounds like a better idea. How can we try this out ? The A04 is related to pax as it is the travel rout for each passenger. That will always be the same for each passenger.

 

[namliam]

well you can try it out by using the techniques you should now know (mostly).

"Simply" create a table, open it in the code and stick the needed information in.
Very much the same as you are currently doing with the A02 (PAX it was I believe?)

 

[anishkgt]

Okie doki will try that out then

 

[anishkgt]

so here is what i should be doing

create the following tables
tblFile with fields FileID, FileNamea and PNR
tblPax with fields Passenger, FileID and PaxSeq
tblPAXFareValue with what fields ???

 

[namliam]

tblPax FileID, PaxSeq, Passenger + whatever (other) A02 information you need
tblPaxFareValue FileID, PaxSeq, + whatever (other) A07 information you need

We could make things more complex than this , particularly once you get into more and more of these tables. However that is mostly over the top and not really needed since the gains will be minimal at best.

 

[anishkgt]

ok so far,

tblPax
PaxID - Autonumber
Passengers - ShortText
PaxType - ShortText
PaxSeq - ShorText
FIleID - Number

tblFiles
FIleID - AutoNumber
FIleName - Short Text
PNR - Short Text

tblPAXFareValue
PFVID - AutoNumber
FileID - Short Text
PaxSeq - Short Text

all tables are populated as needed except for the FileID. The FileID value for all the other tables, should this be value from the tblFiles ? how can i get it filed into the other tables ?

 

[namliam]

well, two ways to go about it
1)
Dont make it an auto number, instead create it in code first
myFileID = nz(DMax("FileID", "tblFiles"),0) + 1
Then add it to your table yourself

2)
Use the auto number and lookup what your ID is after inserting the Filename and PNR
myFileID = Dlookup("FileID", "tblFlies")

The first option will be prevered by purists, people that love control and such.
The later that like to have the database work for them, no real difference between the two options other than the first option will guarantee to be a LITTLE faster as your table grows in number of records since it is guaranteed to use an index of your PK, where the second option may not have an index, though you can create one. Still a search on an integer field will be faster than on a text field.
Since I love speed and am a control freak, I would go for 1 !

Once you have the myFileID varaible filled, it should be peanuts for you to stick that into other tables as and where needed.

 

[anishkgt]

just noticed, I need data from line A07 as it has the fares for each passenger. Should that go into a new table like tbleFares or any of th existing ?

 

[anishkgt]

was trying to populate the tblPAXFareValue and i get an error "Object Variable or with block variable not set" and one quick question are we creating tables for each line ? If so do i have to create a a table for the A04 line which holds the sectors, in other words the route of the passengers flight. I need this data too. Shown below are those lines.

A04[COLOR=Red][B]01[/B][/COLOR]QR157QATAR AIRWAY   5V HK02DEC1720 2200 2DOHDOHA         LHRLONDON/HEATHRINM   O0   333    03259F TK:YJT:07.40ANL:QATAR AIRWAYS           DDL:02DEC14
A04[COLOR=Red][B]02[/B][/COLOR]QR157QATAR AIRWAY   4B HK20DEC1415 2355 2LHRLONDON/HEATHRDOHDOHA         INM   O0   388 T4 03259F TK:YJT:06.40ANL:QATAR AIRWAYS           DDL:20D

Side note:
The ones marked in RED are not linked with any data of the PAX