Review Chess Moves and Times by importing in Excel

[chiswickbridge]

I play online Chess games with various opponents.....On completion.....these games are saved on the Website's page.... So after accessing the source code, I can get the text code of the game that I played in the following format....


Every move starts with a left curly bracket and ends with a right curly bracket..... Every game can have several lines of these moves.....


What I need is to save these games in Excel Workbook, on separate sheets for future analysis.


My workbook is named "Chess_games" and save on my D Drive with the following path....D:\Games\Chess-games.xlsm


Here is how I will start....On completion of every game......I will copy the source code to a Notepad file named "Chess".....and save in the same above folder.......Number of lines will vary with every game... but will surely start and end with the Curly brackets.....


I need a VBA to import the data into a new sheet called "Import" and data import begins from A1....I have listed 2 moves for the data to be imported.......


Move / timeusage / timeUsageClient / fen / knotid / Children
c2c4 / 7067 / 7098 / rnbqkbnr\/pppppppp\/8\/8\/2P5\/8\/PP1PPPPP\/RNBQKBNR b HAha - 0 1 / 1 / [2]
e7e5 / 6553 / 6055 / rnbqkbnr\/pppp1ppp\/8\/4p3\/2P5\/8\/PP1PPPPP\/RNBQKBNR w HAha - 0 2 / 2 / [3]


As soon as the data is imported..... The VBA can rename the sheets......and so will have all my games saved....


I little extra help....


My workbook will have the First sheet as MENU, 2nd sheet as IMPORT, 3rd and subsequent sheets may be named by the VBA.... Commencing from Game 1 to Game 999...


Hope I am clear and Thanks for your help...


{"move":"c2c4","timeUsage":7067,"timeUsageClient":7098,"fen":"rnbqkbnr\/pppppppp\/8\/8\/2P5\/8\/PP1PPPPP\/RNBQKBNR b HAha - 0 1","knotId":1,"children":[2]},{"move":"e7e5","timeUsage":6553,"timeUsageClient":6055,"fen":"rnbqkbnr\/pppp1ppp\/8\/4p3\/2P5\/8\/PP1PPPPP\/RNBQKBNR w HAha - 0 2","knotId":2,"children":[3]},{"move":"b1c3","timeUsage":2461,"timeUsageClient":2227,"fen":"rnbqkbnr\/pppp1ppp\/8\/4p3\/2P5\/2N5\/PP1PPPPP\/R1BQKBNR b HAha - 1 2","knotId":3,"children":[4]},{"move":"b8c6","timeUsage":18570,"timeUsageClient":18415,"fen":"r1bqkbnr\/pppp1ppp\/2n5\/4p3\/2P5\/2N5\/PP1PPPPP\/R1BQKBNR w HAha - 2 3","knotId":4,"children":[5]},{"move":"g2g3","timeUsage":7298,"timeUsageClient":7077,"fen":"r1bqkbnr\/pppp1ppp\/2n5\/4p3\/2P5\/2N3P1\/PP1PPP1P\/R1BQKBNR b HAha - 0 3","knotId":5,"children":[6]},{"move":"g8f6","timeUsage":15190,"timeUsageClient":15037,"fen":"r1bqkb1r\/pppp1ppp\/2n2n2\/4p3\/2P5\/2N3P1\/PP1PPP1P\/R1BQKBNR w HAha - 1 4","knotId":6,"children":[7]},{"move":"f1g2","timeUsage":7002,"timeUsageClient":6752,"fen":"r1bqkb1r\/pppp1ppp\/2n2n2\/4p3\/2P5\/2N3P1\/PP1PPPBP\/R1BQK1NR b HAha - 2 4","knotId":7,"children":[8]},{"move":"f8b4","timeUsage":15775,"timeUsageClient":15625,"fen":"r1bqk2r\/pppp1ppp\/2n2n2\/4p3\/1bP5\/2N3P1\/PP1PPPBP\/R1BQK1NR w HAha - 3 5","knotId":8,"children":[9]},{"move":"e2e3","timeUsage":55497,"timeUsageClient":55265,"fen":"r1bqk2r\/pppp1ppp\/2n2n2\/4p3\/1bP5\/2N1P1P1\/PP1P1PBP\/R1BQK1NR b HAha - 0 5","knotId":9,"children":[10]},{"move":"b4c3","timeUsage":15538,"timeUsageClient":15385,"fen":"r1bqk2r\/pppp1ppp\/2n2n2\/4p3\/2P5\/2b1P1P1\/PP1P1PBP\/R1BQK1NR w HAha - 0 6","knotId":10,"children":[11]},{"move":"b2c3","timeUsage":3641,"timeUsageClient":3420,"fen":"r1bqk2r\/pppp1ppp\/2n2n2\/4p3\/2P5\/2P1P1P1\/P2P1PBP\/R1BQK1NR b HAha - 0 6","knotId":11,"children":[12]},{"move":"d7d6","timeUsage":16295,"timeUsageClient":16131,"fen":"r1bqk2r\/ppp2ppp\/2np1n2\/4p3\/2P5\/2P1P1P1\/P2P1PBP\/R1BQK1NR w HAha - 0 7","knotId":12,"children":[13]},{"move":"d2d4","timeUsage":23901,"timeUsageClient":23678,"fen":"r1bqk2r\/ppp2ppp\/2np1n2\/4p3\/2PP4\/2P1P1P1\/P4PBP\/R1BQK1NR b HAha - 0 7","knotId":13,"children":[14]},{"move":"c8f5","timeUsage":18336,"timeUsageClient":18178,"fen":"r2qk2r\/ppp2ppp\/2np1n2\/4pb2\/2PP4\/2P1P1P1\/P4PBP\/R1BQK1NR w HAha - 1 8","knotId":14,"children":[15]},{"move":"g1f3","timeUsage":38407,"timeUsageClient":38154,"fen":"r2qk2r\/ppp2ppp\/2np1n2\/4pb2\/2PP4\/2P1PNP1\/P4PBP\/R1BQK2R b HAha - 2 8","knotId":15,"children":[16]},{"move":"f5e4","timeUsage":20468,"timeUsageClient":20312,"fen":"r2qk2r\/ppp2ppp\/2np1n2\/4p3\/2PPb3\/2P1PNP1\/P4PBP\/R1BQK2R w HAha - 3 9","knotId":16,"children":[17]},{"move":"d4d5","timeUsage":13291,"timeUsageClient":13035,"fen":"r2qk2r\/ppp2ppp\/2np1n2\/3Pp3\/2P1b3\/2P1PNP1\/P4PBP\/R1BQK2R b HAha - 0 9","knotId":17,"children":[18]},{"move":"c6a5","timeUsage":23720,"timeUsageClient":23572,"fen":"r2qk2r\/ppp2ppp\/3p1n2\/n2Pp3\/2P1b3\/2P1PNP1\/P4PBP\/R1BQK2R w HAha - 1 10","knotId":18,"children":[19]},{"move":"d1a4","timeUsage":10187,"timeUsageClient":9933,"fen":"r2qk2r\/ppp2ppp\/3p1n2\/n2Pp3\/Q1P1b3\/2P1PNP1\/P4PBP\/R1B1K2R b HAha - 2 10","knotId":19,"children":[20]},


Thanks in Advance.....

 

[jimrward]

Can you export the data in standard pgn format this will then allow you to cut and paste into a number of chess engines for analysis purposes
The time maybe irrelevant depending on time controls unless you really need it

 

[chiswickbridge]

Yes...will do...basically...I need the data for analysis and future reviews....

 

[chiswickbridge]

I cannot export from the web page as it is for Premium members....I am just a newbie...

 

[SonofBelial]

Yes...will do...basically...I need the data for analysis and future reviews....

OK, so I know this is an old thread but here's a PowerShell script you can use to do this. I've used a Magnus Carlsen game from last year as an example:

# Store the PGN content as a herestring (a multi-line string) in a variable called $pgn
$pgn = @"
[Event "FTX Crypto Cup 2022"]
[Site "Miami USA"]
[Date "2022.08.18"]
[Round "4.4"]
[White "Le, Quang Liem"]
[Black "Carlsen, Magnus"]
[Result "0-1"]
[WhiteTitle "GM"]
[BlackTitle "GM"]
[WhiteElo "2728"]
[BlackElo "2864"]
[ECO "D11"]
[Opening "QGD Slav"]
[Variation "3.Nf3"]
[WhiteFideId "12401137"]
[BlackFideId "1503014"]
[EventDate "2022.08.15"]

1. d4 d5 2. c4 c6 3. Nf3 e6 4. Qc2 Nf6 5. g3 dxc4 6. Qxc4 b5 7. Qb3 Bb7 8. Bg2
a6 9. Be3 Nbd7 10. a4 c5 11. axb5 Bd5 12. Qc2 cxd4 13. Bxd4 axb5 14. Rxa8 Qxa8
15. O-O Be7 16. Nc3 Bc6 17. Nh4 Bxg2 18. Nxg2 b4 19. Nb5 O-O 20. Rc1 h6 21. Na7
Bd6 22. Nc6 e5 23. Be3 Nd5 24. Qf5 N7f6 25. Nxe5 Ne7 26. Qf4 Ng6 27. Nxg6 Bxf4
28. Ne7+ Kh7 29. Nxf4 Qe4 30. Nc8 Rd8 31. Nb6 b3 32. h3 Rd6 33. Kh2 g5 34. Nd3
Rxb6 35. Bxb6 Qxe2 36. Rc3 Nd5 37. Rxb3 Qc2 0-1
"@

# Split the PGN content into lines and extract lines containing property tags (e.g., [Event ...])
# Store the property tags in a variable called $pgnProperties
$pgnProperties = $pgn -split "`n" | Select-String "\[.+\]" | % { $_.ToString().Trim() }

# Split the PGN content into lines and extract lines containing moves (e.g., 1. d4 d5 ...)
# Store the move text in a variable called $pgnMoves
$pgnMoves = $pgn -split "`n" | Select-String "^\d+\. .+" | % { $_.ToString().Trim() }

# Extract the header names (e.g., Event, Site, Date, ...) from the property tags and join them using ","
# Store the resulting CSV header string in a variable called $csvHeaders
$csvHeaders = $pgnProperties -replace "\[(\w+) .+", "`$1" -join ","

# Extract the header values (e.g., "FTX Crypto Cup 2022", "Miami USA", "2022.08.18", ...) from the property tags and join them using '","'
# Store the resulting CSV values string in a variable called $csvValues
$csvValues = $pgnProperties -replace "\[\w+ (.+)\]", "`$1" -join '","'

# Combine the CSV header, CSV values, and moves into a variable called $csv
# Add newline (`n) between the headers and values, and enclose the values and moves in double quotes ("")
$csv = "$csvHeaders`n`"$csvValues`",`"$pgnMoves`""

# Save the resulting CSV content to a file named "chess.csv"
$csv | Set-Content "chess.csv"

 

[jdellasala]

Power Query is made for this:

let
    Source = Csv.Document(File.Contents("C:\Temp\Chess.txt"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    ReplacedValue0 = Table.ReplaceValue(Source,"{""move"":""","",Replacer.ReplaceText,{"Column1"}),
    SplitColumnByPosition = Table.SplitColumn(ReplacedValue0, "Column1", Splitter.SplitTextByPositions({0, 2}, false), {"Move From", "Move To"}),
    ReplacedValue1 = Table.ReplaceValue(SplitColumnByPosition,"""","",Replacer.ReplaceText,{"Move To"}),
    ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,"timeUsage:","",Replacer.ReplaceText,{"Column2"}),
    RenamedColumns0 = Table.RenameColumns(ReplacedValue2,{{"Column2", "Time Usage"}}),
    ReplacedValue3 = Table.ReplaceValue(RenamedColumns0,"timeUsageClient:","",Replacer.ReplaceText,{"Column3"}),
    RenamedColumns1 = Table.RenameColumns(ReplacedValue3,{{"Column3", "Time Usage Client"}}),
    ReplacedValue4 = Table.ReplaceValue(RenamedColumns1,"fen:""","",Replacer.ReplaceText,{"Column4"}),
    ReplacedValue5 = Table.ReplaceValue(ReplacedValue4,"""","",Replacer.ReplaceText,{"Column4"}),
    SplitColumnByDelimiter = Table.SplitColumn(ReplacedValue5, "Column4", Splitter.SplitTextByDelimiter("\/", QuoteStyle.Csv), {"Column4.1", "Column4.2", "Column4.3", "Column4.4", "Column4.5", "Column4.6", "Column4.7", "Column4.8"}),
    ReplacedValue6 = Table.ReplaceValue(SplitColumnByDelimiter,"knotId:","",Replacer.ReplaceText,{"Column5"}),
    RenamedColumns2 = Table.RenameColumns(ReplacedValue6,{{"Column5", "KnotId"}}),
    ReplacedValue7 = Table.ReplaceValue(RenamedColumns2,"children:","",Replacer.ReplaceText,{"Column6"}),
    ReplacedValue8 = Table.ReplaceValue(ReplacedValue7,"}","",Replacer.ReplaceText,{"Column6"}),
    RenamedColumns3 = Table.RenameColumns(ReplacedValue8,{{"Column6", "Children"}}),
    RemovedColumn7 = Table.RemoveColumns(RenamedColumns3,{"Column7"})
in
    RemovedColumn7

Results in this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Move From	Move To	Time Usage	Time Usage Client	Column4.1	Column4.2	Column4.3	Column4.4	Column4.5	Column4.6	Column4.7	Column4.8	KnotId	Children
2	c2	c4	7067	7098	rnbqkbnr	pppppppp	8	8	2P5	8	PP1PPPPP	RNBQKBNR b HAha - 0 1	1	[2]
3	e7	e5	6553	6055	rnbqkbnr	pppp1ppp	8	4p3	2P5	8	PP1PPPPP	RNBQKBNR w HAha - 0 2	2	[3]
4	b1	c3	2461	2227	rnbqkbnr	pppp1ppp	8	4p3	2P5	2N5	PP1PPPPP	R1BQKBNR b HAha - 1 2	3	[4]
5	b8	c6	18570	18415	r1bqkbnr	pppp1ppp	2n5	4p3	2P5	2N5	PP1PPPPP	R1BQKBNR w HAha - 2 3	4	[5]
6	g2	g3	7298	7077	r1bqkbnr	pppp1ppp	2n5	4p3	2P5	2N3P1	PP1PPP1P	R1BQKBNR b HAha - 0 3	5	[6]
7	g8	f6	15190	15037	r1bqkb1r	pppp1ppp	2n2n2	4p3	2P5	2N3P1	PP1PPP1P	R1BQKBNR w HAha - 1 4	6	[7]
8	f1	g2	7002	6752	r1bqkb1r	pppp1ppp	2n2n2	4p3	2P5	2N3P1	PP1PPPBP	R1BQK1NR b HAha - 2 4	7	[8]
9	f8	b4	15775	15625	r1bqk2r	pppp1ppp	2n2n2	4p3	1bP5	2N3P1	PP1PPPBP	R1BQK1NR w HAha - 3 5	8	[9]
10	e2	e3	55497	55265	r1bqk2r	pppp1ppp	2n2n2	4p3	1bP5	2N1P1P1	PP1P1PBP	R1BQK1NR b HAha - 0 5	9	[10]
11	b4	c3	15538	15385	r1bqk2r	pppp1ppp	2n2n2	4p3	2P5	2b1P1P1	PP1P1PBP	R1BQK1NR w HAha - 0 6	10	[11]
12	b2	c3	3641	3420	r1bqk2r	pppp1ppp	2n2n2	4p3	2P5	2P1P1P1	P2P1PBP	R1BQK1NR b HAha - 0 6	11	[12]
13	d7	d6	16295	16131	r1bqk2r	ppp2ppp	2np1n2	4p3	2P5	2P1P1P1	P2P1PBP	R1BQK1NR w HAha - 0 7	12	[13]
14	d2	d4	23901	23678	r1bqk2r	ppp2ppp	2np1n2	4p3	2PP4	2P1P1P1	P4PBP	R1BQK1NR b HAha - 0 7	13	[14]
15	c8	f5	18336	18178	r2qk2r	ppp2ppp	2np1n2	4pb2	2PP4	2P1P1P1	P4PBP	R1BQK1NR w HAha - 1 8	14	[15]
16	g1	f3	38407	38154	r2qk2r	ppp2ppp	2np1n2	4pb2	2PP4	2P1PNP1	P4PBP	R1BQK2R b HAha - 2 8	15	[16]
17	f5	e4	20468	20312	r2qk2r	ppp2ppp	2np1n2	4p3	2PPb3	2P1PNP1	P4PBP	R1BQK2R w HAha - 3 9	16	[17]
18	d4	d5	13291	13035	r2qk2r	ppp2ppp	2np1n2	3Pp3	2P1b3	2P1PNP1	P4PBP	R1BQK2R b HAha - 0 9	17	[18]
19	c6	a5	23720	23572	r2qk2r	ppp2ppp	3p1n2	n2Pp3	2P1b3	2P1PNP1	P4PBP	R1BQK2R w HAha - 1 10	18	[19]
20	d1	a4	10187	9933	r2qk2r	ppp2ppp	3p1n2	n2Pp3	Q1P1b3	2P1PNP1	P4PBP	R1B1K2R b HAha - 2 10	19	[20]
Chess

It can also be turned into a function:

( SrcFile as text ) =>
let
    Source = Csv.Document(File.Contents(SrcFile),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    ReplacedValue0 = Table.ReplaceValue(Source,"{""move"":""","",Replacer.ReplaceText,{"Column1"}),
    SplitColumnByPosition = Table.SplitColumn(ReplacedValue0, "Column1", Splitter.SplitTextByPositions({0, 2}, false), {"Move From", "Move To"}),
    ReplacedValue1 = Table.ReplaceValue(SplitColumnByPosition,"""","",Replacer.ReplaceText,{"Move To"}),
    ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,"timeUsage:","",Replacer.ReplaceText,{"Column2"}),
    RenamedColumns0 = Table.RenameColumns(ReplacedValue2,{{"Column2", "Time Usage"}}),
    ReplacedValue3 = Table.ReplaceValue(RenamedColumns0,"timeUsageClient:","",Replacer.ReplaceText,{"Column3"}),
    RenamedColumns1 = Table.RenameColumns(ReplacedValue3,{{"Column3", "Time Usage Client"}}),
    ReplacedValue4 = Table.ReplaceValue(RenamedColumns1,"fen:""","",Replacer.ReplaceText,{"Column4"}),
    ReplacedValue5 = Table.ReplaceValue(ReplacedValue4,"""","",Replacer.ReplaceText,{"Column4"}),
    SplitColumnByDelimiter = Table.SplitColumn(ReplacedValue5, "Column4", Splitter.SplitTextByDelimiter("\/", QuoteStyle.Csv), {"Column4.1", "Column4.2", "Column4.3", "Column4.4", "Column4.5", "Column4.6", "Column4.7", "Column4.8"}),
    ReplacedValue6 = Table.ReplaceValue(SplitColumnByDelimiter,"knotId:","",Replacer.ReplaceText,{"Column5"}),
    RenamedColumns2 = Table.RenameColumns(ReplacedValue6,{{"Column5", "KnotId"}}),
    ReplacedValue7 = Table.ReplaceValue(RenamedColumns2,"children:","",Replacer.ReplaceText,{"Column6"}),
    ReplacedValue8 = Table.ReplaceValue(ReplacedValue7,"}","",Replacer.ReplaceText,{"Column6"}),
    RenamedColumns3 = Table.RenameColumns(ReplacedValue8,{{"Column6", "Children"}}),
    RemovedColumn7 = Table.RemoveColumns(RenamedColumns3,{"Column7"})
in
    RemovedColumn7

at which point the function can be double clicked in the Queries & Connections pane where the file path can be entered to create the table:

Good luck!