I decided to use Regex to determine the number of columns (N) by counting the | character in the first row and subtracting 1, then splitting to rows and numbering the rows from 1 to N, then adding record IDs and re-shaping the data. This feels like it should stand up to a lot of variance in the input data format.
This was a challenging one.
I had to borrow from the Automatically Remove All Null Columns thread to make it work. I learned some new tricks. I'd never used Dynamic Select before and I never would have come up with the Dyanamic Rename/Transpose/Summarize/Rename portion of my solution had I not read that thread.