1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Importing text files automatically into ms access

Discussion in 'Business Applications' started by nursell, Jan 4, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Hi,

    I have been searching for a while about importing text files automatically into my database.

    I was thinking of having a hidden form with code attached the looks in a folder on a network drive and to make it more difficult i need it to look every 5 seconds.

    So when my external program exports the files to Y:\Dispatch\Export\100045.out access will pick it up and import the data to my access table

    I hope this can be done and someone can help me.

    Thank you
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all is "100045.out" the name of the file?
    A form can be set up to do this using the Form's Timer setting and On Timer Event Procedure, but the naming of the file may be important and also the file's data structure, does it change?
    Is it to be Appended to a table?
     
  3. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Yes 100045.out is the file to be imported but the last two digits change every time like 45,46,47,48 and so on.

    .out is a text file by the way

    The file structure will be the same everytime e.g

    "33587","Mr Smith","2 Test Road","Hartlepool","HT4 4EU","245665"

    I want the data to be put in the same table everytime named "DPDTrackingNumbers"
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I think you will need a Table containing the Names of the imported files, so that you know when a new one appears.
    Then import it in to a temporary table and use an Append Query to add it to the real table.
    What version of Access are you using?
     
  5. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Im using Microsoft Access 2003.

    That sounds good if you can help me with it? I am not very good with VBA sorry :-(

    A new file will be in the folder between every 5-10 seconds
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Access 2003 has a VBA facility that allows you to load all the file names from a Folder which you can then compare to the names in the table.
    Or another option is to Delete or move the files out of that folder once copied.
    Your Access database will be working pretty hard if you are importing every 5-10 seconds, you might be better of importing every 10 minutes or so and importing all the files in one go.
     
  7. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    That will be brilliant if the files can be deleted after being imported. Every 10 minutes is fine.

    Can you help me with the VBA? I'm really looking forward to learning.

    Thank you so much
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Will you be available over the weekend?
     
  9. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would use something very similar.
    Note the bit about file name extensions.
    Got to go now.
     
  11. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Thank you
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you currently have some files in the Y:\Dispatch\Export\ folder?
     
  13. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Yes, there you go.

    Thank you
     

    Attached Files:

  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are all the files single record files?
     
  15. nursell

    nursell Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    143
    Yes they are
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083729

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice