Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    41

    Unanswered: Importing Fields from a Text File

    I have an MS Access 97 database - my main table as all of my clients, as well as account numbers. There is a "settle date" field associated with each account.

    Each day, I get a text file with a bunch of lines in it - each line contains only an account number (except for the first and last line, which is header information. For example, the file would look like:

    !!HEADER!!
    11545
    11245
    11248
    11487
    21545
    !!FOOTER!!

    Basically, i get this file each day,and at some pre-set time each day, i want to run a macro that will read in the file, and mark the "settle date" field with today's date. How can I do this?

    Thanks,
    Andrew

  2. #2
    Join Date
    May 2003
    Posts
    41

    Re: Importing Fields from a Text File

    I should mention, I just want to mark the settle date of the accounts that are in the daily text file.

  3. #3
    Join Date
    Oct 2003
    Posts
    66
    The way i would do this is to first create import specification for the text file. Then i would use the specifications to import the daily text file into a temp table. Then write an update query to set the dates.

    To create import specifications for a textfile:
    Right Click in the table area of the database window and select Import from popup menu. Select .txt file in the Files of type box. find and open the textfile. You will then be presented with the import textfile wizard. click on Advanced button. Set you field specs here and then save them. Store the import in a temp table. Next time you import just open the saved specs.

    The query would just be a update query that update the date in you perminent table with todays date if a mtahc is found in temp table.

    You would want to clear the temp table before next import.

    The SQL for th eupdate query would look somehting like this:

    UPDATE PerminentTableName
    SET SettleDate = Date()
    FROM TempTableName
    WHERE [PerminentTableName].[Account#] = [TempTableNAme].[Acct#]

    I am not the best with SQL so maybe someone else can helop you with writeing the query.
    Last edited by adrkoehler; 11-13-03 at 12:07.

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Importing Fields from a Text File

    Originally posted by secutanudu
    I have an MS Access 97 database - my main table as all of my clients, as well as account numbers. There is a "settle date" field associated with each account.

    Each day, I get a text file with a bunch of lines in it - each line contains only an account number (except for the first and last line, which is header information. For example, the file would look like:

    !!HEADER!!
    11545
    11245
    11248
    11487
    21545
    !!FOOTER!!

    Basically, i get this file each day,and at some pre-set time each day, i want to run a macro that will read in the file, and mark the "settle date" field with today's date. How can I do this?

    Thanks,
    Andrew
    You could use Docmd.TransferText to import the text file into a temporary table. You could then filter out the header and footer detail and copy the data into your live table along with the date you want to insert.

    You could also use VBA to open the text file and read the file line by line - this would be slower but would allow a fine degree of control over validating the data as you import it. To use VBa you can use the Windows file scripting object or the Office FileSearch object

Posting Permissions

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