HELP! Import File from FTP Server!!!

[cvia]

Hello All!
I hope someone can help me out with my problem here:
I would like to import a file into Access that is located on an FTP
server. I want to be able to do this quite often, so manually
importing it every time it is neccessary is not really an option.
So, I realize that some VB is probably the solution. The file in
which I want to import is a tab delimited list of products: product
number, price, etc. I would like to put the elements into a table.
Is this at all possible?
Any help or a shove in the right direction would be greatly
appreciated!
Thanks so much!

 

[rhesus]

Hi cvia, I am actually doing this right now. The way I do it is somewhat complicated: I wrote a program to download the data using C++ and combine it all into one text file, then I do a link table to the text file in Access. Hope that helps a little.

 

[jjturner]

Lookup the 'TransferText' method in Access help. You'll want to setup and save an Import Specification as well. As long as you know the file name and FTP address that you're trying to import from, should be relatively simple.

HTH,
John

 

[cvia]

Thanks John! I will try it out. Cross your fingers!!

 

[cvia]

Alright, I am running into some problems trying to do this:
While trying to set up the transferText in a macro, and I can't figure out what to put into the Specification name and File name fields. Obviously, the name of the file should go in the filename field, but since I am getting it from an FTP server, how does this work. Also, I understand what the specification means, the text file is a tab delimited, but how do I work this into the specification name, i.e. how do I set this up? I went to File, Get External Data, and then I tried to set up a new FTP location and I am getting the error: The folder isn't accessable, It may be located on an unavailable volume or protected by a password. I entered the username and password for the ftp site while setting up the new FTP location.
What am I doing wrong here?
Please be patient and kind, I am new to this "advanced-type" Access stuff.
Thanks so much!
Christine

 

[jjturner]

Hi Christine,

Apologies, I presumed that you had already been 'manually' importing your FTP file into Access using the Import directory on Access. I'm only familiar with Access 97. Unfortunately, my own experience is that I've been unable to retrieve an FTP file if it exists on a subdirectory on the FTP site, although I've had no trouble with retrieving if the file is on a main directory.

Hopefully someone can shed more light on the directory problem, but I believe you can reference something like this for the name of FTP site (if it's in a main directory):

/DirectoryNameOnFTP/YourFileName

So, only after you're able to connect to the FTP file through Access will you be able to setup an Import Specification - it will be a button like 'Save as Import Specification' when you're in the Import 'Wizard'.

Once you save the Import Specification (it will ask you to name it), the name that you choose for it will be what you type in your Macro for 'Specification Name'.

No Worries,
John

 

[jjturner]

Once again apologies...

Your 'name of FTP site' should just be the name of the directory:

<FTPDirectoryName> (No quotes, brackets, or slashes)

(Access seems to be fussy about syntax )

Your username and password are presumably correct since you've already been pulling the file off of FTP with them (from some application other than Access, I'm guessing).

Keep at it!

 

[jjturner]

Another tip

Yeah, I'm still working on this one

I discovered that when you go to Import from Access, you can just click 'Internet Locations (FTP)' in the 'Look In' box THEN in the 'File Name' box on the bottom you can just paste (or type) in your FTP site address (i.e. "ftp://whatever...") and Access automatically saves it if it can find it. (This gets around the site naming limitations from the FTP Add/Modify dialog box.) From there it will prompt you to log in Anonymously or with UserName and Password.

At any rate, once you accomplish that, you'll hopefully be able to proceed with the import, saving specification, etc . . .

Let me know how it turns out.

Regards,
John

 

[cvia]

John,
Alright, so I did as you suggested, and I was all excited when I typed in the ftp site and it all came up...all of the files and folders located there. However, when I selected the file and clicked on the Import button, it failed. I got a message saying the "ftp location" is not a valid account name. Now what does this mean. It doesn't make sense to me at all!!!
I hope that your expertise will allow you to shed some light on my problem here!!!
Thanks so much for your time and intellect!
Christine

 

[jjturner]

Workaround . . .

OK,

To start with, just like you, even though I could see the FTP files in Access, nothing worked when I clicked Import. (You may wonder why I'm trying so hard to crack this nut - that's because I have a bit of self-interest in the solution since up until now I've been doing the same as you, which is 'manually' pulling down FTP files into the LAN directory using an FTP Client app, THEN pulling into Access from there.)

Solution - use Excel to pull the FTP file down (for some reason it works there).

Will get back to you with the rest of the plan (swamped with work right now) . . .

John

 

[jjturner]

Still Thrashing About . . .

Christine,

I did some more searching and found this article in the MS Access Knowledge Base.

Appears as though the Access Import Wizard cannot handle 'viewing' FTP file contents, therefore you can't setup an Import spec from the FTP site. Use an example file that you've stored locally on your LAN or C: drive to setup and save the import spec. Then, just by running your TransferText macro, with the proper arguments in it of course, you'll be able to pull the file down from the FTP site direct into Access.

A rebel yell here: THIS WORKED FOR ME !!!

All is well and I'm at peace with the world

(If that hadn't worked, my alternative answer with Excel was gonna get a little dicey and technical)

Faithfully,
John

PS - if you find yourself still in the woods on this, I assure you that you're nearly out, and of course help is ever at hand

 

[NOL]

FTP export error

I've spent all day trying to do export a query from my MS access database to an FTP server using a macro with TransferText.

I get this error saying
"Microsoft Jet Database could not find the object
'ftp://UserName:assword@abc.com/../dir1/dir2/dir3/Data/' .Make sure the object exists and that you spell it's name and path correctly."

MY Macro
Transfer Type : Export Delimited
Specification Name:
Table Name : MyQuery
File Name : ftp://UserName:assword@abc.com/../dir1/dir2/dir3/Data/MyFile.csv[/url]
Has Field Names : No
HTML Table Name:
Code Page:


The FTP URL works juts fine in IE .
I also tried File Open in Accesss , set up my FTP server and i can see all my files and folders on the FTP server.

This one is really gettign to me fast!
And looks like you guys have cracked this nut .
Would appreciate any help ..
Thanks,
Gina.

 

[jjturner]

Gina,

I don't know if the hyperlink you provided was supposed to be a valid path, but it didn't work when I clicked it.

Here is another post dealing with FTP. It should give you some more ideas about what you can do and definitely more flexibility.

I'm not familiar with Exporting to FTP (being just recently familiarized with Importing from it ) - but I don't see why it shouldn't work if your FTP site has uploading permissions on it.

You may want to define a specification to use with your macro - and of course bear in mind that the Wizard's don't like FTP, so just Export to test directory on your computer to setup the spec.

Last note: I wouldn't expect your query to be the source of the problem due to the error message, but if all else fails you may want to double-check if it's trying to take parameters that may not be necessarily getting provided to it during execution of your macro.

HTH,
John

 

[NOL]

Hi John,

I cannot provide the ftp server details.
Those are dummy values i typed in. sorry about that.

I created an import spec as u suggested and tried importing the file instead of exporting. I get the same error.

I can export and import my file on local disk with or without the specs and it's just the way i want it.

But thanks for taking the time to reply.
I will try the suggestions from your "other" posts
and will let you know if I succeed !


Thanks,
Gina.

 

[NOL]

Done!

Hi ,

I used a script on my harddisk with FTP commands.
it's simple and works reliably.

*************************************************
Function FTPUpload()

On Error GoTo Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\FTPXfer.txt", True)
a.Writeline ("OPEN <FTP Server>")
a.Writeline ("USER <user name> <password>")
a.Writeline ("CD ../user/data_files")
a.Writeline ("LCD C:\Reports")
a.Writeline ("BINARY")
a.Writeline ("PUT MyFile.csv")
a.Writeline ("BYE")
a.Close

Call Shell("C:\WINNT\System32\ftp.exe -ns:C:\FTPXfer.txt", vbMinimizedFocus)

Err:
'MsgBox Err.Number & " " & Err.Description
Exit Function

End Function

*************************************************

Thanks for the timely help guys !

Gina

 

[fenhow]

Hi, How do I implement this? It looks like it is what I am in need of?

*************************************************
Function FTPUpload()

On Error GoTo Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\FTPXfer.txt", True)
a.Writeline ("OPEN <FTP Server>")
a.Writeline ("USER <user name> <password>")
a.Writeline ("CD ../user/data_files")
a.Writeline ("LCD C:\Reports")
a.Writeline ("BINARY")
a.Writeline ("PUT MyFile.csv")
a.Writeline ("BYE")
a.Close

Call Shell("C:\WINNT\System32\ftp.exe -ns:C:\FTPXfer.txt", vbMinimizedFocus)

Err:
'MsgBox Err.Number & " " & Err.Description
Exit Function

End Function

*************************************************