Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Import file via FTP (2000-->)

    I am currently undertaking a project where I will regularly getting a user to download 2.5 million rows of data from a csv file. Due to various problems in the way data is displayed in the csv file I am having to convert so that it can eventually be uploaded to a SQL server. The best way that I have found this to work is importing this csv file into an Access table and then connecting it to the SQL server via enterprise manager.

    The csv file is extracted from a legacy system into a server folder which I connect to and download via FTP.

    I have done a similar program in VB where I have connected to an ftp folder and saved the file to a local folder but is there a method/example I could look at that would allow the user to click a command button in Access and connect to the FTP site and allow them to import the csv to a table?
    Jerry

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

    Re: Import file via FTP (2000-->)

    Hi Jerry,

    You'll find some info in the thread starting at <post#=260430>post 260430</post#>.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import file via FTP (2000-->)

    Thanks Hans

    That is going to be useful
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import file via FTP (2000-->)

    I know it's late on a Friday evening but I will have to work on this tomorrow so planning ahead.

    I followed the link you suggested and I have been able to get my database to connect to the FTP site and download a csv file. I want to upload the recently extracted file and import this to that database. I haven't done this yet so am in the dark but just trying to be prewarned when uploading a csv or excel file normally you have to tell access if is tab delimited or csv and format via the wizard. As these files will always the same is there away that I can circumvent the formatting and just tell access that this file will always comma seperated and inverted commas are the fields etc. I am trying to automate this laborious task as much as possible.
    Jerry

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

    Re: Import file via FTP (2000-->)

    Perform the import of one such file manually, and just before you would click Finish, when everything has been set as needed, click Advanced... instead. You'll see a specification of the import settings. Click Save As to save the specification for later reuse. Give it a meaningful name. You don't need to finish the import (although it might be a good idea to test it).

    The code to import a .csv file looks like this:

    DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="MySpecification", TableName:="tblImport", FileName:="C:ImportTest.csv", HasFieldNames:=True

    with of course the appropriate names substituted. The provided specification will take care of all the details.

Posting Permissions

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