1
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
_____________|__________|__________________________|___
The example you've given is the "wrong" layout for the data? What exactly is the intended "correct" layout you're aiming for? – DMA57361 – 2010-09-21T07:52:17.127
all 3 values for Employment History should be within the same column of row 1, so that one person occupies one row, just as they would in a real database – Drew – 2010-09-21T07:56:06.870
If the first column is empty for all duplicates I think a simple VBA macro will probably be the best way to do this for you - I'll throw something together and stick it in an answer. – DMA57361 – 2010-09-21T07:58:41.027
if it helps you, DMA57261, column A is always merged to the appropriate size, it's columns further along the dataset that are sporadically broken. – Drew – 2010-09-21T08:01:18.803
So "A1" is actually over "A1:A3" in the inital example? – DMA57361 – 2010-09-21T08:02:28.473
Yeah, in the file I'm looking at what should be A2 (if the record was all in one row) spans A2 through A8, as do almost all the other columns, except for the offending column, which has A2 through A8 as separate cells each with their own data. Does that make sense? – Drew – 2010-09-21T08:09:09.127
Yep. Final intention is that the offending row is merged to one cell and then the entire row is just in row 2, get rid of the merging? – DMA57361 – 2010-09-21T08:10:20.587
yep! that'd be perfect. – Drew – 2010-09-21T08:13:06.643