Review Chess Moves and Times by importing in Excel

chiswickbridge

Board Regular
Joined
Feb 2, 2013
Messages
127
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.....
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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:
Rich (BB code):
# 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"
 
Upvote 0
Power Query is made for this:
Power Query:
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
ABCDEFGHIJKLMN
1Move FromMove ToTime UsageTime Usage ClientColumn4.1Column4.2Column4.3Column4.4Column4.5Column4.6Column4.7Column4.8KnotIdChildren
2c2c470677098rnbqkbnrpppppppp882P58PP1PPPPPRNBQKBNR b HAha - 0 11[2]
3e7e565536055rnbqkbnrpppp1ppp84p32P58PP1PPPPPRNBQKBNR w HAha - 0 22[3]
4b1c324612227rnbqkbnrpppp1ppp84p32P52N5PP1PPPPPR1BQKBNR b HAha - 1 23[4]
5b8c61857018415r1bqkbnrpppp1ppp2n54p32P52N5PP1PPPPPR1BQKBNR w HAha - 2 34[5]
6g2g372987077r1bqkbnrpppp1ppp2n54p32P52N3P1PP1PPP1PR1BQKBNR b HAha - 0 35[6]
7g8f61519015037r1bqkb1rpppp1ppp2n2n24p32P52N3P1PP1PPP1PR1BQKBNR w HAha - 1 46[7]
8f1g270026752r1bqkb1rpppp1ppp2n2n24p32P52N3P1PP1PPPBPR1BQK1NR b HAha - 2 47[8]
9f8b41577515625r1bqk2rpppp1ppp2n2n24p31bP52N3P1PP1PPPBPR1BQK1NR w HAha - 3 58[9]
10e2e35549755265r1bqk2rpppp1ppp2n2n24p31bP52N1P1P1PP1P1PBPR1BQK1NR b HAha - 0 59[10]
11b4c31553815385r1bqk2rpppp1ppp2n2n24p32P52b1P1P1PP1P1PBPR1BQK1NR w HAha - 0 610[11]
12b2c336413420r1bqk2rpppp1ppp2n2n24p32P52P1P1P1P2P1PBPR1BQK1NR b HAha - 0 611[12]
13d7d61629516131r1bqk2rppp2ppp2np1n24p32P52P1P1P1P2P1PBPR1BQK1NR w HAha - 0 712[13]
14d2d42390123678r1bqk2rppp2ppp2np1n24p32PP42P1P1P1P4PBPR1BQK1NR b HAha - 0 713[14]
15c8f51833618178r2qk2rppp2ppp2np1n24pb22PP42P1P1P1P4PBPR1BQK1NR w HAha - 1 814[15]
16g1f33840738154r2qk2rppp2ppp2np1n24pb22PP42P1PNP1P4PBPR1BQK2R b HAha - 2 815[16]
17f5e42046820312r2qk2rppp2ppp2np1n24p32PPb32P1PNP1P4PBPR1BQK2R w HAha - 3 916[17]
18d4d51329113035r2qk2rppp2ppp2np1n23Pp32P1b32P1PNP1P4PBPR1BQK2R b HAha - 0 917[18]
19c6a52372023572r2qk2rppp2ppp3p1n2n2Pp32P1b32P1PNP1P4PBPR1BQK2R w HAha - 1 1018[19]
20d1a4101879933r2qk2rppp2ppp3p1n2n2Pp3Q1P1b32P1PNP1P4PBPR1B1K2R b HAha - 2 1019[20]
Chess

It can also be turned into a function:
Power Query:
( 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:
1681470122688.png

Good luck!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top