ServerRequest "GET" 1

[AlastairP]

I am trying to get CSV from a remote server, and they have provided sample code. I am trying to convert this into VFP code and got stuck on one bit.
Not sure how to do the server request "GET" in VFP or if there is any equivalent function to do this?

data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=1&apikey=" & apikey, "")

Full code:

Public Function DownloadAndImport(what As String, tablename As String, Secification As String) As String
    Dim data As String
    Dim page As Integer
    Dim fs As Object ''FileSystemObject
    Dim tsOut As Object  ''TextStream

    'Your Company's API Key goes below
    apikey = ""
    server = "[URL unfurl="true"]http://xxxxxx.xxx.com.au/"[/URL]

    page = 0
    lastPage = False

    'download the complete file first

    Set fs = CreateObject("Scripting.FileSystemObject")
    tempFileName = "C:\windows\temp\" & tablename & ".csv"
    Set tsOut = fs.CreateTextFile(tempFileName, True)

    MsgBox server & "download/" & what & "/" & page & "?onlyheader=0&apikey=" & apikey
    End

    'Get Header Row
    data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=1&apikey=" & apikey, "")
    If InStr(data, "Error") <> 0 Then
       lastPage = True
    Else
       tsOut.WriteLine data
    End If

    'Get csv data
    While lastPage = False
        SysCmd acSysCmdSetStatus, "Downloading To file " & what & ", Page " & page

        data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=0&apikey=" & apikey, "")

        'Check for an Error, meaning it has returned no records
        If InStr(data, "Error") <> 0 Then
            lastPage = True
        Else
            tsOut.WriteLine data
            page = page + 1
        End If
        DoEvents
    Wend

    tsOut.Close

    'Import CSV Data into table
    SysCmd acSysCmdSetStatus, "Importing File " & what & " to table " & tablename & ", Page " & page
    DoCmd.TransferText acImportDelim, Secification, tablename, tempFileName, True

    SetAttr tempFileName, vbNormal
    ' Kill tempFileName


    SysCmd acSysCmdClearStatus
End Function 

 

[atlopes]

You may use a WinHttp.WinHttpRequest object.

LOCAL ServerRequest AS WinHttp.WinHttpRequest

m.ServerRequest = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")

* the first call
m.ServerRequest.Open("Get", m.Server + "download/" + m.What + "/0?onlyheader=1&" + "apikey=" + m.ApiKey, .F.)
m.ServerRequest.Send()
m.Data = "" + m.ServerRequest.ResponseBody

* etc.

* the subsequent calls
m.ServerRequest.Open("Get", m.Server + "download/" + m.What + "/" + LTRIM(STR(m.Page)) + "?onlyheader=0&" + "apikey=" + m.ApiKey, .F.)
m.ServerRequest.Send()
m.Data = "" + m.ServerRequest.ResponseBody

* etc.

 

[AlastairP]

This is great!
Thanks

Alastair

 

[atlopes]

Glad to hear!

Do you know in advance the structure of the CSV file?

 

[Mike Lewis]

Atlopes, it's good that you were able to provide a solution so quickly.

I see that you have been active in this forum for about three years now, and you have posted many helpful replies. With that in mind, I was wondering if you would now consider coming out from behind your pseudonym.

Let me explain.

There is a feeling among some of the forum regulars that it would be helpful if the more active forum members used their real names in their posts. This is partly for the sake of openness and transparency, and partly to help people feel part of a community rather than just a group of anonymous posters hiding behind invented names.

This is only a suggestion, and by no means compulsory. But the suggestion is that the more regular members (such as yourself) change their "screen names" to show their actual names. You can do this by going to My Stuff (at the top of the screen) and then My Profile.

Of course, if you have a good reason to remain anonymous, that's fine. There's no pressure. But please at least consider the idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[atlopes]

With a big grin, Mike: atlopes is not a pseudonym, in fact it's my real name, like mlewis would be a representation of yours. There are some other VFP fora in which my first name is fully spelled out in my user account, as well as in my Bitbucket and Github accounts, to which I pointed at TT more than once, and even here I sign with my first name, occasionally.

António (A. T. Lopes)

 

[AlastairP]

Hi Antonio,

Well I know a bit about the structure. I can also get the file as JSON, though I have had no experience with JSON files and limited with CSV.
It has a field name header row.
I have documentation of the original field datatypes & sizes.
I am creating DBF tables with these data types and will update the tables with new CSV records.
As usual I just work it all out as I go

Alastair

 

[Mike Lewis]

António,

Just knowing that your name is António makes a big difference. It means that we can address you by name, which is the most natural way of talking to people. It makes the whole thing more friendly.

If a friend of yours went into a bar and saw you drinking a beer, wouldn't you expect them to say something like "Hi, António" or possibly "Good evening, Mr Lopes." Nobody would actually address you as "atlopes" - any more than they would address me as "mlewis".

If you don't want to change your screen name, that's fine. But how about putting your full name - or at least your first name -into your Tek-Tips signature (from My Stuff -> My Profile).

Just a thought.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads