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

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
aite ! managed to get it rite. Now how do i append to exiting text file. at the moment it creates a new file every time i click.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
After a few googling, found the code to be
Code:
Open log For Append As #2
which is added to the top of the file, like so
Code:
EMD = DLookup("[EMD]", "tblFilePath", "[PathID]=1") & Rename
    NoPax = DLookup("[NoPax]", "tblFilePath", "[PathID]=1") & Rename
    Set RSFiles = CurrentDb.OpenRecordset("tblFiles")
    Set RSPax = CurrentDb.OpenRecordset("tblPax")
    Set RSPXFare = CurrentDb.OpenRecordset("tblPAXFareValue")
    Set RSSector = CurrentDb.OpenRecordset("tblSector")
    Set RSFop = CurrentDb.OpenRecordset("tblFop")
    Set RSEx = CurrentDb.OpenRecordset("tblExchange")
    'Set fs = CreateObject("Scripting.FileSystemObject")
    'Set a = fs.CreateTextFile(log, True)
    Open log For Append As #2
and close the file, like so
Code:
NextFile:
        
        srcfile = Dir 'get next file
        Loop 'srcfile
        RSFiles.Close
        RSPax.Close
        RSPXFare.Close
        RSSector.Close
        RSFop.Close
        RSEx.Close
        Write #2, txtMIRActivity.Value
        Close #2
Now the error i get when it runs again is that "File is already open" at line
Code:
Open log For Append As #2
how can i resolve it ?
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Solved it ! now how can i create new files every 1MB ?
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
Filelen function should be able to give you that... Though why cut the files at 1 MB? why not 10 MB? Or remove old history at some point?
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Not sure files of 10 mb would open fast. Ok lets make it 10MB if you say so. Removing the old history, well we need those log at some time.
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
Depends on what you call fast,
depends on what you need them for,
Depends on how often you need them,
Etc, etc.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
was trying the below code
Code:
log = "C:\MIR\" & Date & "MirActivityLog.txt"
    [COLOR=Red]Flen = FileLen(log)[/COLOR]
If Flen > 5 Then
    MsgBox ("File length Reached")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(log, True)
    Else
    Open log For Append As #2
    End If
i get an error at line marked in red "File Not found". Need to create new file with current date stamp
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
To create a file you use the earlier OPEN code....
Filelen requires the file to be there....

Alternatively you can use the DIR function to check if it exists...
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Code:
LFile = "C:\MIR\19-Jan-15_MIRActivityMIR.txt"
    If LFile = "" Then
    Set lf = fs.CreateTextFile("C:\MIR\" & Date & "_" & "MIRActivityLog.txt", True)
    [COLOR=Red]Flen = FileLen(LFile)[/COLOR]
    Else
    Open LFile For Append As #2
    End If
    If Flen > 5 Then
    MsgBox ("File length Reached")
    Set NF = fs.CreateTextFile(Now & "_" & "C:\MIR\19-Jan-15_MIRActivityMIR.txt", True)
    End If

The line in red is the part where it checks the file size but the file size returned is always zero even when the file size is above 3KB. Where is the error ?
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
Set lf = fs.CreateTextFile("C:\MIR\" & Date & "_" & "MIRActivityLog.txt", True)
Flen = FileLen(LFile)
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Can't find the difference here. Seems both are the same.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Figured it out :)
Code:
LogF = "C:\MIR\"
    NewF = LogF & Rename & "MIRActivityLogg.txt"
    LFile = "C:\MIR\" & Date & "_" & "MIRActivityLog.txt"
    If LFile = "" Then
        Set lf = fs.CreateTextFile(LogF & Date & "_" & "MIRActivityLog.txt", True)
    Else
        Open LFile For Append As #2
    End If
    [B][COLOR=Red]Flen = FileLen(LFile)[/COLOR][/B]
    If Flen > 10000000 Then
        Set NF = fs.CreateTextFile(NewF, True)
    End If
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
hi,

Been trying to do a duplicate check with the following code
Code:
If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq) And DCount("FileID", "qryMIR", "[TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication
                    txtMIRActivity.Value = srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE" & vbNewLine & txtMIRActivity.Value
                    Print #2, srcfile & " " & MIRSeq & " " & tkt & " " & PNR & " " & "DUPLICATE MIR FILE"
                    Close #1
                    Name SrcFolder & srcfile As dupfolder & srcfile 'Move Duplicate file
                    GoTo NextFile
                    End If

well it does work well, how can i make to compare the MIRSeq and tkt of that particular row. The above code checks the whole query rather than a row so the values does repeat which is something that i found out down the road.
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
Wow didnt even know this thread still lived on here :)

Code:
Dcount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") then
Be carefull with DCounts and DLookups though, they are a big source of "slow down" in your code... Usually you can merge multiple DCounts or DLookups into one query which will be much faster.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Lol still hanging on :) so i create a septate query with mirseq and tkt ? And dlookup into this new query ? But still i would be using the same dlookup function, rite ? Is that whatnyou meant ?
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:35
Joined
Aug 11, 2003
Messages
11,696
No, .... It is perfectly fine to use this single DLookup/Dcount as long as you dont have multiple Dfunctions on the same query or table in sequence.

If for example your next check is again on that same query but another field:
Code:
Dcount("FileID", "qryMIR", "[SomeField]=" & MIRSeq  ) then
You can speed things up by using a query... something along the lines of
Code:
dim rs as DAO.Recordset
set rs = Currentdb.Openrecordset("Select ...")
If ...
end if

If ...
End if
rs.close
set rs = nothing

Simply remember that any DFunction is a query upon itself.

Also dont know if this is true or not, but it may not be necesarry to utilize the qryMIR.
the DCount will trigger the full query to run (again) which if it is a big query can take precious time. Instead perhaps you can use a smaller query or even go back to the source table and check that instead.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Ok thanks for that info. Let me try your suggestion. Don't how to set the select query when coming to vb coding. Will give it a try tho.
 

anishkgt

Registered User.
Local time
Today, 23:35
Joined
Nov 4, 2013
Messages
384
Or can i use separate if statements instead of in one line ?
 

Users who are viewing this thread

Top Bottom