Read a file and import its data into a table

anishkgt said:
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 ?
Click to expand...

Geven the structure of the file i would make a separate table per type of line that does have your paxseq, just Sems easiest that way

What part of the code is throwing you the error?
I expect like i told you a few times already it has something to do with your recordstet being closed
 
anishkgt said:
Code: 
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
Click to expand...
These may not be related to the pax, however they are legs of either the flight or the ticket booked by the passengers?
So make a separate table and store your required info + what ever linking data you need (ticket number or flight number)
 
The error line is in red
Code: 
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)
                Tax1 = Trim(Mid(TextLine, 57, 8))
                Tax2 = Trim(Mid(TextLine, 70, 8))
                Tax3 = Trim(Mid(TextLine, 83, 8))
                Tax4 = Trim(Mid(TextLine, 96, 8))
                Tax5 = Trim(Mid(TextLine, 109, 8))
                [B][COLOR=Red]RSPXFare.AddNew[/COLOR][/B]
                RSPXFare![BaseFare] = BaseFare
                RSPXFare![EquivalentFare] = EQAmt
                RSPXFare![Tax1] = Tax1
                RSPXFare![Tax2] = Tax2
                RSPXFare![Tax3] = Tax3
                RSPXFare![Tax4] = Tax4
                RSPXFare![Tax5] = Tax5
                RSPXFare.Update
                Line Input #1, TextLine
                Line Input #1, TextLine
                Do While Left(TextLine, 3) = "A07" 'Reads remaining fares
                    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)
                    Tax1 = Trim(Mid(TextLine, 57, 8))
                    Tax2 = Trim(Mid(TextLine, 70, 8))
                    Tax3 = Trim(Mid(TextLine, 83, 8))
                    Tax4 = Trim(Mid(TextLine, 96, 8))
                    Tax5 = Trim(Mid(TextLine, 109, 8))
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If
 
Ooops ! my bad. did not set the variable to the table. Done now.
 
That is what I was talking about, setting the table :)

Arent you missing the PAXSeq in this table (and possibly the FileID)?
 
Yup careless mistakes :(

I was going to ask you about how to populate the FileID for each table from tblFiles for all the tables and the paxseq for this table.

I only know the addnew part not the edit syntax. How do i add the FileID to each table and paxseq
 
You dont need to edit anything.... you can simple add them while adding, there are two different things though.
1) FileID
This is unique per file, earlier in your code (see my post #198) you filed FileID.
Here in this part of the code simply add it to your record

2) PaxSeq
Already exists on this line, so simply extract it the same as the other information and add it to your table

Dont really see this as being complex?
 
Not sure if i got it correct but here it is

Code: 
    Set RSFiles = CurrentDb.OpenRecordset("tblFiles")
    Set RSPax = CurrentDb.OpenRecordset("tblPax")
    Set RSPXFare = CurrentDb.OpenRecordset("tblPAXFareValue")
    Set RSSector = CurrentDb.OpenRecordset("tblSector")
    FileID = Nz(DMax("FileID", "tblFiles"), 0) + 1
.
.
.
.
.
.
.
.
RSFiles.AddNew
        RSFiles![FileID] = FileID
        RSFiles![FileName] = srcfile
        RSFiles![PNR] = PNR
        RSFiles.Update
the result in the table tblFIles is that FileID of all the records are 1
 
The FileID = Nz(Dmax()) line needs to be just before the RSFiles.Addnew
If it is not there, then it would seem logical the FileID= line is only executed once and will always be 1
 
all seem to be working well except the paxSeq. how do i add the paxseq from tblpax to the tblPaxFareValue ? tired the concept used in for the FileID, the PaxID came correct but the PaxID in tblPaxFareValue was repeating.
 
For you patients and willingness to help people to the extent that you display in this thread. Kudos to you my friend.
 
The paxseq is on the same line as well isnt it?

A0701
A0702

Or whatever A line it was
 
ah yes, how could i forget that ? got it, done. all ok, now for some error traps. was trying the below and it was working before
Code: 
If DCount("PaxID", "tblPax", "[Ticket#]=" & tkt) Then 'Find ticket # Duplicate
i get an error "data type mismatch in criteria expression"only when condition is met else the field is populated.
 
I guess there is a glitch. The FileID does not seem to be populated to other tables its just repeating the ID.
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))
                PaxSeq = Trim(Mid(TextLine, 76, 2))
                txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                tktDisp = ALC & Trim(Mid(TextLine, 49, 10))
                'If DCount("PaxID", "tblPax", "[Ticket#]='" & tkt) Then 'Find ticket # Duplicate
                '   'Name SrcFolder & srcfile As DupFolder & srcfile
                '    txtMIRActivity.Value = srcfile & " " & tkt & " " & "DUPLICATE TICKET " & vbNewLine & txtMIRActivity.Value
                '    GoTo NextFile 'Abort and move to next file
                'End If
                RSPax.AddNew
                [COLOR=Red][B]RSPax![FileID] = myfileid[/B][/COLOR]
                pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
                RSPax![paxid] = pxid
                RSPax![Passengers] = Pax
                RSPax![ticket#] = tkt
                RSPax![PaxType] = PaxType
                RSPax![PaxSeq] = PaxSeq
                RSPax.Update
                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 remaining A02 (Pax) lines (if any)
                    Pax = Trim(Mid(TextLine, 4, 30))
                    tkt = ALC & Trim(Mid(TextLine, 49, 10))
                    PaxType = Trim(Mid(TextLine, 67, 6))
                    PaxSeq = Trim(Mid(TextLine, 76, 2))
                    txtMIRActivity.Value = srcfile & " " & tkt & " " & "Processed" & vbNewLine & txtMIRActivity.Value
                    RSPax.AddNew
                    pxid = Nz(DMax("PaxID", "tblPax"), 0) + 1
                    RSPax![paxid] = pxid
                    [COLOR=Red][B]RSPax![FileID] = myfileid[/B][/COLOR]
                    RSPax![Passengers] = Pax
                    RSPax![ticket#] = tkt
                    RSPax![PaxType] = PaxType
                    RSPax![PaxSeq] = PaxSeq
                    RSPax.Update
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
            End If
I did not notice until i was building the query as there duplicates coming up.
 
Aite ! having bad day:banghead:.......Figured out everything just need help with post 216:(
 
Never had to come across creating forms from a query but here there seems to be a need. I did try making the current query into a form for manual entry but the form when displayed in the form view goes completely blank.

How can i create this form ?
 
You must log in or register to reply here.

Similar threads

I
How to automate importing 31 .csv files into SQL Server?
2
Replies
38
Views
1,702
ions
I
I
Get a field ID
Replies
2
Views
864
Isaac
Isaac
M
Importing PowerQuery results into SQL Server
Replies
10
Views
978
jdraw
jdraw
T
  • Question Question
Advice needed please. Importing Sharepoint Text file into List and Deleting previous days data
Replies
4
Views
1,108
Isaac
Isaac
B
Getting data from differently formatted Excel sheets to a table Form/VBA/Import question
Replies
13
Views
1,278
CJ_London
C

Users who are viewing this thread

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