I avoided having to write a programming script to convert a PGN games file into a spreadsheet, but I used a word processing application instead.
The WP app needs to support regular expressions (regex), which allow you to use variables as well as the usual literals. Basically, this is activated by setting a checkbox in the Find/Replace dialog(s), but you need to use special characters to specify how you want the text manipulated. (The helpfile on "regex" explains how, with examples.)
The key is that each variable in a PGN game header stands on its own line. To concatenate them all for use by a spreadsheet, you need to convert the paragraph separator characters, which I'll call [Para] characters, into [Tab] characters.
But first, knowing that all PGN games are separated by a blank line indicates there are two para characters separating them. This makes the [Para] chars ambiguous, except for the pairing characteristic when they're used for game separation. You want the games on separate rows in your spreadsheet, so you need to preserve this game separation info and restore it once the concatenation is complete.
So, first I copied (or opened) the PGN file in(to) MS Word.
Then I regex-replaced all the double-para characters with a special-character pair I reserved for that purpose: "&&". That's the delimiter between games.
(Note: As you manipulate the file in Word, it will immediately start looking weird. Ignore that, and just execute the steps.)
Next, I regex-replaced all the "][Para]" End-of-PGN-Field character pairs with a special-character pair I reserved for that purpose: "]%". The "]" is just a target-finder; it specifies that only the lines ending in a "]" are to be concatenated. The "%" becomes the delimiter between the PGN fields, and between the last field and the start of the game score.
Next, you have to concatenate the moves, which have been arbitrarily split by the PGN file creation software so that they're human-readable using a simple text processor, like Notepad.
To do this, I regex-replaced all remaining [Para] chars with space " " chars. This concatenates the game scores, with at least one space between the end of one line and the beginning of the next from the PGN file. Note that this has no effect on the PGN header fields or the game separations; those were already converted to special characters to prevent them from being affected by this step.
Next I regex-replaced the "%" characters with "[Tab]" characters so the PGN fields will load into separate columns in the spreadsheet when the time comes.
Finally, I regex-replaced the "&&" character pairs with [Para] chars so that each game would be on its own row in the spreadsheet.
Finally, I selected all (you can use "Select-All", instead of trying to drag-select over sometimes 100's of pages, but even that can be done by placing the cursor at the start of the text [Ctrl]-[End]) of the text from the document, opened a fresh spreadsheet, and pasted the text.
A few caveats:
- If you find that some of the PGN games have one set of PGN header fields, and some of them have a different set (this shouldn't happen, but might if your PGN file creator adds uncommon fields only for the games that came with that data, and omits them for others), then you can easily detect the disparities by just looking for misalignments in one of the last PGN file columns in the spreadsheet. If this affects only a small number of rows, you can generally correct it with some manual copy-pasta work in the spreadsheet to realign the fields. I always dropped any PGN field that only a few games had.
Before you undertake the manual process, assess the magnitude of the effort. How many rows are non-conforming to the standard content of the majority of the games? If it's a lot, and you don't need the unusual fields, it may be more efficient to just repeat the text file conversion process, but precede it by first removing all of these non-conforming fields and their data. Again, you can use a regex-replace to specify a starting "[", the name of the field plus a [wildcard] character to specify all of the non-essential text, as well as a "]", and then just replace them all with nothing.
Some may observe that if we had known this would be a problem before beginning the first conversion cycle, we could have saved that effort. That's true, but it turns out the most reliable way to find the anomalies is to produce a pilot version of the spreadsheet, and look for the misalignments of PGN fields caused by the interjection of the unusual fields' data in the anomalous rows. This is comprehensive, 100% effective at avoiding false positives and false negatives, and fairly easy. In the conversion process itself, once a step has been executed and verified to operate correctly, can be easily repeated using shortcuts like "Last Used" dropdown box selections in the Regex dialog box, or even automated with simple macros or one master macro (though in that case we're back to programming).
- Sometimes there are anomalies in PGN game files that cause a game (or even swathes of them) not to be recognized by a chess reader. (I'll leave to you whether it's worth pursuing and addressing how they occurred.) Therefore, you should always get a count of the PGN games that are supposed to be in the file by counting the number of occurrences of a telltale, standard PGN field (I like to use the "White" field). To get the count, load (a copy of) the PGN file into your word processor and do a replace of "White" with "White". The file will be identical, but the word processor will report the number of replacements.
Then load the file into a chess reader, and check the number of games it reports in the file. If the numbers don't match (the reader's count cannot be higher, but it can be lower), I always isolate the games that the chess reader can't read and try to recover them. Most of the time, it's just a matter of correcting some character anomaly, a missing field delimiter "[" or "]", or (rarely) a missing required PGN field.
If this problem exists, it may cause the reader to stumble and be unable to read several perfectly intact games after the offending game; don't assume that subsequent "unreadable" games all have problems. Often, fixing the first in the set and then trying again magically makes all of them readable.
- PGN readers are (by the specification in the standard) very tolerant of lots of sloppiness, so they ignore most disparities between one game and the next, including the order of fields. If you get all of the PGN games in a file from one source, then such disparities shouldn't appear at all. But if you concatenate games from different sources (which I highly discourage, for this reason), they are likely to show up. Then, trying to get the data from multiple sources to align in the right spreadsheet columns for all of the rows can be a real exercise.
Better to load PGN files from different sources into separate spreadsheet tabs, and then merge them later after any column inconsistencies between them have been identified and sorted out.
Why do this analysis prep work at all?
I've used it to find games that met several criteria:
- Upsets, based on the underdog's ELO deficit vs their opponent
- Miniatures (games with decisive results in under [game-length-limit] moves.)
but the ability to sort, filter and calculate based on the PGN fields alone has made it worthwhile in my research.
