Spent more time cleaning up the data than parsing it, but that also uncovered some errors in my process (like removing punctuation resulting in decimals I wanted being deleted) - diligence matters....
I guess mine isn't dynamic regarding number of columns....
Spoiler
Process:
- Test to columns, with "|" delimiter
- Removed Null records (that got rid of the row separator "------------etc"
- Data Cleansing to remove trailing spaces, etc
- Dynamic Rename to capture field names in Record 1
- Clean-up getting rid of N/As, a couple of extraneous |, and empty fields (convert to Null)
- Test to columns, with "|" delimiter
- Removed Null records (that got rid of the row separator "------------etc"
- Data Cleansing to remove trailing spaces, etc
- Dynamic Rename to capture field names in Record 1
- Clean-up getting rid of N/As, a couple of extraneous |, and empty fields (convert to Null)
Interesting to see all the apps/macros based responses. Feel like this solution is dynamic and doesn't require either of those.
