Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    import multiple text files (2003)

    Good Morning.

    I have a need to import multiple delimited text files with variable names. In doing this I would also like to capture the file name as a field.
    Ex:
    R2098213.txt
    R2098214.txt

    The purpose of the database is error and resolution tracking.
    My root problem is that while the resolutions are being entered the db is open and I cannot write directly to it to input new errors (the system writing the errors out requires exclusive access)
    I tried writing to 1 database and linking to the table from another that my users could keep open, but if they have the linked table open it keeps the original db open too.

    So my next thought was to have each error create it's own text file, and build an import routine that the users can kick off, but that's where I get the variable file names.
    Any ideas gratefully accepted.

    Kathy Nielsen
    Duke University

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    Would you like the import to be initiated by a user (for example by clicking a command button), or should the database scan for them automatically?

    What should happen with the text files after they have been imported? Should they be deleted, or renamed, or moved to another folder?

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import multiple text files (2003)

    Hans,

    In a perfect world...
    The database would scan for them automatically and delete them after they were imported.

    If the world is not perfect... then a command button and move the file after import. I can create a program to delete them from the new location.

    Thank you,
    Kathy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    Should all files be imported to one table, or each to a separate table?

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import multiple text files (2003)

    Ideally the files should import to the same table.

    Thanks, Kathy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    I have attached a sample database that demonstrates how it could be done. You will have to modify it in several places to make it work for you.

    You must set up a folder where the text files will be stored. The folder should contain no other text files than those intended to be imported. I have assumed .txt as extension, you can change this in the code.
    You should change the value of the FilePath field in the single record in tblPath to the full path of the folder you will use.

    The data will be imported into tblImport. The first field of this table is an AutoNumber field ID, and the second field a text field FileName. You should leave these unchanged.
    The remaining fields are intended for the import - you should modify them and add other fields as needed.

    The form frmImport contains an On Timer event procedure that loops through the text files in the specified folder, reads them into tblImport, setting the FileName field, then deletes the files.

    The Timer Interval property of the form has been set to 60,000 milliseconds, i.e. 60 seconds or 1 minute. You can change this, but don't set it to a small value, for that won't leave enough time for the code to process the files. It worked well in a small-scale test.

    I hope you can use this as a starting point; feel free to ask questions.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import multiple text files (2003)

    Hans,

    This is fantastic. I'll try it right away.

    Thank you.


    Kathy

  8. #8
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import multiple text files (2003)

    Hans,

    It's wonderful.

    I had to change my outbound routine to bar delimit the fields because one of the fields contains a comma character so all of the text fields had been enclosed in quotes and it did not like that, but that was a very simple fix for me and well worth it to see this work.

    Thank you so much.
    Kathy

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import multiple text files (2003)

    Thanks Hans I've been looking for this

    John

  10. #10
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: import multiple text files (2003)

    Hans, in your code you used a comma delimiter. How would you change that to tab?
    Thanks
    chuck

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    Change the line
    <code>
    arr = Split(strLine, ",")
    </code>
    to
    <code>
    arr = Split(strLine, vbTab)
    </code>
    That's all!

  12. #12
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: import multiple text files (2003)

    Well I was close...didn't think to prefix 'Tab' with 'vb'. There was a time when I could not understand any code. You see, I have learned a lot reading these forums and having my questions answered.

    If I may ask just one more question. You declare arr but not as anything. Is arr a key word or does nothing mean it is of the variable type?
    Thanks
    chuck

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    'arr' is just a name for a variable. I intended it as an abbreviation of 'array' but it could have been any valid name.
    Since I didn't declare a data type explicitly, it is a Variant, i.e. it can contain anything. I could have declared it as an array of strings instead:

    Dim arr() As String

  14. #14
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: import multiple text files (2003)

    Thank you Hans. If I were not retiring in 522 days I might start catching on to this stuff. In the mean time I really appreciate your help.
    Thanks
    chuck

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: import multiple text files (2003)

    Well, you can still learn quite a lot before the end of July, 2008! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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