August 13, 2002 at 7:33 am
I have a client that needs to import a text file into his database on a daily basis. The current scenario is that the text file is generated daily and is sitting out on a url, i.e. http://www.domain.com/directory/file.txt
The recipient of the file wants FTP access to the server to retrieve the file and import into his SQL Server 2000. The originator of the file does not want to open his FTP port.
Is there a way for SQL Server to pick up the file from the URL rather than FTP in?
Or... is there a better way to do this altogether?
August 13, 2002 at 10:02 pm
As much as I try, there are still some things you just can't do with SQL or vbScript. If anyone knows how to do this with VBScript and SQL, please let me know.
Here's the code to a simple VB program that should do the job for you.
Start a new VB EXE program.
Add a Module called Module1
Remove Form1
Add a class - class1
Paste the Module code into the Module
Paste the Class code into the class
Go to Project1 Properties
Click Make
Next to Command Line Arguements: type in;
URL=http://www.domain.com/directory/file.txt;SaveFile=c:\file.txt
Run the project
Because the URL and SaveFile are command line arguements, you'll be able to pass the values you need to the exe once it's compiled. It's set up to overwrite savefile if it exists. You may want to add a saveHistory funcion of some kind.
Have fun!
John
Module Code:
Option Explicit
Public strCommand As String
Public downloading As Integer
Sub Main()
Dim c1 As New Class1
strCommand = Command
downloading = 1
c1.Download ParseURL
Do Until downloading = 0
DoEvents
Loop
End Sub
Function ParseURL() As String
Dim x As Integer
x = Len("URL=") + 1
ParseURL = Mid(strCommand, x, InStr(x, strCommand, ";") - x)
End Function
Public Function ParseSaveFile() As String
Dim i As Integer
Dim x As Integer
i = InStr(1, strCommand, ";")
x = i + Len("SaveFile=")
ParseSaveFile = Mid(strCommand, x + 1, Len(strCommand) - (x))
End Function
Class Code:
Option Explicit
Dim NavURL As Variant
Dim WithEvents ie As InternetExplorer
Public Sub Download(FileURL As String)
Set ie = New InternetExplorer
ie.Navigate FileURL
End Sub
Private Sub ie_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
NavURL = URL
End Sub
Private Sub ie_DocumentComplete(ByVal pDisp As Object, URL As Variant)
Dim fso As Scripting.FileSystemObject
Dim ts As TextStream
If URL = NavURL Then
Set fso = New Scripting.FileSystemObject
Set ts = fso.CreateTextFile(ParseSaveFile, True)
ts.Write ie.Document.body.innertext
downloading = 0
End If
End Sub
August 23, 2002 at 3:24 pm
Found a much easier solution! Still uses a VB application, but makes a single API call to download the file. Matter of fact, it's called URLDownloadToFile. Here's an example:
http://www.mvps.org/vbnet/index.html?code/internet/urldownloadtofile.htm
August 24, 2002 at 6:05 am
Wow these are both awesome solutions to a problem that when I read thought was going to be really difficult.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply