Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Copying a TXT file from an FTP Site

    I have a linked table to a pipe-delimited file. This file is output every night to an ftp site on our intranet. All the user has to do is copy that file to a directory where the linked table is.

    I sure would love to automate this process for them. The filename is the same every time. All they need to do is copy it.

    I have them browse to

    FTP:\\intranet.acme.com\dailyfeeds\widget20081102. txt



    They right click and save as

    S:\accessApplication\data\widget.txt


    The there is a linked table to the wiget.txt file


    Any idea?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ...

    You can transfer your table or query directly to the s:/

    docmd.transferspreadsheet(.......)
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by mr. blonde
    You can transfer your table or query directly to the s:/

    docmd.transferspreadsheet(.......)

    Sorry dont follow you. Can you show me an example using my FTP filename and target text file names as above?

    My table already exists on the S: - it gets refreshed by a linked txt file that's the result of a mainframe feed.

    Sounds like you have a very simple solution but I don't understand.

    thanks

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    You have a table in your Access database correct?
    What is the name of your table that you want exported to your s:/ drive?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by mr. blonde
    You have a table in your Access database correct?
    What is the name of your table that you want exported to your s:/ drive?
    I don't want a table exported.

    I have a linked table in access. It is fed by a pipe delimited file using the linked table manager. That pipe delimited file is refreshed every day. The user copies it over in the mornings to run their reports for the day.

    MainFrame System->outputs feed.txt nightly -> user every morning copies from FTP site to shared drive on local server -> linked to Access Table

    The access system READ the data from the mainframe. It exports nothing.

    Here are names you can use in your example


    1.Pipe delimited file that exists on FTP Site: widget20081102.txt
    1.FTP Site Name: FTP:\\intranet.acme.com\dailyfeeds\
    2.Linked filename: wiget.txt
    2.Linked Directory: S:\accessApplication\data
    3.Linked table Name: PPMUNC


    I've number them to indicate data flow direction from source(1) to destination(3)
    Last edited by PonPending; 11-07-08 at 17:06.

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    1.) If you want to copy a file, that is not really an Access question. You could probably do this with an MS-Dos batch file to copy the pipe delimited file to your desired drive as opposed to the manual way.

    2.) Or, since the linked table in Access is essentially the same file as the one on the FTP site since you linked it in, you can export that table to the desired location as a text file, csv, etc...
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  7. #7
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by mr. blonde
    1.) If you want to copy a file, that is not really an Access question. You could probably do this with an MS-Dos batch file to copy the pipe delimited file to your desired drive as opposed to the manual way.

    2.) Or, since the linked table in Access is essentially the same file as the one on the FTP site since you linked it in, you can export that table to the desired location as a text file, csv, etc...


    We're missing each other. No export is necessary. Why do you think I want to export from access? I've been saying the data is coming INTO access to report on. I don't know how to make that any clearer.

    I want to copy that text file in VB as part of my access application and don't want to resort to batch files. This is an FTP copy question in VB within an access database application.

    The text file happens to be the source of a linked table.





    If I wanted to just copy a file I would not resort to batch files, I would:

    FileCopy SourceFile , OutputFile

    and if I wanted to export a table I would:

    DoCmd.TransferSpreadsheet acExport, , "PPMUNC", OutputFile


    The Subject of this post is "Copying a TXT file from an FTP Site". I think that wraps it up.

    The problem exists because my sourcefile is on an FTP site. Do you have FTP + VB experience??
    Last edited by PonPending; 11-07-08 at 17:21.

  8. #8
    Join Date
    Feb 2004
    Posts
    214

    ...

    I apologize, I had the process backwards.

    Is the file always going to be called "FTP:\\intranet.acme.com\dailyfeeds\widget20081102 . txt"
    Or is the 2008-11-02 the date of the export and can change?
    Is that date always the current date?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  9. #9
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by mr. blonde
    I apologize, I had the process backwards.

    Is the file always going to be called "FTP:\\intranet.acme.com\dailyfeeds\widget20081102 . txt"
    Or is the 2008-11-02 the date of the export and can change?
    Is that date always the current date?
    Thanks lol.

    I can have the feed guys write out the same filename everyday to make this process easier.

    Let's assume the FTP file is called "widget.txt"

    I'm currently trying some FTP code but i'm really not having any luck..

  10. #10
    Join Date
    Feb 2004
    Posts
    214

    ....

    If the file is called FTP:\\intranet.acme.com\dailyfeeds\widget.txt and you want to copy that to the following location S:\accessApplication\data\widget.txt so that your table can link to that file?

    The easiest way I know is to match a bat file.

    Open up Wordpad or Notepad.

    Type in the following:
    Del S:\accessApplication\data\widget.txt"
    Copy "FTP:\\intranet.acme.com\dailyfeeds\widget.txt " "S:\accessApplication\data\widget.txt"

    Then save that file and change the extension to .bat.

    You can then click and run the bat file or have it run in vb from your db.

    Would this work?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  11. #11
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by mr. blonde
    If the file is called FTP:\\intranet.acme.com\dailyfeeds\widget.txt and you want to copy that to the following location S:\accessApplication\data\widget.txt so that your table can link to that file?

    The easiest way I know is to match a bat file.

    Open up Wordpad or Notepad.

    Type in the following:
    Del S:\accessApplication\data\widget.txt"
    Copy "FTP:\\intranet.acme.com\dailyfeeds\widget.txt " "S:\accessApplication\data\widget.txt"

    Then save that file and change the extension to .bat.

    You can then click and run the bat file or have it run in vb from your db.

    Would this work?
    The filename, directory name, or volume label syntax is incorrect.
    0 file(s) copied.

  12. #12
    Join Date
    Aug 2008
    Posts
    78
    Let me repost very simply for new readers:

    I would like to copy a file from an FTP or HTTP address to my local drive programatically

    FROM
    http://intranet.widget.com/datafeeds/helloWorld.txt
    ftp://intranet.widget.com/datafeeds/helloWorld.txt

    TO

    c:\welcomeHome.txt

    I'm using Microsoft Access. Can I do this simply in VB?

    I can access the same file thru FTP or HTTP. But right now I'm right clicking and saving....Batch file no good.


    Any Idea?

Posting Permissions

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