What is the easiest way to convert this data from a website into a VFP table? 7

[german12]

I found an interesting list of chess players on the net.

Link

The list contains 5000 names of chess players from all over the world.

It starts with the current world champion in chess (Magnus Carlsen, Norway).
and shows the respective rating number of all players in the different game modes (classic, rapid and blitz chess) as well as the age of these persons and their nationality.

You can also mark the data and thus accept it (e.g. transfer it to a Word file) - but maybe there is a better/easier way.

I am very grateful for every idea.
Thanks

Klaus

Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus,

There are several ways of doing this. The one that I would use would be to go via Excel.

1. From Excel's Data tab, choose "From Web" (or maybe "Web Query", depending on your version).

2. In the resulting dialogue, set the Address to the URL of the relevant page. Click Go. (Ignore any error warnings.)

3. Click the little yellow arrow next to the table in question. Click Import.

4. The data will now appear in the worksheet. You can adjust it as necessary, then save it as a CSV file.

5. Import the CSV into VFP in the usual way.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Klaus,

I just tried doing the above steps with the chess players site, and it didn't work very well.

However, I found I could simply copy and paste the table from the site into Excel, and that worked perfectly. From there, it would be an easy step to save the worksheet as a CSV and import it into VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Great, Mike!
That was again a good hint from you.
As the net-pages allow to show 100 lines at once, it would be not much effort to get 5000 lines by drag and drop.
When I have transferred them from EXCEL into a vfp-table I can do a lot more - and will inform you about the result - just for fun - that is what we all need most today.

Thank you very much.

Regards
Klaus






Peace worldwide - it starts here...

 

[atlopes]

Klaus, you may fetch the data from the page source code and transform it into something that VFP can digest.

*
* first step, fetch the data from the server
*

LOCAL HTTP AS MSXML2.ServerXMLHTTP60

m.HTTP = CREATEOBJECT("MSXML2.ServerXMLHTTP.6.0")
m.HTTP.Open("Get", "[URL unfurl="true"]https://www.chess-international.com/fide/",[/URL] .F.)
m.HTTP.Send()

*
* then, extract the table data from the source
*

LOCAL SourceData AS String

m.SourceData = STREXTRACT(m.HTTP.Responsetext, "<tbody>", "</tbody>")

*
* change rows into lines, cells into tab delimited columns
*

m.SourceData = STRTRAN(m.SourceData, "<tr>", "")
m.SourceData = STRTRAN(m.SourceData, "</tr>", 0h0d0a)
m.SourceData = STRTRAN(m.SourceData, "<td>", "")
m.SourceData = STRTRAN(m.SourceData, "</td>", 0h09)
m.SourceData = STRTRAN(m.SourceData, 0h090d0a, 0h0d0a)

*
* create a cursor to hold the data
*

CREATE CURSOR Chess (PlayerName Varchar(50), PlayerNumber Integer, Federation Varchar(12), Sex Char(1), ;
	Classic Integer, Rapid Integer, Blitz Integer, Age Integer)
	

*
* save the source into a temporary file
*

LOCAL Temp AS String

m.Temp = ADDBS(SYS(2023)) + "~chess.txt"
STRTOFILE(m.SourceData, m.Temp)

*
* and append into the cursor
*

APPEND FROM (m.Temp) DELIMITED WITH TAB

*
* erase the temporary file
*

ERASE (m.Temp)

*
* the data is available in a cursor, now
*

GO TOP
BROWSE

Resulting in

 

[german12]

atlopes - I didn't think it could get any better because I was already very happy with Mike's idea.

But it's even better, as your program shows. Drag and drop are no longer necessary and all data is immediately available.
This is what makes this platform so valuable - when there are several solutions to a problem. Everyone can learn something new with it - and it works best without personal insults etc.

Thank you very much - now I really can't imagine how it could be any better.

greetings from Germany
Klaus

Peace worldwide - it starts here...

 

[Steve Meyerson]

Hi Klaus,

Just curious. Were you able to copy all the data in a single transfer? If so, how?

Transfer of a single page is simple. However, the site showed me only 25 records per screen, which would require 200 transfers. I couldn't find a "From Web" or "Web Query" option in my Excel (2016) as Mike suggested.

Thanks.

Steve

 

[Mike Lewis]

I wasn't suggesting that the transfer to Excel should be done by drag-and-drop, but rather by copying and pasting, which I think would be easier. That said, I just noticed that, as Steve pointed out, the page only displays a maximum of 100 rows at a time, which makes it more difficult (25 rows by defualt, but you can select 100 from the combo just above the table).

Steve: Here is the "From Web" button on the Data tab (but this is Excel 2010, so it might be out of date).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Hi Mike and Steve
thank you for further details concerning this approach via EXCEL.

This is the history, what I did:


First time I tried it with my version - which is EXCEL 2013 -

but got a script-error - and gave up. (too early as I know it now.)
(maybe because Mike wrote that it was not satisfied in his version of EXCEL)

Therefore I used the web-page - and saw that the entries per page can be advanced to 100 lines per page.
I marked a whole page (CTRL-C) and copied into EXCEL with (CTRL-V)
Sorry - due to my language I would have named this "drag and drop" , but "copy and paste" is obviously the right designation.

If I had continued that 50 times, I would have had all the data.

Then atlopes suggestion came in here - and it worked immediately.
All data were in a cursor.

Now, when you explained more - I tried it again with EXCEL's "data from WEB" but this time I ignored the script error - and now I see that
it worked after a moment.(about 30 seconds)

The result:

Now I also understand the script error - if you see the first rows in Excel. The empty lines in the header may have been the cause.


I hope I described in enough detail what my thoughts were.

In any case, I learned a lot again (I still use Lotus 1-2-3 spreadsheet, so I had never pulled data from the net with Excel before.)

There are many roads that lead to Rome - if only you always knew them....

Thank you again

Keep healthy, guys...

Klaus













Peace worldwide - it starts here...

 

[Mike Lewis]

I still use Lotus 1-2-3 spreadsheet, so I had never pulled data from the net with Excel before.

Fascinating. You must be one of the last. I think the last release of 1-2-3 was the millennium edition, which was discontinued in the early 2000s. I used the DOS version up to the late 1980s, with a third-party add-on that allowed you to choose fonts and limited graphics for printing. I really liked 1-2-3, but it could never compete with Excel.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Looking into the source code I see a script part makes use of

https://cdn.datatables.net/

So this ranking list is a DataTable fetched fromm the CloudFlare network and I guess with a little research about how to use this API you could get at the raw data in one request. Because while this DataTableAPI is mainly there to provide data as a HTML gadget this page uses, one major function is to filter/select data for pagination and you can also opt for all data at once.

What's in the way with such things is that it requires an API key and so you might only get at the paginated single pages of data.

I would have guessed something as official and public as the chess ranking list would be available somewhere as public open data, ie as a CSV file. It seems many sites only list the top 100.

Chriss

 

[atlopes]

Were you able to copy all the data in a single transfer? If so, how?

The page includes 5000 entries. The page scripting controls the selection and navigation, but the data are all there.

 

[Steve-vfp9user]

One thing that might help you with eventually sorting the data in alphabetical order (if in fact you need to do that), is this.

You currently have a column with Forename, Surname

Carlsen, Magnus
Kasparov, Garry
Firouzja, Alireza
Ding, Liren
Aronian, Levon
Caruana, Fabiano
So, Wesley
Rapport, Richard
Giri, Anish
Nepomniachtchi, Ian
Mamedyarov, Shakhriyar
Vachier-Lagrave, Maxime
Dominguez Perez, Leinier
Radjabov, Teimour
Kramnik, Vladimir
Anand, Viswanathan
Duda, Jan-Krzysztof
Nakamura, Hikaru
Karjakin, Sergey
Wang, Hao
Grischuk, Alexander
Topalov, Veselin
Wei, Yi
Andreikin, Dmitry
Vitiugov, Nikita

If you add two columns after the first column and start by adding the first surname (Carlsen) in column2. Now press CTRL + E. This will automatically populate the whole of column two with the surnames.

Now add the first forename in column 3 and press CTRL + E again.

You should now have all the forenames in column3.

Hope that helps prior to importing the data into a table.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[german12]

Mike

I agree for the time being - but in the beginning Lotus 1-2-3 was superior compared to the first versions of Excel.
That was all a long time ago, but I remember that Lotus already had a cross-product BASIC Scripting Language, the input was also more comfortable, you always had to enter a "+" for numbers in Excel.
In any case, I was able to organize a lot of things in the company with Lotus 1-2-3 back then

e.g.
business statement sheet
forecast
result analysis
estimates
payroll accounting etc.

Only in later years did Microsoft adopt many features from Lotus 1-2-3

Chriss

I haven't found a bigger list than this either, but in this list there are at least 5000 of the strongest players worldwide who are organized in FIDE.
Of course, this also leads to the question of how many chess players there are in the world.
I found this:

"No one knows the answer. Not even close. And no one should claim to know the answer," -- Edward Winter

Is chess the most popular sport in the world?

No one can say for sure, but here are some interesting numbers:

Estimated number of chess players: 600,000,000

This often quoted number is the number of players who played a game of chess in 2012 according to FIDE (the world chess federation)2. FIDE refers to a survey by YouGov.

Other estimates are as high as one billion chess players, but this appears to be based on word of mouth and is as inaccurate as the previous FIDE3 estimate. While no one can ever tell the true number of players, it is clear that chess is and has always been one of the most popular games in the world.

Link

Of course there are lists for organized chessplayers (like me).
In Germany there are about 89,000 persons organized in chess-clubs.

There is a big database existing -
Link

That link also allows you to search with parameters

Thanks
Klaus















Peace worldwide - it starts here...

 

[german12]

Steve

The problem of separating surnames and first names is very common.
I didn't know your hint to do this within Excel.

Very convenient!

Thanks
Klaus

Peace worldwide - it starts here...