Read a file and import its data into a table (1 Viewer)

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
txtMIRActivity.Value = txtMIRActivity.Value & vbnewline & srcfile

Something like so?

strongly suggest you add a DoEvents command after that line to ensure it keeps updating on screen, otherwize if you have many files the screen will freeze up.

Not sure whats wrong with your "Error file trap" does your textLine contain A02 at that point?
You can add a STOP command just before the if to force the code to stop there so you can validate your textLine
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
post your code and file, so I can see whats going on
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
that database doesnt contain any msgbox or as far as I can see error trapping?
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
i had removed them. will add them and post agian. alss how can i set text box's for the srcfolder and dstfolder on another form tried using
Code:
[Forms]![frmPath]![txtsource]
did not work
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
I would expect you want to keep processing other files, or is indeed your intention to completely stop?

The code seems to step out properly, after completing your T5 branch....
However when it exits the branch it is (still) at the 6VQ line, thus when it reaches the line
If Left(TextLine, 3) = Trim("A02") Then 'First A02(PAX) line
in the code, it finds " 6V" instead of A02 and will go to your error part...

I do want to stress indening (properly), you will find some things are out of place if you do...
The fact that they are out of place can cause issues down the line.

Also I would turn around this
txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & srcfile & " " & "[" & Now() & "]")
to
txtMIRActivity.Value = Trim(srcfile & " " & "[" & Now() & "]" & vbNewLine & txtMIRActivity.Value)

so new files are added on top, instead of at the bottom which will quickly scroll "offscreen"
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
yes i wish to continue to the next file after moving the error file to the error folder or writing to a txt file.

Just curious how you came to conclusion, as the error trap is checking the if condition, that is if A02 is present then continue down else display the error message.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
if A02 is present in the first 3 chars of your TextLine, not present anywhere in the file.
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
The A02 characters, isn't it searching for those characters in the whole file ? i mean is that what it is supposed to do. So i just need to skip the file if that line is not present, isn't that possible ?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
The file is read one line at a time... each time into TextLine using the line input command.

access knows not about the whole file, only line by line.

Best way to go is to take all the RS stuff and move it elsewhere, One file = one record right?

If so... change
Code:
        RS.Update    'update the table
        Close #1                    ' Close file
        Name SrcFolder & srcfile As DstFolder & srcfile
        srcfile = Dir 'get next file
        txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & srcfile & " " & "[" & Now() & "]")
to something like:
Code:
        Close #1                    ' Close file
        If len(pax) = 0 then 
            ' no passengers
            name srcfolder & srcfile as errorfolder & srcfile
            txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & _  
                                        "ERROR " & srcfile & " " & "[" & Now() & "]" & vbnewline & _
                                        "No Passengers" )
        else
            rs.addnew
            rs!... 'fill all the fields
            RS.Update    'update the table
            Name SrcFolder & srcfile As DstFolder & srcfile
        endif
        srcfile = Dir 'get next file
        txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & srcfile & " " & "[" & Now() & "]")
Also I can strongly recomend you split this into two columns:
RS![FileName] = srcfile & " " & "[" & Date & " " & "at" & Time() & "]"

One for file name and one for the actual datetime, also dont use Date at Time, just use now()
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
After a few work on the code with the ending loops,.........VOILA ! all Done. was wondering if i could refer the variable srcfolder to a text in another form tried
Code:
SrcFolder = [Forms]![frmpath]![txtSource.Value]
returns an error "Microsoft access cannot find the referenced form 'frmpath' "
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
form has to be open right?

also you can try Forms("Formname"), I think...

Again wondering toyour wonderfull coding work :)
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
Ok got it all wrong. Wanted to set the table, not the form.

Again wondering toyour wonderfull coding work :)
Got ideas, sadly no knowledge with any programming language. All my projects are on the access macros.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
Screw macros, anything they can do VBA can do better!
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
Yea that's correct. But will take time before i learn it. Now in this table i need to avoid any duplicate ticket numbers per file.

How can i ignore any files with same ticket number (tkt) and move those files to a folder C:\Duplicates
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
Trying the below code but no change in the color for either font.
Code:
If Len(pax) = 0 Then ' no passengers
            Name SrcFolder & srcfile As ErrorFolder & srcfile[COLOR=Red]
            txtMIRActivity.Forecolor = vbRed[/COLOR]
            txtMIRActivity.Value = Trim(txtMIRActivity.Value & vbNewLine & _
                                        "ERROR ! " & srcfile & " " & "[" & Now() & "]" & vbNewLine & _
                                        "No Passengers" & vbNewLine)
        Else
            [B][COLOR=Red]txtMIRActivity.Forecolor = vbBlack[/COLOR][/B]
            RS.AddNew
                RS![FileName] = srcfile
                RS![DateAndTime] = Now()
                RS![IATACode] = IATACode
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
The error trap does not seem to be working for files that are between other files, earlier those files tested where the first ones to parse like changing the file name from "AAABAGAL - Copy.MIR" to "AAABAGAL.MIR"
As far as i've understootd, the previous files pax variable is used in the next file if the current files pax is not available.

How can i change the code to check for any file in the current folder ? may clearing the variable value could help ? tried
Code:
set pax  = Nothing
but gave an error "Object required"
 
Last edited:

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
hmmmm to tackle, I've created a separate table to store the ticket numbers (tblTicketNumber) now i am trying to check during import if a file has the same ticket number and avoid that file with the code
Code:
RSTkt.AddNew
            If RSTkt![TicketNumber] = tkt Then
               .............don't know what to add here to ignore the file
            Else
                RSTkt![TicketNumber] = tkt
                RSTkt![FileName] = srcfile
            End If
            RSTkt.Update           'update tblTicketNumber
Is that how its done ?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:16
Joined
Aug 11, 2003
Messages
11,696
Just clear the fields after you added the data to the records
Pax = ""

To find out if you have an existing ticket number... Use the DCount function
Dcount("[YourKeyColumn", "[YourTable]", "[TicketNumber = " & tkt)

You should be able to figure out how to move the file
 

anishkgt

Registered User.
Local time
Today, 14:16
Joined
Nov 4, 2013
Messages
384
Code:
If DCount("[TicketNoID]", "tblticketNumber", "[TicketNumber=" & tkt)
retruns an errror "Missing),], or item in query expression'[Ticket number = 1234567' "
 
Last edited:

Users who are viewing this thread

Top Bottom