I have to use a web site to export data in XLS format, to be converted to CSV, to be dumped into a MySQL database.
My problem is that this POS exporter treats carriage returns in the original data as new fields, so that the Excel file is a mess:
Example:
[Name] [Age] [Employment History] // Excel File:
Smith, John 30 2005-2006 Bennigan's // row 1
2006-2007 Shenanigan's // row 2
2007-2008 Grizzlebee's // row 3
Smith, Karen . . . // row 4
This totally effs my one-step CSV conversion. The standard merge command is not helpful because it drops all the data apart from the first field.
The only solution I've found is that I can insert an adjacent column, merge the appropriate rows, concatenate those 3 lines into the post-merge block in the second column, and then remove the original. This is brutally, almost impossibly time-intensive though.
Does anyone have a better way to solve the problem? The POS web exporter is controlled by another company and completely out of my hands...
Edit to add a "correct" example:
[Name] | [Age] | [Employment History] | // Excel File:
______________|__________|__________________________|__
Smith, John | 30 | 2005-2006 Bennigan's | // row 1
| | 2006-2007 Shenanigan's |
| | 2007-2008 Grizzlebee's |
______________|__________|__________________________|___
Smith, Karen | 25 | 2001-2010 Ma Bell | // row 2
_____________|__________|__________________________|___