How can I parse a multi-value column in a csv?

[reguzik]

How can I parse a multi-value column in a csv?

[reguzik]

On FMNH Collections pages and other search-sites for collections data, you can often download search results as a csv. However, data that was formatted in multi-value tables in EMu are often concatenated into a single column in the web-accessible csv, with values separated by pipe symbols: |

To re-separate or parse a multi-valued field:

1. Open the csv in Excel or LibreOffice, and first check through the column of headings--e.g., "Measurement"
   If headings are the same throughout, go to step 2.
   If they vary between records, though, sort your data by the multi-headings column, and move data sub-sets with different headings into separate sheets. Then go to step 2.

2. If the column containing multiple values isn't the last column, copy and paste it into the first empty column to the right of the rest of the data. Make sure the columns to the right of it are empty, too, since they will be overwritten.

3. Highlight the newly pasted column, and find & replace all " | " (pipe with a space on either side) with "|" (pipe with no spaces on either side).
   in Excel, do this by pressing Ctrl+F, and filling in the "Replace" tab.
   in LibreOffice-Calc, press Ctrl+H, and fill in the find/replace form.

4. Re-highlight the full column, and...
   In Excel, go to the "Data" tab in the menu, and "Text to Columns." Select "Delimited" and click Next. Uncheck all "Delimiters" except "Other," and enter a pipe "|" into the text box. ("Text qualifier" should be double quotes: "). Click Finish.
   In LibreOffice-Calc, go to "Data" -> "Text to Columns". Select "Delimited," and in "Separator Options" check "Other" and enter a pipe "|" into the text box. If your data looks wrong in the preview window, make sure all other "Separator" boxes are not selected. ("Text delimiter" should be double quotes: ") Click OK.
   Your data values should now be parsed into separate columns for each value.

5. If corresponding headings are in a separate column, and are similarly concatenated (e.g., the "Measurement" column), copy that column to a new sheet, and repeat the find/replace, and text-to-columns.

6. If the parsed headings match from top row to bottom row (never hurts to double check), simply copy one set of headings, and paste it at the top of your first spreadsheet over the corresponding parsed values.
   Save the sheet, and share/analyze/etc.

These instructions are also public here: http://collections-zoology.fieldmuseum.org/page/help-how-parse-measurements