import data from the web

[thegame8311]

Greetings all

Is there a way to import data from the web straight to foxpro or is it easier to go through excel to foxpro

 

[Mike Lewis]

There are several ways of doing this. One option would be to use the Internet Transfer Control, which is an ActiveX control that comes with VFP. Drop it on a form, change its name to, say, oleInet, and execute code similar to the following:

lcURL = <URL of a page on the website)
lcPage = THISFORM.oleInet.OpenURL(lcURL)

This will give you the contents of the page as a text stream, complete with HTML tags, etc.

Does that meet your requirements?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

There are some ways dedicated to data transfer via internet: Web services and vendor APIs based on wither web services or other means.

Besides that you can of course scripe off data from webpages, crawling them like google or other search engines. But that can also be illegal, if you don't repect limits. For example if you programmaticall call googles search several times, it will stop working for the IP for a certain time and instead return a warning.

Scriping off some data, eg prices of articles or stock, is possible, but using an official api is making this faster and also with less traffic bandwidth usage than scriping off some net data from a whole web page.

For example google offers very many interfaces to developers here:

https://developers.google.com/

And that also includes searches. Many things already are offered in that specific way, you just have to look around for apis, sdks, developer or webmaster offerings of websites.

Bye, Olaf.

 

[Dan Freeman]

Is there a way to import data from the web straight to foxpro or is it easier to go through excel to foxpro

Yes.

To get a more specific answer you need a more specific question.

 

[thegame8311]

actually that small snippet of code is pretty much what I was looking for, thanks.

To be more specific I wanted to import data from the web into a foxpro app with a command button, that I am creating(not for distribution) just for fun and to get use to using foxpro.

 

[Olaf Doschke]

I wanted to import data from the web into a foxpro app with a command button, that I am creating(not for distribution) just for fun and to get use to using foxpro.

What are you referrring to with "data from the web", this is so unspecific, that I can only again second dan, you need to be more specific on WHAT data from the web you want to access.

Web pages? WEb databases like mysql? Web services?
What?

There is not a small snippet doing all of these, so you need to be very specific on what you want sample code for.

Bye, Olaf.

 

[Olaf Doschke]

That said depending on what you think of, this is not the core of vfp development, vfp is mainly a tool to create desktop applications with local data.

Remote data access is built in and works the same way with some sql server setup locally, in the lan or in the web, both for ms sql server, mysql, firebird, postgresql or any other database server.

This mainly needs configuration of the databases to allow remote acccess in general and of course login credientials specifically, but does not make any other difference to vfp, remote is remote, no matter how remote exactly.

Bye, Olaf.

 

[Mike Lewis]

Olaf and Dan have made a good point.

In my response, I was only showing how to retrieve a web page. I was assuming the "data" was somehow embedded in the page, perhaps in the form of a table. And that it would be parsed out, using VFP's rich string-handling functions.

But if we are talking about "real" data, for example, data held in a database, that's an entirely different issue.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[thegame8311]

oh okay I apologize for not being specific, I am referring to using Foxpro to stream data in from a webpage and parse out the parts that are not html codes, such as names and numbers, basically the actual text of the page.

If you need anymore details I will be happy to explain more of what I am trying to accomplish

sorry for being cryptic I was intending to, I will try to think out my question more carefully

 

[Mike Lewis]

So, you're happy you can retrieve the contents of the page (HTML tags, headers, and the actual text of the page)? And now you want to parse out the text?

That's simply a matter of using VFP's string-handling functions. STREXTRACT() is particularly useful in this type of work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[thegame8311]

Strextract is great but how do you deal with this:

 strextract(x, "<a href="/>", "")

 

[Dan Freeman]

First you fix the typo.

What did you need to know/do?

 

[thegame8311]

I need to know how to deal with there being a double quote in the middle of my begin delimiter

 

[JRB-Bldr]

x = strextract(x, '<a href="/>', "")

Note that VFP uses both Single Quotes and Double Quotes.

If you want to 'bracket' double quotes, use single quotes
If you want to 'bracket' single quotes, use double quotes

Good Luck,
JRB-Bldr

 

[Mike Lewis]

TheGame,

If you're trying to extract the target of a hyperlink, your example is not quite right. Assuming x contains the text of the page, you need to do this:

lcHyper = STREXTRACT(x, '<a href="', '">')

But it's not quite that simple. If the <a> tag also contains other attributes, such as 'target="_blank"', you will have to filter that out as well.

Also, this code will only give you the first hyperlink. To get them all (perhaps in an array), you will have to do it in a loop. Something like this:

lnCount = OCCURS(x, '<a href="')
DIMENSION laHyper(lnCount)
FOR lnI = 1 to lnCount
  laHyper(lnI) = STREXTRACT(x, '<a href="', '">', lnI)
ENDFOR

This is not tested. It's only meant to give you the general idea.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

No need to apologize, when we're just giving the feedback we can't answer a question too general. Your concept of data is just differing from a develop perspective.

Your first posting also implied going from a web page to excel is easy. I wonder what you have in mind with that. Perhaps what is described here as manual steps?

http://www.mrkent.com/tools/converter/index.htm.

Maybe you know an even easier way.

Mike Lewis then already has made the correct assumption you want to extract parts of a web page.

As you want the html tags removed, the easiest would be:

* use the ie browser
oIE = createobject("internetexplorer.application")
* to navigate to a web page of interest
oIE.navigate2("[URL unfurl="true"]http://www.tek-tips.com")[/URL]
* wait for the page to load
Do While oIE.readystate<>4
   Doevents
Enddo
* extract the net text:
? oIE.Document.body.innertext

Just a few lines, but that makes you rather learn OLE automation than foxpro. As said web page handling is not in the core concept of foxpro, it's not a starter to learn VFP.

In regard of the specific problem you want to solve, not striping off the html before parsing the text is the better concept. Because of the M in HTML. HTML is a Markup language, and the markup is what makes it more than just pure text, html tags specify what soemthing is, a heading (H1,H2,...), a paragraph (p), a table (table) a table row (tr) a table cell (td) and so you have both the net text the user sees in the browser and a conceptual description what each part of the docuemnt is.

Parsing out all table cell contents then is using STEXTRACT() with <td> and </td> as the delimiters. And you might even find better delimiters of the net data you want to extract.

Bye, Olaf.

 

[thegame8311]

nice these are all really great, but now for my issue with AT()

I am using it in a loop like this

SUBSTR(z, 1, AT(",",z)

which returns the same thing from the first line every time, for example this is my list:
Doe, John
Smith, Joan

what is returned is:
Doe
Doe

what I want is:
Doe
Smith

Any ideas or do you need the rest of the code for the loop

 

[mmerlinn]

If you are always getting "Doe", did you remember to INCREMENT your counter?

Also, fix your typo in the code in the last post.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Mike Lewis]

You need to add a third parameter to AT(). This will indicate the occurrence. In other words, to get the first occurrence of the substring, you pass 1; to get the second, you pass 2; and so on.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If you need to split the string in z at any comma, use ALINES(arrayofthelines,z,","), then you find the parts in the array "arrayofthelines".

Bye, Olaf.