I’m on a Mac, first of all.
If I have a series of records that look like this:
Is there a relatively painless way that Excel can turn it into something more easily handled by using the field names just once and pulling all the information into the columns underneath those names? I don’t even necessarily want all the data, so I can leave off the inconsistent fields.
I know this can be done somehow, I just don’t know how and I don’t know how convoluted and timesucky it is.
As a first step, you can dump the text of each record into a single cell and then separate it using the colon as a delimiter. It’s pretty straightforward and will get your field names isolated.
On this data, you’ll end up with two columns, A and B.
Then it gets a little bit more difficult (this would be best using macros) but now you can place all your Field Names from column A across the top row (Copy, Paste Special>Transpose) and then one-by-one transpose your data from column B into subsequent rows.
If necessary, there are ways to strip extra spaces out of the data as well, if that becomes a problem for you.
If this is data that will be manipulated a lot, and more records will be created, consider using a database (like MS Access), since it is better at this sort of thing. There’s a learning curve, but once it’s up and running it really is a much better tool than Excel.
Second the advice to switch to a relational db if this is a large record set that is being manipulated a lot. Once in a db, you cna easily output to XML, CSV, anything you want.
I might do it somewhat differently. First I’d make sure there each product had the same number of lines in the same order, even if it required putting in N/A.
Still in a text editor, replace every double return (beginning of new record) with some distinctive character like ^.
Then replace all returns with tabs, and go back to replace every ^ with a return. Now you should have each record on one long line, with the headings more or less lined up. Now replace the colons with even more tabs.
Then bring this into Excel. You can copy and paste, or let the Import Wizard guess that tabs are delimiters. Look through to make sure things are lining up properly. Use Insert (shift cells right) to make sure of it.
Finally, put in the column headings you want, and delete the columns saying Version: a hundred times down the column.