An example of this data is as follows:
Rather than save the data to a file and then import, the data can simply be cut and pasted into a clean Google Sheet:
Notice the past icon (the clipboard) with a down triangle indicating a menu. Clicking on the down triangle displays a menu from which Split text to columns should be selected:
Clicking on Split text to columns displays the following:
Clicking on Detect automatically displays the following:
In the Custom separator textbox enter a vertical bar:
Click on enter and the delimited data will populate the columns using the vertical bar character as a separator:
The delimited data values each contain a leading and trailing single quote. To remove these click the top left of the sheet (to the left of A and above 1) to select all data elements:
From the Edit menu select Find and replace:
Using Find and replace the single quotes can be removed: