Need help reading files on a webserver

[taupirho]

Hi, I'm using the ADO code below to read two CSV files on my PC as if they were tables in a RDBMS. All its doing is displaying an employee name and their salary. This works a treat. My question is this, say the two CSV files were on a remote server on the internet e.g

http://myserver.com/employee.csv

and

http://myserver.com/salary.csv

(these files dont exist at the moment), what code changes would be required in terms of connect strings etc ...

Private sub select_data()
Set db_data = New Connection 
db_data.CursorLocation = adUseClient 

' set up DSNless connection 
db_data.Open "PROVIDER=MSDASQL;dsn=Text Files;uid=;pwd=;database=;" 

Set ado_data = New Recordset 

' Note exactly the same select statement as would be used in a relational database 
' 
ado_data.Open "select name, salary from employee.csv e, salary.csv s where e.id=s.id" , adOpenStatic, adLockOptimistic 

If ado_data.RecordCount < 1 Then 
        MsgBox "No data found" 
        Exit Sub 
End If 

' go to the first record in the set and loop around till no more available 
' 
Ado_data.MoveFirst 
For i = 0 To ado_data.RecordCount - 1 
        MsgBox (ado_data.Fields.Item(0).Value & " " &  ado_data.Fields.Item(1).Value) 
        Ado_data.MoveNext 
Next 

Ado_data.Close 
Db_data.Close 

End sub

 

[jebenson]

You could use the Internet Transfer control to read the text file into a string variable, then write the string to a local text file. The ADO connection could be made to the local file.

Dim s As String
    
s = Inet1.OpenURL("[URL unfurl="true"]http://myserver.com/employee.csv",[/URL] icString)

Open "D:\Temp\InetTest.txt" For Output As #1
Print #1, s
Close #1

Of course, instead of "D:\Temp\InetTest.txt" put the path and filename of your .csv file. The advantage of this method is that you don't have to mess around with different filenames, or changing the ADO connection string, etc. The local file can always be the same name.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson