Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: import data from text file start from a specific line to access table

    Hi Gurus,

    I have a text file which will grow when a test is performed.

    I am thinking to develop an access program run every 5 minutes to read the data in the text file and write into a access table. However, the next time the program run, I don't want the entire text file be loaded into the table again. I just want to load the new lines. How to utilize the record count in the table to load the data in the text file start from line #(recordcount +1) to the table? Thanks.

    Any input will be very much appreciated.

    Newguyinaccess

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    This is actually easier than you think but it does completely depend upon the type of data which is contained within the text file and how it may be delimited if it is at all. If it isn't, it still isn't the end of the world....just more code.

    Starting from the beginning, Real quick and off hand I would...

    1) Create a function to Read the text file,
    2) isolate the pertenent data from each line read and place into proper type variables,
    3) Using the the INSERT INTO SQL Statement, add the data to table,
    4) Using the UPDATE SQL statement I would place the line number of the last read line of the text file into a field in a table (perhaps a setup table or something).

    In the database in either a visible or hidden Form, set a timerInterval to 60000 (1 minute) and in the OnTimer event work with tick chunks to fire code that will establish 5 minutes....upon approx 5 minutes time (final chunk), call the ReadTextFile function and in the function I would....

    1) Retrieve the last line read from text file from where it was stored in table,
    2) Start to read the text file from that line + 1,
    3) Do again from Item 2 in the list above.

    something like that..
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by CyberLynx
    This is actually easier than you think but it does completely depend upon the type of data which is contained within the text file and how it may be delimited if it is at all. If it isn't, it still isn't the end of the world....just more code.

    Starting from the beginning, Real quick and off hand I would...

    1) Create a function to Read the text file,
    2) isolate the pertenent data from each line read and place into proper type variables,
    3) Using the the INSERT INTO SQL Statement, add the data to table,
    4) Using the UPDATE SQL statement I would place the line number of the last read line of the text file into a field in a table (perhaps a setup table or something).

    In the database in either a visible or hidden Form, set a timerInterval to 60000 (1 minute) and in the OnTimer event work with tick chunks to fire code that will establish 5 minutes....upon approx 5 minutes time (final chunk), call the ReadTextFile function and in the function I would....

    1) Retrieve the last line read from text file from where it was stored in table,
    2) Start to read the text file from that line + 1,
    3) Do again from Item 2 in the list above.

    something like that..
    Hi CyberLynx,

    Thank you so much for sharing. After reviewing your code from another thread you responded to me, I guess I know how to get the last line read from text file and stored in a setup table. However, what's the syntax to start to read the text file from that line +1? Sorry, I am kind of weak in "file" handling.

    Thank you so much!

    Newguy.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Real quick....Here is one way......

    Open the Text File as if you would for normal read but before you process the new lines...skip through the file until you get to where you want to be. the code in blue is what you can try:

    Code:
    Dim EndLine as Long, FFile As Integer, X As Long, Strg As String
     EndLine = Nz(DLookup("[LastLineFieldInTable]","[myTableName]"), 0)
    FFile = FreeFile
    
    Open "mytextFile.txt" For Input As #FFile
       'The following Six lines will take you to the last line in file
       'you read may have read previously...
       If EndLine > 0 Then
          Do Until X = EndLine Or EOF(FFile)
             Line Input #FFile, Strg
             X = X + 1
          Loop
       End If
       Do Until EOF(FFile)
         'This will sequencialy carry on the read from next line 
         'of where we left off up above.
         Line Input #FFile, Strg 
         '..........................................
         ' Process your read Text here....
         '..........................................
         '...........................................
       Loop
    Close #FFile

    hmmmm.....
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Aug 2007
    Posts
    88

    Thanks for help!

    Quote Originally Posted by CyberLynx
    Real quick....Here is one way......

    Open the Text File as if you would for normal read but before you process the new lines...skip through the file until you get to where you want to be. the code in blue is what you can try:

    Code:
    Dim EndLine as Long, FFile As Integer, X As Long, Strg As String
     EndLine = Nz(DLookup("[LastLineFieldInTable]","[myTableName]"), 0)
    FFile = FreeFile
    
    Open "mytextFile.txt" For Input As #FFile
       'The following Six lines will take you to the last line in file
       'you read may have read previously...
       If EndLine > 0 Then
          Do Until X = EndLine Or EOF(FFile)
             Line Input #FFile, Strg
             X = X + 1
          Loop
       End If
       Do Until EOF(FFile)
         'This will sequencialy carry on the read from next line 
         'of where we left off up above.
         Line Input #FFile, Strg 
         '..........................................
         ' Process your read Text here....
         '..........................................
         '...........................................
       Loop
    Close #FFile

    hmmmm.....
    Thanks for the code. It's really good! very nice of you!

    Newguy

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is of course an even simpler and dirtier approach

    open the file in an editor, scroll down to the required line then delete to the start of the file... jobsagoddun
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmm.....Intriguing.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Aug 2007
    Posts
    88

    we can not delete the data in the text file

    Quote Originally Posted by healdem
    there is of course an even simpler and dirtier approach

    open the file in an editor, scroll down to the required line then delete to the start of the file... jobsagoddun
    Thanks for responding. However, in our situation, we need to keep all the raw data in the text file and we can not delete it. This text file was accessed by the machine and the program.

    Thanks.

    newguy

  9. #9
    Join Date
    Sep 2007
    Posts
    5
    I'd been looking for the same solution. Thanks for posting good references. Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •