如何将PGN中的数据转换/解析/提取为电子表格/Google表格/Excel文件? [英] How do convert/parse/extract data from a PGN into a spreadsheet/google sheet/excel file?

问题描述

此问题的续集:来自chess.com的实时统计chess960?

所以假设我喜欢

https://api.chess.com/pub/player/gmwso/games/2020/12

或

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

会有很多这样的东西

  [UTCDate"2018.01.03"][WhiteElo"2706"][BlackElo"2900"" 

如何将这些数据保存到电子表格中，例如第1列是所有日期，第2列是相应的白色elo，第3列是黑色elo，col4是白色用户名，而col5是黑色用户名?

---

更新2:现在已修复.参见"json"与"preformed".哇.

更新1:看来，迈克·斯蒂尔森有一个答案

在此处提供示例

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

仅在对象棋960的情况下似乎存在问题.例如，考虑此播放器:将"gmwso"替换为播放器的用户名会产生奇怪的输出.我想混合使用Chess960和Chess的输出会更混乱.

解决方案

另一种更简单且集成度更高的方法来解析PGN文件

 /***解析一个PGN文件.** @param {range} url url* @param {range}要获取的xpath数据* @param {boolean}序列(如果将获取真实序列)* @customfunction*/函数readPGN(url，xpath，sequence){var result = []var reponse = UrlFetchApp.fetch(url);var json = reponse.getContentText();var data = JSON.parse(json);if(typeof xpath =='object'){var liste = xpath.join().split(，")}否则{var liste = xpath.split("|")}}对于(var i = 0; i< data.games.length; i ++){var prov = []var parts = data.games [i] .pgn.split(String.fromCharCode(10,10))var pparts = parts [0] .split(String.fromCharCode(10))for(var x = 0; x< pparts.length; x ++){pparts [x] = pparts [x] .replace('['，''''').replace(']'，'').replace(''，'" ::"')//"; key:" value}var donnees = JSON.parse('{'+ pparts.join('，')+'}')//mise au format jsonliste.forEach(function(chemin){prov.push(donnees.item(chemin))})if(序列){prov.push(parts [1])}result.push(省)}返回结果}Object.prototype.item = function(i){返回this [i]}; 

https://docs.google.com/spreadsheets/d/1asiDRDusHYSDXK0C8g

Sequel to this question: Live statistics chess960 from chess.com?

So suppose I go to like

https://api.chess.com/pub/player/gmwso/games/2020/12

or

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

there's gonna be a bunch of stuff like say

[UTCDate "2018.01.03"]
[WhiteElo "2706"]
[BlackElo "2940"]

How do I get this data into a spreadsheet like column 1 is all the dates, column 2 is the corresponding white elo, column 3 black elo, col4 white username and col5 black username?

---

Update 2: Fixed now. see the 'json' vs the 'preformed'. WOW.

Update 1: It appears Mike Steelson has an answer here, where the code is given as

=arrayformula( regexextract(split( substitute(substitute(substitute(getDataJSON(A1;"/games";"/pgn");"[";"");"]";"");"""";"") ;char(10));"\s.*") )

with an example given here

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

It appears there's a problem when it gets to the case of chess960 only. Consider for example this player: Replacing 'gmwso' with the player's username will yield a weird output. i imagine the output will be messier for mixed chess960 and chess.

解决方案

Another way, simplier and more integrated, to parse the PGN file

/**
 * Parse a PGN file.
 *
 * @param {range} url url 
 * @param {range} xpath data to be fetched 
 * @param {boolean} sequence if true sequence will be fetched 
 * @customfunction
 */
function readPGN(url,xpath,sequence) {
  var result=[]
  var reponse = UrlFetchApp.fetch(url);
  var json = reponse.getContentText();
  var data = JSON.parse(json);
  if (typeof xpath == 'object'){var liste = xpath.join().split(",")} else {var liste = xpath.split("|")}
  for (var i=0; i<data.games.length; i++) {
    var prov=[]
    var parts =  data.games[i].pgn.split(String.fromCharCode(10,10))
    var pparts = parts[0].split(String.fromCharCode(10))
    for (var x=0; x<pparts.length; x++){
      pparts[x]=pparts[x].replace('[','"').replace(']','').replace(' "','":"') // "key":"value"
    }
    var donnees = JSON.parse('{'+pparts.join(',')+'}') // mise au format json
    liste.forEach(function(chemin){
      prov.push(donnees.item(chemin))
    })
    if (sequence){prov.push(parts[1])}
    result.push(prov)
  }
  return result
}
Object.prototype.item=function(i){return this[i]};

https://docs.google.com/spreadsheets/d/1asiDRDusHYSDXK0c8gR_GuMIC1Nh0GNXiBpYctrLLlA/copy

这篇关于如何将PGN中的数据转换/解析/提取为电子表格/Google表格/Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！